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 instanc...
Need Help
Show and Tell
Tips and Tricks
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