![1706191226_large.pngcharsetUTF-8](https://quantinsightsnetwork.com/wp-content/uploads/2024/01/1706191226_large.png;charset=UTF-8)
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:
- 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’
- 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
Be the first to comment