Forum Discussion
Helper ICounting unique text occurrences across multiple columns?
You do this in this query editor:
1) Load your data-table
2) Check column "Comment"
3) Rightclick your mouse: "Unpivot Other Columns"
4) Check column "Value"
5) Rightclick mouse: "Group By" and just click "OK" (don't change the defaults here)
This will deliver your table below. No need for the lookuptable so far. (But you can join it if you want to show the other values as well. The operation is called "Merge" in the PBI menu)
Community ChampionYou do this in this query editor:
1) Load your data-table
2) Check column "Comment"
3) Rightclick your mouse: "Unpivot Other Columns"
4) Check column "Value"
5) Rightclick mouse: "Group By" and just click "OK" (don't change the defaults here)
This will deliver your table below. No need for the lookuptable so far. (But you can join it if you want to show the other values as well. The operation is called "Merge" in the PBI menu)
Helper IThanks, Imke!
That was just the nudge in the direction that I needed.
My actual dataset has loads of columns on either side of the array that's similar to what I shared above. I ended up multiselecting all of the Comment_Code_n columns and used Unpivot to flatten them. Then I related the resulting Values column with Comment_Code in the Dictionary table which gave me that counts that I needed. Though it wasn't part of the stated problem, I was able to use a discrete row identifier to get distinct counts of unique respondents too.
Thanks again for your help!
- Russ