Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
fernandoC
Helper V
Helper V

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. 

 

fernandoC_0-1599828650013.png

I would like to have the following: 

 

fernandoC_1-1599830102082.png

 

Instead of:

 

fernandoC_2-1599830233114.png

 

Hope it makes sense, any additional information required please let me know.

 

Best,

 

 

7 REPLIES 7
amitchandak
Super User
Super User

@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
Super User
Super User

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


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Hi Greg! @Greg_Deckler 

 

Please find below a text test table:

 

IDCustom Field NameCustom Field Value
10100Contact Date15/5/2020
10100Contact Date15/5/2020
10100Contact Date15/5/2020
10100Years of Exp.1-3
10100Years of Exp.1-3
10100Years of Exp.1-3
10200Contact Date20/3/2020

10200

Contact Date20/3/2020
10200Contact Date20/3/2020
10200Years of Exp.2-5
10200Years of Exp.2-5
10200Years of Exp.2-5

 

 

The ideal would be to have the following:

IDCustom Field 1Custom Field ValueCustom Field 2Custom Field Value
10100Contact Date15/5/2020Years of Experience1-3
10200Contact Date20/3/2020Years of Experience2-5

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

Greg_Deckler_0-1599835307335.png

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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.


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

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. 

 

fernandoC_0-1599840780998.png

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. 

 

fernandoC_1-1599841034707.png

 

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.