Safely removing columns in Power Query

Error when 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”

New M function: Text.ReplaceAll

Replacement dictionary for Text.ReplaceAll

Update 4 June 2019: unfortunately, the function was not meant to be published. The function is part of a certified connector and is now hidden.

The May 2019 release of Power BI Desktop includes a new M function: Text.ReplaceAll (not documented on Microsoft Docs as of 18 May 2019). This function simplifies multiple word replacements that previously had to be done by using custom functions. Continue reading “New M function: Text.ReplaceAll”

Power Query Advanced Editor keyboard shortcuts

Remember my post about DAX formula bar keyboard shortcuts in Power BI Desktop? Turns out, there are also lots of keyboard shortcuts in Advanced Editor inside Power Query Editor in Power BI, with many being the same as in DAX formula bar! Continue reading “Power Query Advanced Editor keyboard shortcuts”