cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
ovetteabejuela
Impactful Individual
Impactful Individual

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

Sorry, I'm more of a VBA guy....

 

Looking for something similar in PowerQuery.

 

I'd like to categorize by Tenure...

1 to 30

31 to 60

60 to 90

90 and above

 

some sort...

 

Thank you.

2 ACCEPTED SOLUTIONS
dkay84_PowerBI
Microsoft
Microsoft

In Power Query, add a custom column with the syntax:

 

if [Tenure] <= 30 then "1 to 30" else if [Tenure] >30 and [Tenure] <= 60 then "31 to 60" ...

 

You can also bucket items from the desktop UI or with DAX, rather than use Power Query.

View solution in original post

v-sihou-msft
Microsoft
Microsoft

@ovetteabejuela

 

You can also use DAX to add a calculated column like:

 

Buckets =
IF (
    Table[Tenure] > 1,
    IF (
        Table[Tenure] <= 30,
        "1 to 30",
        IF (
            Table[Tenure] <= 60,
            "31 to 60",
            IF ( Table[Tenure] <= 90, "61 to 90", "90 or above" )
        )
    )
)

If you need to do segmentation on your data, please refer to blogs below:

 

 

http://www.daxpatterns.com/static-segmentation

http://www.daxpatterns.com/dynamic-segmentation

 

Regards.

View solution in original post

4 REPLIES 4
Anonymous
Not applicable

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"

 

v-sihou-msft
Microsoft
Microsoft

@ovetteabejuela

 

You can also use DAX to add a calculated column like:

 

Buckets =
IF (
    Table[Tenure] > 1,
    IF (
        Table[Tenure] <= 30,
        "1 to 30",
        IF (
            Table[Tenure] <= 60,
            "31 to 60",
            IF ( Table[Tenure] <= 90, "61 to 90", "90 or above" )
        )
    )
)

If you need to do segmentation on your data, please refer to blogs below:

 

 

http://www.daxpatterns.com/static-segmentation

http://www.daxpatterns.com/dynamic-segmentation

 

Regards.

Thanks those are valuable info and links...

dkay84_PowerBI
Microsoft
Microsoft

In Power Query, add a custom column with the syntax:

 

if [Tenure] <= 30 then "1 to 30" else if [Tenure] >30 and [Tenure] <= 60 then "31 to 60" ...

 

You can also bucket items from the desktop UI or with DAX, rather than use Power Query.

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors