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