Some people say that naming is the hardest aspect in coding. Today’s blog post is about naming conventions. Continue reading “Power BI Antipatterns #13: Naming conventions”
Category: M
SWITCH and SWITCH(TRUE… in Power Query
Ever wanted to mimic the SWITCH function from DAX (or CASE ... WHEN from SQL) in Power Query, including the SWITCH(TRUE... behavior? In this blog post, I’m providing a function that does just that.
Power Query in dataflows vs. datasets
A few months ago I participated in Q&A in the Sydney Power BI User Group, and one person asked whether we should write our queries in datasets or dataflows. The Q&A format doesn’t allow you to spend too much time on a single question, so in this blog posts I’m providing a more comprehensive comparison of the two options. Continue reading “Power Query in dataflows vs. datasets”
Built-in column sort order in Power BI
The sort by column feature of Power BI hides some potentially unwanted complexities. This blog post shows how you can avoid having to sort a column by another column while maintaining a custom sort order. Continue reading “Built-in column sort order in Power BI”
Single-letter day and month names in Power BI
Occasionally in Power BI, you may want to display day or month names as single letters to save space. This may result in duplicates because neither day nor month names are unique when you shorten them to one letter. In this blog post, I’m showing two solutions to the problem: one in DAX and one in Power Query (M language). Continue reading “Single-letter day and month names in Power BI”
Grouping text values in Power BI
The August 2020 release of Power BI Desktop includes a new Power Query function: Table.AddFuzzyClusterColumn. This function allows you to clean, cluster, or group text values. In this blog post I’m showing an example of how it works. Continue reading “Grouping text values in Power BI”
Converting hexadecimal to decimal numbers in Power Query
On more than one occasion I saw people writing complex functions in M to convert hexadecimal numbers to decimal ones (Greg Deckler and Soheil Bakhshi, to name a few). In this blog post, I’d like to share probably the quickest way of doing it. Continue reading “Converting hexadecimal to decimal numbers in Power Query”
Safely transforming column types in Power Query
The Table.TransformColumnTypes function in M throws an error when you try to transform the data type of a column that does not exist. As of March 2020, there is no missingField parameter that can address this issue, in contrast to some other Table functions, like Table.RemoveColumns, which I blogged about before. This blog post shows how to imitate the behavior of the MissingField.Ignore argument in Table.TransformColumnTypes.
Continue reading “Safely transforming column types in Power Query”
Safely removing columns in Power Query
In Power Query, if you remove a column that does not exist, you’ll get an error. While you probably aren’t going to remove a non-existent column on purpose, this may happen when the column in question is removed from your data source.
A popular way to prevent this error from happening is to use the Table.SelectColumns function instead of Table.RemoveColumns. This may potentially bloat your query code if you have many columns to select. You may also use try and otherwise, which again makes your code unnecessarily long. In this blog post, I’m showing an alternative solution. Continue reading “Safely removing columns in Power Query”
PQ text replacement competition
In my previous blog post, I wrote about the then-new M function, Text.ReplaceAll. Unfortunately, this function is not available anymore. A certain someone from Microsoft contacted me and said that this function was made visible by mistake; it was part of a certified connector and should not have been exposed. They were kind enough to provide the actual code behind the function though. I was quite impressed by the way the function was written, and I decided to run a competition (with a prize!) to write the improved version of the function in the shortest possible way. Continue reading “PQ text replacement competition”