Microsoft recently announced a new conditional column feature in Power Query (Power BI Desktop version) that warrants a blog post. This feature makes the process of creating banded columns much easier for the average user. I blogged about banded columns using DAX here, and now there is this second, potentially easier approach. In the demo below, I will add a new column of “age bands” just like I did with the DAX example above.
This column is then part of the data model and can be used in Power BI Desktop to slice and dice the visualisations.
The New Conditional Column Menu Item
I am using Adventure works and importing the Customer table (shown as 1 below) for this demo. Note on the Add Column menu (2) there is a new menu item “Conditional Column” (3).
Add an Age Column
The first thing I need to do is create an Age column. To do this, I first created a “Year of Birth” column by selecting the Birth Date column and then selecting Date\Year\Year. This will add a new column containing the year of birth (keeps the original Birth Date column).
I then edited the code created by Power Query directly in the Formula Bar.
The original Code created by Power Query was this:
= Table.AddColumn(_Customers, "Custom", each Date.Year([BirthDate]), type number)
I simply changed the code directly in the Formula Bar to be like this:
= Table.AddColumn(_Customers, "Age", each 2016 - Date.Year([BirthDate]), type number)
Once you get the hang of the way Power Query formulas are constructed, you can easily make small changes like this to streamline the process. Of course this is a hard coded approximate age, and it will not be 100% correct after the person has a birthday, but it is good enough for the purpose of this demo.
Add the Conditional Column
Then I clicked the new Conditional Column menu button (shown earlier) and the following dialogue box appeared.
It is just a matter of using the drop down menu items to build the logic for the banded column using standard Boolean If/Then logic.
The main thing to note is that you need to build the logic in a way that it incrementally checks each step and then it exits the logical test once it finds the right band. In my example above, I started with the oldest band, and each test is a “Greater Than”. I could have also started with the youngest band and applied “Less Than” test. But it would not work if I started with the youngest band and did a “Greater Than” test, or if I started with the oldest band and did a “Less Than” test.
The Final Result
Note how the new banded column now appears (shown as 1 below) and also note the code in the Formula Bar (shown as 2 below).
It has always been possible to write this code (shown in the formula bar 2 above) using the “Add Custom Column” feature. What Microsoft has done here is to create the user interface to help the user write this custom column without having to learn the PQL language. In fact this is what Power Query is all about – it is simply a user interface driven tool that sits on top of a power formula language, and the user interface does most of the heavy lifting for the user.
The product keeps getting better – keep up the good work Microsoft.
If you want a comprehensive lesson on how to use Power Query, checkout my training course here http://xbi.com.au/pqt