Forum Discussion

KristofferAJ's avatar
KristofferAJ
Helper III
2 years ago

Powerquery devide a columns into three equal sized groups/bins

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

 

 

Need Help
Show and Tell
Tips and Tricks

8 Replies

  • Ahmedx's avatar
    Ahmedx
    Super User
    2 years ago

    or this

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

     

    • KristofferAJ's avatar
      KristofferAJ
      Helper III
      2 years ago

      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's avatar
        Ahmedx
        Super User
        2 years ago

        don't understand, post an example

  • yakamoz007's avatar
    yakamoz007
    Helper I
    2 years ago

    Hi,

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

     

  • KristofferAJ's avatar
    KristofferAJ
    Helper III
    2 years ago

    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

  • Idrissshatila's avatar
    Idrissshatila
    Super User
    2 years ago

    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's avatar
      KristofferAJ
      Helper III
      2 years ago

      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