Level: Beginners
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 https://exceleratorbi.com.au/product/power-query-excel-power-bi-online-training/
How to put TODAY’s date function to VALUE field in Add Conditional Column Window? I Tried
=TODAY()
but it doesn’t work.
maybe not as you would expect. if you add null to a number, you get null. There are different approaches to address this. Probably the easiest way is to replace the nulls with 0 before adding the new column
How are nulls dealt with in “Add Condition Column”?
Hi – is it possible to put a formula in the “Output” for the Conditional Column?
I’m not sure what you mean. The conditional column writes a line of M code that can be edited manually if you like.
Hi! I have a similar question as anje. In the output of the conditional column, I want to get a column value multiplied by a column from another table. I don’t see other table columns listed in the UI and it gave error when coding. Any suggestions?
Hi, if you want to use a column from another table, first you need to bring that column into the current table. You can do this by doing a merge between the current table and the second table that contains the extra column(s), then extract the column(s) you want. Then it will be visible in UI and you can use it.
Instead of “each 2016 – Date.Year([BirthDate])” you should have “each Date.Year(DateTime.LocalNow()) – Date.Year([BirthDate])”
You are right. Mine was a hard coded calculation and you suggestion will work at anytime in the future.
How do you edit a previously created Conditional Column?
Like all Queries, you need to edit the query, then go back to the first step (source) and work your way through until you find the point you want to edit. Keep an eye out for the cog icons in the Query Steps Pane to indicate steps that you can edit using the UI.
So how do you edit the query with the UI? I know you can go in and edit the text of the query like this:
= Table.AddColumn(#”Removed Columns1″, “Basic OS”, each if Text.Contains([Windows OS], “2003”) then “Server 2003” else if Text.Contains([Windows OS], “2008”) then “Server 2008” else if Text.Contains([Windows OS], “2016”) then “Server 2016” else if Text.Contains([Windows OS], “2012”) then “Server 2012” else if Text.Contains([Windows OS], “2000”) then “Server 2000” else null )
But it’s much easier and less prone to error if I could step through the UI and add additional conditions.
Thanks.
Any step in Power Query that has a dialog box will have a ‘cog’ icon in the “Applied Steps” pane on the right hand side. Click the cog and you will be taken back to the dialog to edit the query.
Please take notice of the fact that there is a bug in the current version of Power BI Desktop (2.34.4372.322 64-bit (april 2016)) that makes it imposible to have 2 consecutive conditional columns after each other; there has to be a step in between that is not a conditional column. Here is the text from Microsoft:
“This is a bug in the product, rather than a deliberate design choice. You can definitely add more than one conditional column to a query, they just need to be non-consecutive steps in the query; otherwise, trying to add a second column will take you back to the Edit Settings mode for the previous step.
We’re actively working on addressing this bug, so that you can create as many consecutive Conditional Columns as you want. The temporary workaround would be to add an intermediate step (simplest way to do this is just clicking the “fx” button in the formula bar, which will insert a no-op step that just references the previous one) and then insert the Conditional Column step.”
Interesting.. Now if we could get a Switch statement in Power Query
That is pretty awesome and a lot easier, thank you for the post Matt.
Just out of interest, how would one go about making the hard coded year (2016) dynamic?
Well the truth is I hard coded this so that I didn’t have to solve this exact issue :-).
One of the reasons I like blogging is so I can find stuff later when I need it again. I recall using the formula for this in my blog about creating Power Query Calendar tables here http://www.powerpivotpro.com/2015/02/create-a-custom-calendar-in-power-query/
You will see in that post that I refer to the following formula.
DateTime.Date(DateTime.FixedLocalNow())
So this picks up the DateTime from your local PC and converts it to a Date. You can use this data instead of the hard coded year to get a much more accurate (and up to date) age.