Forum Discussion

KathyO's avatar
KathyO
Frequent Visitor
2 years ago

Column not grouping correctly

So, I have a data source from a survey that has numerous answers for one field, separated by semi-colons, like this:

I have managed to split and sort the answers like this (below), but you can see when I sort it, it's not quite grouping the responses together correctly:

So, when I graph it, it looks like this, with multiple entries for some responses (e.g. Monthly plant/offsite audits appears twice with 3 responses for one instance, and 2 responses for the other instance, instead of 5 in total):

I think maybe it's something to do with the way I have split the table with the semicolon, with some potentially having a 'space' beforehand and others not? Help please!

 

 

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    2 years ago

    KathyO , Based on what I got, Create sub category index in the power query use a measure on top of that on the tooltip, and sort based on that to create max (Table[Sub index])

     

    Power BI and Power Query- Sub Category Rank and Index: https://youtu.be/tMBviW4-s4A

  • KathyO's avatar
    KathyO
    Frequent Visitor
    2 years ago

    Thanks for your help... but I tried just using the "Trim Text" option in Power Query to remove the space from the start of the text after splitting, which worked a treat.  Simple solution... just took me a while to find it!