Starting in May 2020, Power BI allows you to navigate or drill through to pages by using conditional formatting. In this blog post, I’m showing how to make it work. Continue reading “Dynamic page navigation and drillthrough”
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”
Using text measures in multi-row cards in Power BI
A not-so-well-known feature of the multi-row card visual in Power BI is that it can have its own title, called Card Title, in addition to the title any visual can have. In the image above, the Card Title is blue, and it is formatted separately from the visual title. It only works if you use one measure that returns text, and all other visuals are not text. In this blog post, I’m sharing a technique to display text as values in a multi-row card and still have the special Card Title. Continue reading “Using text measures in multi-row cards in Power BI”
TRIMMEAN in DAX
There are multiple ways of averaging values in DAX. Some of the most popular ways are mean (AVERAGE), median (MEDIAN) and mode (no built-in function; see DAX Patterns for an example). Yet another is TRIMMEAN, which exists in Excel. There is no corresponding function in DAX, and this blog post shows how you can replicate the function in DAX. Continue reading “TRIMMEAN in DAX”
New DAX functions: CONVERT and REMOVEFILTERS
August 2019 saw the introduction of two new DAX functions: CONVERT and REMOVEFILTERS. They are so new that at the time of writing this blog post (23 August 2019), they are only available in Azure Analysis Services and Power BI service, and even DAX Guide does not list them. In this blog post I’m showing what they are doing. Continue reading “New DAX functions: CONVERT and REMOVEFILTERS”
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”
New M function: 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”
Sort by Column in Power BI
The Sort by Column feature of Power BI and tabular data models in general is well-known. The simplest example is sorting month names by their actual position in a year instead of the default (alphabetic) order. There is a popular misconception that there must be a one-to-one mapping between the column you are sorting and the column you are sorting by. This is not so, and in this blog post I am discussing unusual applications of the Sort by Column feature. Continue reading “Sort by Column in Power BI”
Exporting all data from a Power BI data model
The latest version of DAX Studio, 2.8.0, was released yesterday. It has several new features, one of which stands out for me: Export All Data. While you could write your queries to CSV files or Excel before, this feature allows you to export the whole data model at once to CSV files or — and this is why I really like it — SQL Server. In this blog post, I am showing how to do it. Continue reading “Exporting all data from a Power BI data model”