PowerQuery Unpivot top rows

[hnguyen76]

Good morning,

Hopefully this is an easy solution to fix but I can't seem to wrap my head around it. I have a source file that gives me data in this format:

Column1	Column2	Column3	Column4	Column5
	2019	2020	2020	2020
	12	01	02	03
ID203	1000	1010	1015	1020
ID205	500	550	600	650

The first row has my year and the second row has my months. I want to dynamically unpivot the data so it could look like this:

Column1	Column2	Column3	Column4
ID203	2019	12	1000
ID203	2020	01	1010
ID203	2020	02	1015
ID203	2020	03	1020
ID205	2019	12	500
ID205	2020	01	550
ID205	2020	02	600
ID205	2020	03	650

I drill adding a calculated column and using drilldown but it doesn't seem to give me the right results. Any help would be greatly appreciated!

[mwegener]

Hi @hnguyen76 ,

1. Transpose Table

2. Replace Values with Header Names

3. Use First Row as Header

4. Mark the ID Columns and Unpivot Columns

[hnguyen76]

Hi @mwegener ,

I just tried it with the sample example and it seems to be working. My dataset has one more column and row than the sample example.

I need to transform this:

Column1	Column2	Column3	Column4	Column5	Column6
		2019	2020	2020	2020
		12	1	2	3
		YTD	Period	Period	Period
ID203	TECH	1000	1010	1015	1020
ID205	TECH	500	550	600	650
ID203	SAM	2000	2010	2015	2040
ID205	SAM	1000	1100	1200	1300

Into this:

Year	Month	Type	Key	Level1	Value
2019	12	YTD	ID203	TECH	1000
2019	12	YTD	ID205	TECH	500
2019	12	YTD	ID203	SAM	2000
2019	12	YTD	ID205	SAM	1000
2020	1	Period	ID203	TECH	1010
2020	1	Period	ID205	TECH	550
2020	1	Period	ID203	SAM	2010
2020	1	Period	ID205	SAM	1100
2020	2	Period	ID203	TECH	1015
2020	2	Period	ID205	TECH	600
2020	2	Period	ID203	TECH	2015
2020	2	Period	ID205	TECH	1200
2020	3	Period	ID203	SAM	1020
2020	3	Period	ID205	SAM	650
2020	3	Period	ID203	SAM	2040
2020	3	Period	ID205	SAM	1300

I can't seem to make it work as this is the result I'm getting from your steps:

Year	Month	Period	Attribute	Value
			ID203	TECH
			ID205	TECH
			ID203_1	SAM
			ID205_2	SAM
2019	12	YTD	ID203	1000
2019	12	YTD	ID205	500
2019	12	YTD	ID203_1	2000
2019	12	YTD	ID205_2	1000
2020	1	Period	ID203	1010
2020	1	Period	ID205	550
2020	1	Period	ID203_1	2010
2020	1	Period	ID205_2	1100
2020	2	Period	ID203	1015
2020	2	Period	ID205	600
2020	2	Period	ID203_1	2015
2020	2	Period	ID205_2	1200
2020	3	Period	ID203	1020
2020	3	Period	ID205	650
2020	3	Period	ID203_1	2040
2020	3	Period	ID205_2	1300

[mwegener · Accepted solution]

Hi @hnguyen76 ,

check this out. 

PBIX

[hnguyen76]

Dang it. I had done this before. Totally forgot about it. Thanks @mwegener it worked! Sending you virtual hugs!