Transform rows to columns

Question

&nbsp;How can I transform rows 1-6 into columns so that I can correlate them with the plant.&nbsp;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.&nbsp;Currently, it will be hard for me to relate the data in Plant 1 to the rows 1-6.&nbsp;&nbsp;ActualActualActualActualForecastForecastForecastForecast&nbsp;JunJunJunJunJulyAugustSeptemberOctober&nbsp;20202020202020202020202020202020Year to DateYear to DateYear to DateYear to DateYear to DateYear to DateYear to DateYear to DateAccount 1Account 2Account 3Account 4Account 1Account 2Account 3Account 4Expense 1Expense 2Expense 3Expense 4Expense 1Expense 2Expense 3Expense 4Plant 10.00.00.00.00.02,253.00.00.0Plant 20.0561,411.0451,152.00.00.05,254.00.04,451.0Plant 39,774.645,411.00.00.00.09,883.145,111.00.0Plant 45,624.30.00.00.00.00.022.00.0Plant 50.00.00.00.00.00.00.00.0Plant 60.025,200.00.00.0192.90.00.04,551.0Plant 75,000.048,636.152,614.00.04,356.644,141.00.00.0Plant 83,200.020,194.852,561.03,547.59,941.50.08,145.00.0&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

lbendlin · Accepted Answer

First step - eliminate the blanks in the header.&nbsp;TypeActualActualActualActualForecastForecastForecastForecastMonthJunJunJunJunJulyAugustSeptemberOctoberYear20202020202020202020202020202020MeasureYear to DateYear to DateYear to DateYear to DateYear to DateYear to DateYear to DateYear to DateAccountAccount 1Account 2Account 3Account 4Account 1Account 2Account 3Account 4ExpenseExpense 1Expense 2Expense 3Expense 4Expense 1Expense 2Expense 3Expense 4Plant 1000002,253.0000Plant 20561,411.00451,152.00005,254.0004,451.00Plant 39,774.6045,411.000009,883.1045,111.000Plant 45,624.3000000220Plant 500000000Plant 6025,200.0000192.9004,551.00Plant 75,000.0048,636.1052,614.0004,356.6044,141.0000Plant 83,200.0020,194.8052,561.003,547.509,941.5008,145.000&nbsp;Then you can start transposing and unpivoting&nbsp;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"

Forum Discussion

Transform rows to columns

2 Replies

Related content

Transform columns / type

Transpose/ Transform Rows to Columns

Split and transform columns into rows

Select Certain Row to Transform Into Column

Transforming columns into row values

	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

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