Chris Webb's BI Blog

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

User-Defined Conditional Logic In M

with 9 comments

Here’s 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: rather than hard-code the list of conditions and values inside the query, why not take those values from the Excel spreadsheet too? That way end-users can maintain the conditions and values themselves.

Here’s my Excel spreadsheet, now with two tables: one called Input, containing my input value, and one called CaseValues containing my conditions and return values.

image

And here’s my new query, a variation on the simple case statement query from my previous post, but this time using the values from the CaseValues table to drive the logic:

let

    //load input value table from worksheet

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

    //get input value from that table

    InputValue = Source{0}[Input],

    //load case values from worksheet as a table

    CaseTable = Excel.CurrentWorkbook(){[Name="CaseValues"]}[Content],

    //turn that table into a list and append the else condition to the end

    CaseValues = List.Combine({Table.ToRows(CaseTable),{{InputValue, "Else condition"}}}),

    //look for the input value in the CaseValues list and return the value associated with it

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

in

    SimpleCase

The output here, once again, is the text value “Five”. Two interesting things to notice here:

  • I used the Table.ToRows() function to turn the table containing my case values into a list of lists
  • I used List.Combine() to append the else condition (a list containing two values, the input value and the text “Else condition”) onto the end of the list returned by Table.ToRows()

I’ve added this example to my original demo workbook, which can be downloaded here.

Written by Chris Webb

March 14, 2014 at 11:38 pm

Posted in Power Query

9 Responses

Subscribe to comments with RSS.

  1. Hi Chris,

    I pre-ordered your new Book on Amazon last week and can’t wait to get my hands on it! I need it badly!

    I used Power Query to first create and them merge a series of Formula Cost Prices (one per Month) for subsequent loading into Power Pivot.

    The key fields are:
    – StockCode Text 100005G
    – Year Number 2014
    – Month Number 3

    I want to create a combined Cost Look up Key which will combine the above into a single Text Field as
    100005G201403

    When I try using the Custom Column option I get an error … can’t combine Text & Number. I think I can get around that bit … but I cannot see how to get the leading zero required to make the month a 2-character element in the combined field.

    I have created the combined column in Power Pivot, but seeing as I am creating the input table I want to include the Cost Lookup Key in it to take advantage of data compression.

    Can you bail me out please?

    Many thanks,

    Ted.

    Ted Murphy

    March 25, 2014 at 8:21 am

  2. Hey Chris, great post – another powerful feature that is along these lines – rather than taking constant case values a great feature that I have been using is passing functions as data. I had been doing this mostly through hard coding anonymous functions in code but i have a sort of case table where most items are map-able by static cases, but some require more nuance and I needed another variable for some cases. But this was really easy to accomplish with anonymous functions. The only trick is that in hard coding a case you will need to evaluate it, but it is just as easy to replace the text with a function in the compiler, so that I need not even provide a function name or variables, just replace the text with a function for the known function references and an extra step to infer the needed variables from the input table.

    Derik

    April 22, 2014 at 1:55 pm

    • Cool! This is what I love about M – the possibilities are endless…

      Chris Webb

      April 22, 2014 at 2:48 pm

  3. Why didn’t you use an if statement? I want to evaluate a couple of fields and write an answer based upon the evaluation to a third field. Was the if expression an option when this was first written?

    jeff

    June 4, 2014 at 4:36 pm

    • Yes, the if expression was available, but the point of this post is to use a series of values coming from a table in Excel to control the logic. You can’t use an if expression for that.

      Chris Webb

      June 4, 2014 at 4:43 pm

  4. I made a variation on this – created it as a function that is overloaded to accept various inputs. https://www.youtube.com/watch?v=CqfMjR67gRY

  5. The TLDR version – overloaded select case guts (code) only:

    (CaseValue as any, CaseTable as any, ElseCondition as any, optional ValueCaseCompare as any) =>

    // Version of ‘Select-Case’ Statement that takes the following parameters:
    // 1) An equality argument
    // 2) A two-column case-return value pair table and
    // 3) An ‘else’ condition

    let

    //OVERLOAD1: If given a table then use it,
    // otherwise assume CaseTableParameter is the name of a table in the current book

    Cases = if CaseTable is table then CaseTable else Excel.CurrentWorkbook(){[Name= CaseTable]}[Content],

    //Find name of ‘Case’ Column for use in final step

    CaseAttributeName = Table.ColumnNames(Cases){0},

    //look for the input value in the CaseValues list and return the value associated with it

    FoundCase = try
    Record.FieldValues(
    Table.SelectRows(Cases ,
    each Record.Field(_, CaseAttributeName) = CaseValue) //END TABLE.SELECTROWS – FILTERS TO ROWS THAT MATCH CASEVALUE
    {0}) // FIRST RECORD OF TABLE.SELECTROWS KEPT – Prevents Duplicates, Record Passed up to
    {1} // PULLS VALUE FROM SECOND COLUMN (AT 1 INDEX) FROM RECORD
    otherwise
    ElseCondition, // ELSE PARAMETER HANDLES ERRORS – THIS ERROR ONLY EXPECTED WHEN TABLE EXISTS IN CORRECT FORMAT, BUT DOES NOT CONTAIN CASE

    OptionalAsFunction = if ValueCaseCompare is function // STEP TRYS TO CONVERT VALUECASECOMPARE PARAMETER TO A FUNCTION IF GIVEN TEXT
    then ValueCaseCompare
    else Expression.Evaluate(ValueCaseCompare, #shared) as function, // ‘AS FUNCTION’ FORCES AN ERROR IF NOT IN PROPER FUNCTION SYNTAX

    FirstMatch = try
    Record.FieldValues(
    Table.SelectRows(Cases ,
    each OptionalAsFunction(CaseValue, Record.Field(_, CaseAttributeName))) //END TABLE.SELECTROWS – FILTERS TO ROWS THAT MATCH CASEVALUE
    {0}) // FIRST RECORD OF TABLE.SELECTROWS KEPT – Prevents Duplicates, Record Passed up to
    {1} // PULLS VALUE FROM SECOND COLUMN (AT 1 INDEX) FROM RECORD
    otherwise
    ElseCondition, // ELSE PARAMETER HANDLES ERRORS – THIS STEP MAY MASK ERRORS IN THE OPTIONAL FUNCTION ARGUMENT NUMBER 4 OR WHEN TABLE DOES NOT APPROPRIATE CASE

    ReturnValue = if not (Cases is table)
    then error “Table not found – confirm parameter two is of type ‘table’, or the name of an Excel table in the current workbook” // ERROR HANDLES INVALID PARAMETER TYPES
    else if Table.ColumnCount(Cases) < 2
    then error "Function requires a minimum of two columns for input 'case table'." //ERROR GENERATED IF TABLE PARAMETER DOES NOT HAVE AT LEAST TWO COLUMNS – ADDITIONAL COLUMNS IGNORED
    else
    if ValueCaseCompare is null
    then FoundCase
    else FirstMatch

    in
    ReturnValue


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,131 other followers

%d bloggers like this: