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

Source

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

let
   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")
in
   InsertedAgeBand

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

let
    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}
in
    Result
in
    AgeBand

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

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

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”})

Advertisements
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) =>
    let
        Source = Excel.CurrentWorkbook(){[Name="Bands"]}[Content],
        FilteredRows = Table.SelectRows(Source, each try [UB] > age otherwise true),
        KeptFirstRows = Table.First(FilteredRows),
        Band = KeptFirstRows[Age Band]
    in
        Band
    in
        AgeBand
  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