Splitting Column per Category

[fernandoC]

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,

[amitchandak]

@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

[Greg_Deckler]

@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?

[fernandoC]

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

[Greg_Deckler]

@fernandoC So a matrix visualization like this? PBIX is attached, Page 8. This uses you source data as posted, no transformation.

[fernandoC]

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,

[Greg_Deckler]

@fernandoC So solved? Sorry not clear on level of order.

[fernandoC]

Hi @Greg_Deckler ,

So, It works great using a Matrix Visual, however, when I try to use it for a bar chart it doesn't work since the structure of the data needs to be as displayed on the Matrix Visual. 

So I've created a measure to count Hires that are equal to "Issue Type=Offer" & "Custom Field Value = Accepted", and I want to filter them by Disciplines (which are currently under "Custom Field Name") the issue comes when using the axis "Custom Field Name" as both "Accepted & Disciplines" come from the same column.  Hope it makes sense.