Forum Discussion
Counting 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)
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)
Thanks, 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
Do you know how to get a count based upon a concatenation of several columns without using the Group By function in Query Editor? I'm trying to do a count based upon 3 columns in a table but need to have the flexibility to show other columns associated with the data. See my examples below for clarification. I am very new to Power Bi and spinning my wheels on this one.
Table Name Race Gender College Graduate High School Graduate GED Frank W M N Y N Frank W M Y Y N Alice W F Y Y N Alice W F N N Y John W M N Y N John B M Y Y N View if all columns are displayed Name Race Gender College Graduate High School Graduate GED Count of Name, Race, Gender Frank W M N Y N 1 Frank W M Y Y N 1 Alice W F Y Y N 1 Alice W F N N Y 1 John W M N Y N 1 John B M Y Y N 1 View if first three columns are displayed Name Race Gender Count of Name, Race, Gender Frank W M 2 Alice W F 2 John W M 1 John B M 1
