Forum Discussion
Splitting Column per Category
Hi! I'm trying to split a column so that it represents one category per column. I have 2 columns: ID & Custom Field name where for example there multiple "Years of Experience" per the same ID and...
fernandoC - Seems like a grouping or maybe pivot or maybe even a transpose. Can you post sample data as text so I can test which way to go?
Hi Greg! Greg_Deckler
Please find below a text test table:
| ID | Custom Field Name | Custom Field Value |
| 10100 | Contact Date | 15/5/2020 |
| 10100 | Contact Date | 15/5/2020 |
| 10100 | Contact Date | 15/5/2020 |
| 10100 | Years of Exp. | 1-3 |
| 10100 | Years of Exp. | 1-3 |
| 10100 | Years of Exp. | 1-3 |
| 10200 | Contact Date | 20/3/2020 |
10200 | Contact Date | 20/3/2020 |
| 10200 | Contact Date | 20/3/2020 |
| 10200 | Years of Exp. | 2-5 |
| 10200 | Years of Exp. | 2-5 |
| 10200 | Years of Exp. | 2-5 |
The ideal would be to have the following:
| ID | Custom Field 1 | Custom Field Value | Custom Field 2 | Custom Field Value |
| 10100 | Contact Date | 15/5/2020 | Years of Experience | 1-3 |
| 10200 | Contact Date | 20/3/2020 | Years of Experience | 2-5 |
fernandoC So a matrix visualization like this? PBIX is attached, Page 8. This uses you source data as posted, no transformation.
Hi Greg! Greg_Deckler
Yes great! that would be the ideal outcome. I would like to have level of order in my raw data as I will be using those columns for multiple visualizations.
Thanks again for your help.
Best,
fernandoC So solved? Sorry not clear on level of order.