Powerquery devide a columns into three equal sized groups/bins

[KristofferAJ]

Hi,

I have have a column with countries and next to them a RANK (whole number 1-65, depends on the dataset) - I would like a measure which places each country into a group or a tier.

For instance if 12 countries were part of the dataset it would look like below:

Group A (1-4)

Group B (5-8)

Group C (9-12)

Is there a simple solution to this, knowing that I need this template for different datasets, and I can't fix it as the number of countries might change..

Thanks

[Ahmedx]

or this

"Group-" & Text.From(
Number.RoundUp( [Rank] / 4,0))

[KristofferAJ]

This is very good, right now I have this one as an column-add..

Column =
VAR _Rank =
    RANKX ( 'Table', 'Table'[sales],, ASC )
VAR _Range = 3
VAR _Rows =
    COUNTROWS ( 'Table' )
RETURN
    ROUNDUP ( DIVIDE ( _Rank, DIVIDE ( _Rows, _Range ) ), 0 )

An issue I realized with both options are that if I have 10 countries which has no rank/zero/blank it includes them also, is there a way to only group the countries with values (or exclude a specific value lets say "0")

[Ahmedx]

don't understand, post an example

[Idrissshatila]

Hello @KristofferAJ ,

you can add a calculated column as the following

grouping =
IF (
    'YourTableName'[Rank] >= 1
        && 'YourTableName'[Rank] <= 5,
    "Group A",
    IF (
        'YourTableName'[Rank] >= 5
            && 'YourTableName'[Rank] <= 8,
        "Group B",
        IF ( 'YourTableName'[Rank] >= 9 && 'YourTableName'[Rank] <= 12, "Group C" )
    )
)

[KristofferAJ]

Thanks - but it wont work if my next dataset will have 40 countries... thus I need the calculation to look at the entire grou, like an ARRAY in excel

[Idrissshatila]

@KristofferAJ , it won't happen dynamically, you will always would adjust it as per your dataset.

[KristofferAJ]

Thanks, but then I need to define specific cut off values. I need powerquery always to group the countries into equal groups

12 countries

15 countries

30 countries etc

[yakamoz007]

Hi,

you can do this in the Power Query Editor by adding a conditional column.