Hi,
The brunching can be done through the use of data manipulation functionality in Power BI.
This in a way a bit clunkier, but makes your script a bit more flexible when it requires changes to the conditions/brackets.
Say, we have a condition table which looks like this:
To Value
10 LOW
20 MID
30 HIGH
This script converts it into From..To view:
//Conditions
let
//Assume we have a table describing the "select..case" scenario
Source = #table({"To", "Value"}, {{10, "LOW"}, {20, "MID"}, {30, "HIGH"}}),
//Add column "From" and remember the format of the table, we will use it later
TableType = Value.Type(Table.AddColumn(Source, "From", each null, type number)),
//Create From-To version of the table by shifting the column "To" down
ListSkip = List.RemoveLastN (Source[To], 1), // this step truncates the list from the bottom
ListAdd = List.Transform (ListSkip, each _+1), // this step addes 1 to each item in the list as we do not want overlaps, this makes 10 => 11, 20 => 21 etc.
ListFrom = List.Combine({{1}, ListAdd}), // this step adds a starting point (1 in our case) as a base for the first item (i.e. from 1 to 10)
//New combine the created list and the existing table
TableCombine = List.Combine({Table.ToColumns(Source),{ListFrom}}), // Build the table as a set of lists/columns
TableBuild = Table.FromColumns(TableCombine, TableType), // Convert to Table - notice that we use the TableType we recorded earlier
#"Reordered Columns" = Table.ReorderColumns(TableBuild,{"From", "To", "Value"})
in
#"Reordered Columns"
It makes the table to look like (or you can manually create it):
From To Value
1 10 LOW
11 20 MID
21 30 HIGH
The following function returns a [value] from a condition based on argument provided
(amount as number)=>
let
result = Table.SelectRows(Conditions, each [From]<= amount and [To] >= amount){0}[Value]
/* Conditions refers the the condition table From|To|Value that we have created earlier */
/* {0}[Value] - this bit returns the field [value] in first row of the filtred Conditions table */
in
result This is the use case:
let
//Assume we have a table describing the data to be classified
Source = Table.FromColumns({{1..25}}, Value.Type(#table({"Amount"}, {null}))),
#"Added Custom" = Table.AddColumn(Source, "Custom", each fSelectCase([Amount]))
//fSelectCase is the funciton that we created earlier
in
#"Added Custom"
