Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

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
Employee
Employee

@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
Employee
Employee

@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...

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
Fabcon_Europe_Social_Bogo

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Find out what's new and trending in the Fabric Community.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.