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 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,

 

 

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!

     

5 Replies

  • 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!