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 I would only like to have one ID with multiple columns representing each one of the categories.
I would like to have the following:
Instead of:
Hope it makes sense, any additional information required please let me know.
Best,
7 Replies
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.
fernandoC , looking at data difficult to tell.
But Unpivot can help : https://radacad.com/pivot-and-unpivot-with-power-bi
Or You may create an aggregated table
