Forum Discussion

Anne14's avatar
Anne14
Frequent Visitor
2 years ago
Solved

Need to group values

Hello,

 

I need your help. I have a column with many values from 0 to 200 and I need to group them as following:

If the value is between 0 and 20 in a new column called " Grouped values" I want to dispay as text "0-20", in other one if it is between 20-40 I want to display '20-40" etc. I tried to make a conditionnal column in power query  but there is no option "between" so I can't make more than 2 groups and I need 5. 

Thanks for your help

 

  • DataNinja777's avatar
    DataNinja777
    2 years ago

    Anne14 ,

     

    You can produce the required output by writing a calculated column like the one below:

    Bucket = switch(true(),[Value]<=20,"1-20",
[Value]<=40,"21-40",
[Value]<=60,"41-60",
[Value]<=80,"61-80",
[Value]<=100,"81-100",
[Value]<=120,"101-120",
[Value]<=140,"121-140",
[Value]<=160,"141-160",
[Value]<=180,"161-180",
[Value]<=200,"181-200")

    The resultant output is the bucketing of numbers as shown below:

    I have attached an example pbix file.  

    Best regards,

     

2 Replies

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

    Anne14 ,

     

    You can produce the required output by writing a calculated column like the one below:

    Bucket = switch(true(),[Value]<=20,"1-20",
[Value]<=40,"21-40",
[Value]<=60,"41-60",
[Value]<=80,"61-80",
[Value]<=100,"81-100",
[Value]<=120,"101-120",
[Value]<=140,"121-140",
[Value]<=160,"141-160",
[Value]<=180,"161-180",
[Value]<=200,"181-200")

    The resultant output is the bucketing of numbers as shown below:

    I have attached an example pbix file.  

    Best regards,