Grouping or distinct values

[Billy_1979]

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 is no Top 30 in the entry then count it as other.

At the moment as you can see below my query is bring back every that is not a Top 30 as other. so even if i count its by distinct by the id i will be double count the entry.

i hope that makes sense please find below a screen shot of the data. 

Thanks,

[Akash_Varuna]

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]

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

[Akash_Varuna]

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"

[Anonymous · Accepted solution]

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]

This Solution worked perfectly thanks for your help. @Anonymous