Custom function with multiple condition logic to achieve switch in Power query

[v-rzhou-msft]

Sometimes we may need to deal with some complex conditions or cases in query editor that is similar to switch function which isn’t included in M query functions.

Sample data:

Usage scenarios:

#1, Basic switch case to return tag.

1). Write a custom list that is similar to switch function structures.

List structure:

{
//case
            {
                "case 1",
                "Result 1"
            },
//case2
            {
                "case 2",
                "Result 2"
            },
//case3
            {
                "case 3",
                "Result 3"
            },
            //return default input if no item matched 
            {
                input,
                input
            }
 }

Comment:

The above is a 'key, value' structure, it is stored in a list with multiple 'case' and 'result' combos.

Usage case:

       return result 1 if Desc= ‘abc’;

       return result 2 if Desc = ‘def’;

       Other scenario returns default input text.

2). Modify these values and save into a query step and use list function to loop the list to return matched items

function query:

(rawText as text) as text =>

    let

        conditionList = {

            //conditions

            {

                "abc",

                "Result 1"

            },

            {

                "def",

                "Result 2"

            },

            //default

            {

                rawText,

                rawText

            }

        },

        //get reuslt from the first match item

        Result =

            List.Select(

                conditionList,

                each Text.Contains(rawText, _{0})

            ){0}{1}

    in

        Result

Comment:

1. the ‘condition list’ stored the list that combo with two child items, the first is the 'case item', the second the returned 'result'. Custom step ‘Result’ nested the 'Text.Contains'
2. function with the list index operator {} to extract the first item.

List.Select function will return a list, so this place I nest two ‘list index’ operators to get the first return results' second item.

Operator	Description
{}	Access an item in a list by its zero-based numeric index.

3). Click on ‘Add column’ Tab and choose ‘invoke custom function’ to invoke a custom function with current table field values as function parameter.

#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each fxSwitch([Desc]))

#2, Switch between different calculations rules.

Usage case:  

Return the 30% input value if the calculate rule field = percent;

Return 1/5 if the calculate rule = divide;

Other rule returns raw inputted value.

1). Change key, value list to key and expressions to suitable for calculation requirement.

Function query:

(rule as text, inputValue as number) as number =>
    let
        conditionList = {
            //conditions
            {
                "Percent",
                inputValue * 0.3
            },
            {
                "Divide",
                inputValue / 5
            },
            //default 
            {
                rule,
                inputValue
            }
        },
        //get result from the first match item
        Result =
            List.Select(
                conditionList,
                each Text.Contains(rule, _{0})
            ){0}{1}
    in
        Result

2). Create a custom column to invoke function with key and value fields.

#"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom2", each fxProcess([Desc],[Value]))

Summary:

The basic idea is from the excelguru 'multiple condition logic' blog, I expand them to use this for more scenarios.

For the above function code, they can be equivalent to switch function in DAX and do some redirect to different handle methods based on the input rule key. You can also complex calculate/process or nested with other functions to achieve some complex conditions handle in M query.

Reference links:

M Language Operators - PowerQuery M | Microsoft Docs

List.Select - PowerQuery M | Microsoft Docs

Expanded usage sample:

Solved: Re: need help to extract data from a cell with dif... - Microsoft Power BI Community

Author: Xiaoxin Sheng

Reviewer: Kerry Wang & Ula Huang