Transform data with multiple column headers and row headers

[rahkim]

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!

[HotChilli]

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

[rahkim]

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:

[HotChilli · Accepted 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.

[rahkim]

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.