Forum Discussion
Transform data with multiple column headers and row headers
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: