Shaping data - get column headers in rows, when there are existing row headers

[Debround]

Hello,

I hope I can explain myself in an understandable way. I've got a table like the one below to record the number of hours worked on different projects.

			January	January	February	February	March	March	April	April	May	May	June	June	July	July	August	August	September	September	October	October	November	November	December	December
Year	Project	Role	Office 1	Office 2	Office 1	Office 2	Office 1	Office 2	Office 1	Office 2	Office 1	Office 2	Office 1	Office 2	Office 1	Office 2	Office 1	Office 2	Office 1	Office 2	Office 1	Office 2	Office 1	Office 2	Office 1	Office 2
2010	Columbia	Manager	3		2				1		1	3		3		1		2		2		1		1		3
2010	Endevour	Technician		5	1		5	9	3	9		9	8		5	4	1	3	12		2		2		1
2011	Endevour	Manager	3		2				1		1	3		3		1		2		2		1		1		3
2011	Discovery	Technician		5	1		5	9	3	9		9	8		5	4	1	3	12		2		2		1

Obviously, the original one has a lot more of years, projects, and roles, but has the same months and offices (2 split in months). This format was quite convenient at the time it was created, as it was printed directly. But what I want now is to transform all these years of Excels, in just a table like the following; where I've got just the data I need, shaped in a way I can easily represent and filter it.

Year	Project	Role	Month	Office	Hours
2010	Columbia	Manager	January	1	3
2010	Columbia	Manager	February	1	2
2010	Columbia	Manager	April	1	1
2010	Endevour	Technician	January	2	5
2010	Endevour	Technician	February	1	1
2010	Endevour	Technician	March	1	5

I've tried transpose and other commands either in Excel or Power Query, without the desired result. I'll appreciate any help regarding this matter, thanks in advance.

[HotChilli · Accepted solution]

If you know how to use a custom function , it's here

https://www.thebiccountant.com/2017/06/19/unpivot-by-number-of-columns-and-rows-in-powerbi-and-power... 

[Debround]

Thanks, @HotChilli and @C4YNelis for your responses.

It's a one-off, but I have to say that the custom function attracted me more, and it's faster!

[C4YNelis]

@HotChilli The unpivot function is definitely the best way if you do insist on using Power BI to tackle this problem (and a great function to know anyhow).

However, imho, if it's just a one time transformation only (and this is especially true if you are planning on using this same data more often in the future), I believe it's better to just clean the structure of your source data as much as possible (if you have that opportunity), rather than use heavy queries to do so repeatedly for every refresh in the future. It's kind of like killing a mosquito with a cannon.

Just my two cents. 🙂

[C4YNelis]

Hi @Debround,

there are actually a lot of ways you could "easily" do this in Excel (I understand you need this table transformed just once)? In that case I wouldn't worry too much about formula's and complex ways.

If you have only twenty-four columns with months and offices, the easiest way I see is to actually copy your data in twelve or thirteen (depending on your final desired result) copy-paste actions to a new table on a different sheet. Just copy each month (including the first columns with years, projects and roles) with both offices to a new target sheet and in your sourcefile (I strongly recommend you make a backup of this file before you start), you delete the two columns with the offices for the first month that you copied (so that would be the column for January (Office 1 and 2). After this, you copy your data again, paste it below the previous set (which had two columns more on the right side) and again, you delete one month (two columns) on the left in your sourcefile (February (Office 1 and 2)). Continue this until you have copied your last set.

In the end you can deside whether you want the offices next to each other in separate columns, or that you make an additional column (e.g. titled "Office") where you copy your value (office 1 or office 2). If that's what you want, you'll need to copy one last time, and manually add the values for that last column (office names).

After you are done, you should have a target file that looks somewhat like a triangle upside down. Now delete all the additional month / office columns on the right side (except for the ones on the left that you wish to keep) and you're good to go.

If you do it the right way, I'm pretty sure it's a faster method than doing this any automated way (as long as it's a one-time thing).

Good luck!

Cheers,

Niels

(edited, because I just realized I made a little mistake in my explanation, not accounting for two columns per month.)