cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ovetteabejuela Established Member
Established Member

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

Accepted Solutions
dkay84_PowerBI New Contributor
New Contributor

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

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

Moderator v-sihou-msft
Moderator

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

@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
dkay84_PowerBI New Contributor
New Contributor

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

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

Moderator v-sihou-msft
Moderator

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

@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

ovetteabejuela Established Member
Established Member

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

Thanks those are valuable info and links...

jborro Member
Member

Re: 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"

 

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 242 members 2,883 guests
Please welcome our newest community members: