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()
I’ve added this example to my original demo workbook, which can be downloaded here.
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.
I think the Number.ToText() function should solve half your problem (see http://office.microsoft.com/en-gb/excel-help/number-totext-HA104110114.aspx?CTT=5&origin=HA104122363); for the leading zero problem you’d be able to use an if expression to concatentate the 0 if the month number was less than 10.
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…
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.
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
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
Cool stuff!