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

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
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!:
Power BI Cookbook Third Edition (Color)

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!:
Power BI Cookbook Third Edition (Color)

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!:
Power BI Cookbook Third Edition (Color)

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
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.