Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Conditional logic in Power Query

with 8 comments

Writing a simple if statement in Power Query’s M expression language is straightforward. Using an Excel table called Input that contains a single value as the starting point:

image

The following query shows how to use an if … then … else statement to test whether the value from the table is equal to 5:

let

    Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],

    InputValue = Source{0}[Input],

    IfStatement = if InputValue=5 

                    then "The number is five" 

                    else "The number is not five"

in

    IfStatement

What about more complex conditional logic? The M language doesn’t include anything like a case statement, but it is possible to write the equivalent of one quite easily.

Here’s an example of a simple case statement:

let

    Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],

    InputValue = Source{0}[Input],

    CaseValues = {

                    {1, "First"},

                    {2, "Second"},

                    {3, "Third"},

                    {4, "Fourth"},

                    {5, "Fifth"},

                    {InputValue, "Else condition"}

                },

    SimpleCase = List.First(List.Select(CaseValues, each _{0}=InputValue)){1}

in

    SimpleCase

This works as follows:

  • The CaseValues step defines a list containing six items, each of which is itself a list containing a number and some text. The number is the value to compare to the input value, and the text is what will be returned if the number does match the input value.
  • The last item in the CaseValues list contains the input value, so this will be returned where the input value matches none of the preceding values
  • The SimpleCase step uses List.Select() to filter the list in CaseValues so that only the items in the list where the input value matches the number in the list.
  • Since List.Select itself returns a list, this list is then passed to List.First() to get the first item in the list returned by List.Select (there should only be one item in the list in this particular query), and then {1} returns the text from that item. This is the output of the query.

You can write a searched case expression in a very similar way, by declaring functions that return boolean values instead of using numbers as follows:

let

    Source = Excel.CurrentWorkbook(){[Name="Input"]}[Content],

    InputValue = Source{0}[Input],

    CaseValues = {

    { (x)=>x<10, "Less than 10"},

    { (x)=>x<20, "Less than 20"},

    { (x)=>x<30, "Less than 30"},

    { (x)=>x<40, "Less than 40"},

    { (x)=>x<50, "Less than 50"},

    { (x)=>true, "Else condition"}

    },

    SimpleCase = List.First(List.Select(CaseValues, each _{0}(InputValue))){1}

in

    SimpleCase

 

In this query the CaseValues step contains a list of lists, where each item in the list consists of list containing a function and a text value. List.Select calls each function and only returns the items where the function returns true, and finally the text from the first item that List.Select returns is the output of the query.

You can download the sample workbook here.

Written by Chris Webb

March 10, 2014 at 9:00 am

Posted in Power Query

8 Responses

Subscribe to comments with RSS.

  1. […] 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 […]

    • Chris,

      That’s very clever! I use stacked else ifs, but that’s not as cool as your solution. For example, as a custom function, stacking else ifs would look like:

      let
      Source = (x)=>
      if x<10 then "Less than 10"
      else if x<20 then "Less than 20"
      else if x<30 then "Less than 30"
      else if x<40 then "Less than 40"
      else if x<50 then "Less than 50"
      else "Greater than or equal to 50"
      in
      Source

      Colin Banfield

      March 10, 2014 at 4:52 pm

      • I’m prepared to admit that this is a bit *too* clever – and that the stacked ifs is probably more maintainable – but this was as much about learning what’s possible in M as solving a real problem.

        Chris Webb

        March 10, 2014 at 5:41 pm

      • In fact, I wonder whether the stacked ifs might perform better too…

        Chris Webb

        March 10, 2014 at 5:42 pm

      • Performance might be better with stacked else ifs because you avoid multiple function calls. However, my takeaway from your post is, as you said – learning what’s possible in M to solve real problems. The pattern looks like something that can be adapted for other scenarios. It’s ironic when I think that I started using M to fill what I thought were gaps in PQ, and I’ve learned much more than I would have if those gaps weren’t there. I hope to post some of these solutions when I figure out an appropriate outlet.

        Colin Banfield

        March 10, 2014 at 6:53 pm

  2. […] a short follow-up to my last post on conditional logic in M. After that post went live, Ehren Vox of the Power Query team made a good suggestion on Twitter: […]

  3. […] a short follow-up to my last post on conditional logic in M. After that post went live, Ehren Vox of the Power Query team made a good suggestion on Twitter: […]

  4. I just had a case where I had this Chinese textual column (number of CPU cores) I wanted to make numerical. I used the following approach using List.ReplaceMatchingItems() after looking for a way to do a switch statement in M, which seems somewhat similar to your approach, if probably less flexible:

    InsertedCustom = Table.ExpandListColumn(Table.AddColumn(ChangedType, “cores”, each List.ReplaceMatchingItems ({[核心数]}, {{“单核心”, 1},{“双核心”, 2},{“四核心”, 4}})), “cores”)

    The original column just contained string values while List.ReplaceMatchingItems() required a list, so I first had to cast the column value to a list (-> {[核心数]} ), then cast the result back to a regular non-list value, hence the unfortunate Table.ExpandListColumn(). Too bad it doesn’t just work with non-lists, but as a one-liner it’s fair enough for my purposes here.

    tycho01

    March 16, 2014 at 11:49 am


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

Follow

Get every new post delivered to your Inbox.

Join 3,310 other followers

%d bloggers like this: