cancel
Showing results for 
Search instead for 
Did you mean: 
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
jborro
Solution Sage
Solution Sage

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.

View solution in original post

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.

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors