Forum Discussion
Split table into columns based on unique values
Hi guys,
I'd like to split this dummy table into several columns and populate with 1's and 0's, based on the unique values in the Year/Qtr column.
Current layout
| Name | Year/Qtr |
| Dave | 2021Q1 |
| John | 2021Q1 |
| John | 2021Q2 |
| Patricia | 2021Q3 |
| Patricia | 2021Q4 |
| Claire | 2022Q1 |
| Dave | 2022Q2 |
| Claire | 2022Q3 |
| Claire | 2022Q4 |
Desired layout
| Name | 2021Q1 | 2021Q2 | 2021Q3 | 2021Q4 | 2022Q1 | 2022Q2 | 2022Q3 | 2022Q4 |
| Dave | 1 | - | - | - | - | 1 | - | - |
| John | 1 | 1 | - | - | - | - | - | - |
| Patricia | - | - | 1 | 1 | - | - | - | - |
| Claire | - | - | - | - | 1 | - | 1 | 1 |
or
| Name | 2021Q1 | 2021Q2 | 2021Q3 | 2021Q4 | 2022Q1 | 2022Q2 | 2022Q3 | 2022Q4 |
| Dave | 1 | - | - | - | - | 1 | - | - |
| John | 1 | 1 | - | - | - | - | - | - |
| John | 1 | 1 | - | - | - | - | - | - |
| Patricia | - | - | 1 | 1 | - | - | - | - |
| Patricia | - | - | 1 | 1 | - | - | - | - |
| Claire | - | - | - | - | 1 | - | 1 | 1 |
| Dave | 1 | - | - | - | - | 1 | - | - |
| Claire | - | - | - | - | 1 | - | 1 | 1 |
| Claire | - | - | - | - | 1 | - | 1 | 1 |
The only difference in the two is that the second desired layout uses the same rows as the current layout.
What's key here is that I need it to create columns based on unique values, hence no hardcoding of columns as that would force me to re-code the query as soon as a new quarter starts.
Thank you so much for the help!
1 Reply
Hi Anonymous ,
Create a new measure like this:
_countName = DISTINCTCOUNT(yourTable[Name])Then put it all into a matrix visual like this:
Pete
