Power Query: Select...Case...End Select

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"