Forum Discussion

Billy_1979's avatar
Billy_1979
Frequent Visitor
1 year ago
Solved

Grouping or distinct values

Hi I have a data set below.   i have a number of entries for 1 id number - what i am trying to do is if the id number as top 30 entry then group that as Top30 and don't group the rest, but if there...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Billy_1979,

    I think you should modify your M query as below:

    if List.Contains([All Data][Sticker ID], "Top 30") then "Top30" else "Other"

    I have made a simple test for your reference:

    Please check the attached .pbix file for details.

     

    Best Regards,
    Qi
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

     

Akash_Varuna's avatar
Akash_Varuna
Super User
1 year ago

Hi Billy_1979 , Could you please try these 

  • Group by ID: Go to Group By → Group by ID→ Select All Rows
  • Add Custom Column:
    if List.Contains(Table.Column([All Data], "Category"), "Top30") then "Top30" else "Other"
  • Expand Data: Expand grouped rows and use the new column for filtering or classification.
    Alternatively you could use sql to achive this with CASE clause 
    If this post helped please do give a kudos and accept this as a solution
    Thanks In Advance
Billy_1979's avatar
Billy_1979
Frequent Visitor
1 year ago

Hi Akash_Varuna 

 

So i have grouped the data 

 

Then i created the custom column,

 

 

When i do this im getting alot of duplicate entries.

 i think i may have missed a step or the logic i have put in is incorrect.

 

Thanks

 

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Billy_1979,

    I think you should modify your M query as below:

    if List.Contains([All Data][Sticker ID], "Top 30") then "Top30" else "Other"

    I have made a simple test for your reference:

    Please check the attached .pbix file for details.

     

    Best Regards,
    Qi
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

     

    • Billy_1979's avatar
      Billy_1979
      Frequent Visitor
      1 year ago

      This Solution worked perfectly thanks for your help. Anonymous 

  • Akash_Varuna's avatar
    Akash_Varuna
    Super User
    1 year ago

    Hi Billy_1979 , Instead of category put your required column Sticker id or any other 
    eg : if List.Contains([All Data][Sticker ID], "Top30") then "Top30" else "Other"