Conditional Columns in Power BI Desktop

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).

image

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).

image

I then edited the code created by Power Query directly in the Formula Bar.

image

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.

image

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.

image

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).

image

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

Share?

Comments

  1. 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.

  2. 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.”

    • 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.

  3. Instead of “each 2016 – Date.Year([BirthDate])” you should have “each Date.Year(DateTime.LocalNow()) – Date.Year([BirthDate])”

Leave a Reply

Your email address will not be published. Required fields are marked *

Ten Things I Wish I Knew When I Started With Power Pivot.   Learn  Without The Pain!

x