Transform rows to columns

[lotus22]

How can I transform rows 1-6 into columns so that I can correlate them with the plant.

I would like to see Actual/Forecast, Month, Year to date, Account#, and Expense in columns 2,3,4,5,6,7. I believe it will be easier to analyze the data with dashboard if they are all in columns.

Currently, it will be hard for me to relate the data in Plant 1 to the rows 1-6.

	Actual	Actual	Actual	Actual	Forecast	Forecast	Forecast	Forecast
	Jun	Jun	Jun	Jun	July	August	September	October
	2020	2020	2020	2020	2020	2020	2020	2020
Year to Date	Year to Date	Year to Date	Year to Date	Year to Date	Year to Date	Year to Date	Year to Date
Account 1	Account 2	Account 3	Account 4	Account 1	Account 2	Account 3	Account 4
Expense 1	Expense 2	Expense 3	Expense 4	Expense 1	Expense 2	Expense 3	Expense 4
Plant 1	0.0	0.0	0.0	0.0	0.0	2,253.0	0.0	0.0
Plant 2	0.0	561,411.0	451,152.0	0.0	0.0	5,254.0	0.0	4,451.0
Plant 3	9,774.6	45,411.0	0.0	0.0	0.0	9,883.1	45,111.0	0.0
Plant 4	5,624.3	0.0	0.0	0.0	0.0	0.0	22.0	0.0
Plant 5	0.0	0.0	0.0	0.0	0.0	0.0	0.0	0.0
Plant 6	0.0	25,200.0	0.0	0.0	192.9	0.0	0.0	4,551.0
Plant 7	5,000.0	48,636.1	52,614.0	0.0	4,356.6	44,141.0	0.0	0.0
Plant 8	3,200.0	20,194.8	52,561.0	3,547.5	9,941.5	0.0	8,145.0	0.0

[lbendlin · Accepted solution]

First step - eliminate the blanks in the header.

Type	Actual	Actual	Actual	Actual	Forecast	Forecast	Forecast	Forecast
Month	Jun	Jun	Jun	Jun	July	August	September	October
Year	2020	2020	2020	2020	2020	2020	2020	2020
Measure	Year to Date	Year to Date	Year to Date	Year to Date	Year to Date	Year to Date	Year to Date	Year to Date
Account	Account 1	Account 2	Account 3	Account 4	Account 1	Account 2	Account 3	Account 4
Expense	Expense 1	Expense 2	Expense 3	Expense 4	Expense 1	Expense 2	Expense 3	Expense 4
Plant 1	0	0	0	0	0	2,253.00	0	0
Plant 2	0	561,411.00	451,152.00	0	0	5,254.00	0	4,451.00
Plant 3	9,774.60	45,411.00	0	0	0	9,883.10	45,111.00	0
Plant 4	5,624.30	0	0	0	0	0	22	0
Plant 5	0	0	0	0	0	0	0	0
Plant 6	0	25,200.00	0	0	192.9	0	0	4,551.00
Plant 7	5,000.00	48,636.10	52,614.00	0	4,356.60	44,141.00	0	0
Plant 8	3,200.00	20,194.80	52,561.00	3,547.50	9,941.50	0	8,145.00	0

Then you can start transposing and unpivoting

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vVHBSsQwEP2VpechJJNJ2h4X1IMgCnqRsodagh5qu2xTcP/eJG1Ndukqe/GQ9s3w5uW9SVVlL8e9ySDbNnas29/BXX8wTT3Yv+EOquyh7+yHa96P3YVve/Tq4/sYhp/N3prPN3Nw+LGxvUde59XUvoUc+fW/YMTUw3jwIb3Uxvabm9r+Y+k9bJumHzsbthnQRiQYEywTTFfz/V23X3vTDd7GjMLsgjHBMsF0Nd/f9dTWsze+chBQScZjK47g3FNaAAkxkUgJEArTCcdwIhRbBI7ly6jlbZWQ58T0pBIV01NCUUgmZoqIlKhE4T6NxORanpAJz2bUBeJaar2IuFCcn1oUJbIyqQnUedA82OPLJBWgpZ4SKQQtTtYklZ73QSBIrD9D4WoZvSAHURIrZkX3OFNfgqKcqWmLpRNTi1jhtNXPInff", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t, Column3 = _t, Column4 = _t, Column5 = _t, Column6 = _t, Column7 = _t, Column8 = _t, Column9 = _t]),
    #"Transposed Table" = Table.Transpose(Source),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Promoted Headers", {"Expense", "Account", "Measure", "Year", "Month", "Type"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

[ImkeF]

Hi @lotus22 

I've written an article here with a function that lets you achieve this task easily: https://www.thebiccountant.com/2017/06/19/unpivot-by-number-of-columns-and-rows-in-powerbi-and-power...

Please also check the enclosed file on how to apply it to your case.