Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Greetings,
I have an excel sheet as my data source and the owner of this sheet arranged the data so that it has multiple column headers and a couple of row headers. As a bonus, they made they made it so new data is added to columns and not rows. I want to transpose and unpivot all this data. It looks like this picture and it goes on to the right through December. There are also several more locations.
I tried transposing and unpivoting but the location column always seems to cause issues. How can I get it to look like this in query?
(I don't need the day of the week since I can use a date table for that join info.)
Here is a sample of the data.
Location | Supplier | 3/4/2024 | 3/4/2024 | 3/4/2024 | 3/5/2024 | 3/5/2024 | 3/5/2024 |
Mon | Mon | Mon | Tue | Tue | Tue | ||
1st | 2nd | 3rd | 1st | 2nd | 3rd | ||
Atlanta | Supplier 1 | 1% | 3% | 1% | 3% | ||
Supplier 2 | 2% | 3% | 2% | 3% | |||
Supplier 3 | 2% | 3% | 4% | 2% | 3% | 4% | |
Supplier 4 | 2% | 3% | 2% | 3% | |||
Houston | Supplier 3 | 5% | 6% | 5% | 6% | ||
Supplier 4 | 5% | 6% | 5% | 6% | |||
Supplier 5 | 5% | 6% | 5% | 6% | |||
Supplier 6 | 5% | 6% | 7% | 5% | 6% | 7% |
Thanks!
Solved! Go to Solution.
The top row will add a suffix to each date field automatically (.1,.2) if you use them as column headers so you can use that to your advantage.
Filter out the 2nd and 3rd rows above (Mon, Tues 1st, 2nd)
Then perform a Fill Down on Location (you'll need to get null values in the column to make that work).
Now do the Unpivot on all columns except the first 2.
There will be a few steps more but you can get help by searching the forum for Unpivot multiple columns.
That's the point of my first sentence. When you unpivot, there will be a suffix on the date field which you can separate out to use as shift column
Unfortunately the suffixes are sequential so it was a bit tougher. The first shifts were always just the date while the second and third shifts increased in number:
So I split on the "_" to get this:
Then I added a conditional column:
= Table.AddColumn(#"Renamed Columns1", "Custom", each if Number.Mod([Shift],2) = 0 then "3rd" else if [Shift] = null then "1st" else "2nd")
Which gave me the final results in:
The top row will add a suffix to each date field automatically (.1,.2) if you use them as column headers so you can use that to your advantage.
Filter out the 2nd and 3rd rows above (Mon, Tues 1st, 2nd)
Then perform a Fill Down on Location (you'll need to get null values in the column to make that work).
Now do the Unpivot on all columns except the first 2.
There will be a few steps more but you can get help by searching the forum for Unpivot multiple columns.
Hi @HotChilli . Thanks for the reply.
Why would I also remove the third row which has 1st, 2nd and 3rd? I need those to know the shift.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
58 |
User | Count |
---|---|
188 | |
111 | |
105 | |
78 | |
71 |