A Case For Functions

One of the things I don’t like about Power Query is that the list of instructions gets quite long, when a transformation is more than just a simple replace or add columns, and becomes less easily maintained. I am a firm believer in the Smurf principle that a procedure should not take more than one screen. Longer procedures get broken down into sub-procedures, called from the main procedure.

Chris Webb posted another excellent Power Query blog this morning, Conditional Logic in Power Query, where Chris shows how to implement a pseudo-Case statement in Power Query. 

My procedure with nested IFs was getting far too long, and unfortunately, although Chris’ pseudo-Case statement  was more elegant, it did nothing to alleviate this. So I took this one step further, to get the best of both worlds. I built this case logic into a function that could be called from the main procedure, reducing the complexity of that procedure and increase the maintainability.

In this example, the data I will be using is a simple list of names and ages, and I will create an added column of age band. The data is shown below


The idea is to create bands of under 20, 20-39, 40-59, and 60 or over.

First, I loaded the table into Power Query, using the Excel Data>From Table button on the Power Query ribbon tab.

My original code would have looked like this, using nested IF statements

   Source = Excel.CurrentWorkbook(){[Name="tblAgeBand"]}[Content],
   InsertedAgeBand = Table.AddColumn(Source, "Age Band", each
     if [Age] < 20
         then "under 20"
     else if [Age] < 40
         then "20-39"
     else if [Age] < 60
         then "40-59"
     else "60 or over")

Although this works, the results would be as shown in the following image. I think you can agree that the code is not very elegant, even with this very simple set of statements. It can , and does, get far worse with a more complex example.

CfF - If results

To improve this, I created a function to calculate and return the age band, and then invoked that from the main procedure.

The function, which I call fnAgeBand would look as follows

// fnAgeBand
let AgeBand = (age) =>

    AgeBands = 
          {(x)=>x<20, "under 20"},
	  {(x)=>x<40, "20-39"},
	  {(x)=>x<60, "40-59"},
	  {(x)=>x<99, "60 or over"}

    Result = List.First(List.Select(AgeBands, each _{0}(age))){1}

Don’t forget to uncheck the ‘Load to worksheet‘ in the LoadSettings.

All that is the needed is to invoke the function from the main procedure. The code is shown below

    Source = Excel.CurrentWorkbook(){[Name="tblAgeBand"]}[Content],
    InsertedAgeBand = Table.AddColumn(Source, "Age Band", each fnAgeBand([Age]))

You can download the workbook here.

Before anyone adds this, the band could of course be determined in Excel with a formula, such as

=LOOKUP(tblAgeBand[@Age],{0,20,40,60},{“under 20″,”20-39″,”40-59″,”60 or over”})

This entry was posted in Power Query and tagged , , . Bookmark the permalink.

3 Responses to A Case For Functions

  1. Bertrand says:

    Your post got me thinking: how to do the same when you require a parameter table rather than hard-coded bands limits. I created a “Bands” table with 2 columns: [UB] (upper band) and [Age Band] . [UB] contains ordered numeric bands limits. The last row of [UB] contains “over”, but any text value will do.
    This is the function I ended up with:

    let AgeBand = (age) =>
        Source = Excel.CurrentWorkbook(){[Name="Bands"]}[Content],
        FilteredRows = Table.SelectRows(Source, each try [UB] > age otherwise true),
        KeptFirstRows = Table.First(FilteredRows),
        Band = KeptFirstRows[Age Band]
  2. Pingback: Excel is for Parameters | Bob's BI Ramblings

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s