Forum Discussion

Helper III

6 years ago

Solved

Transform rows to columns

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,...

Need Help

lbendlin

6 years ago

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"

lbendlin

Super User

6 years ago

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"