# User-Defined Conditional Logic In M

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.

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

## 9 thoughts on “User-Defined Conditional Logic In M”

1. Ted Murphy says:

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.

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.

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

3. jeff says:

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?

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

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