Microsoft Power BI, Analysis Services, MDX, DAX, M, Power Pivot and Power Query

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

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.

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.

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

Follow Blog via Email

Social

Need some help?

As well as being a blogger, I'm an independent consultant specialising in Analysis Services, MDX, DAX, Power BI, Power Query and Power Pivot. I work with customers from all round the world solving design problems, performance tuning queries and delivering training courses, and I am happy to work on short-term engagements. For more details see http://www.crossjoin.co.uk