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

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

Top Solution Authors