Forum Discussion

KristofferAJ's avatar
KristofferAJ
Icon for Helper III rankHelper 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
Idrissshatila's avatar
Idrissshatila
Icon for Super User rankSuper 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
    Icon for Helper III rankHelper 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

    • Idrissshatila's avatar
      Idrissshatila
      Icon for Super User rankSuper User
      2 years ago

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