Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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,
@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
@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.
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
72 | |
62 | |
51 | |
48 |
User | Count |
---|---|
204 | |
86 | |
62 | |
59 | |
56 |