cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lotus22
Helper II
Helper II

Transform rows to columns

lotus22_0-1596138784270.png

 

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.

 

 ActualActualActualActualForecastForecastForecastForecast
 JunJunJunJunJulyAugustSeptemberOctober
 20202020202020202020202020202020
Year to DateYear to DateYear to DateYear to DateYear to DateYear to DateYear to DateYear to Date
Account 1Account 2Account 3Account 4Account 1Account 2Account 3Account 4
Expense 1Expense 2Expense 3Expense 4Expense 1Expense 2Expense 3Expense 4
Plant 10.00.00.00.00.02,253.00.00.0
Plant 20.0561,411.0451,152.00.00.05,254.00.04,451.0
Plant 39,774.645,411.00.00.00.09,883.145,111.00.0
Plant 45,624.30.00.00.00.00.022.00.0
Plant 50.00.00.00.00.00.00.00.0
Plant 60.025,200.00.00.0192.90.00.04,551.0
Plant 75,000.048,636.152,614.00.04,356.644,141.00.00.0
Plant 83,200.020,194.852,561.03,547.59,941.50.08,145.00.0
         

 

1 ACCEPTED SOLUTION
lbendlin
Super User III
Super User III

First step - eliminate the blanks in the header.

 

TypeActualActualActualActualForecastForecastForecastForecast
MonthJunJunJunJunJulyAugustSeptemberOctober
Year20202020202020202020202020202020
MeasureYear to DateYear to DateYear to DateYear to DateYear to DateYear to DateYear to DateYear to Date
AccountAccount 1Account 2Account 3Account 4Account 1Account 2Account 3Account 4
ExpenseExpense 1Expense 2Expense 3Expense 4Expense 1Expense 2Expense 3Expense 4
Plant 1000002,253.0000
Plant 20561,411.00451,152.00005,254.0004,451.00
Plant 39,774.6045,411.000009,883.1045,111.000
Plant 45,624.3000000220
Plant 500000000
Plant 6025,200.0000192.9004,551.00
Plant 75,000.0048,636.1052,614.0004,356.6044,141.0000
Plant 83,200.0020,194.8052,561.003,547.509,941.5008,145.000

 

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"

View solution in original post

2 REPLIES 2
lbendlin
Super User III
Super User III

First step - eliminate the blanks in the header.

 

TypeActualActualActualActualForecastForecastForecastForecast
MonthJunJunJunJunJulyAugustSeptemberOctober
Year20202020202020202020202020202020
MeasureYear to DateYear to DateYear to DateYear to DateYear to DateYear to DateYear to DateYear to Date
AccountAccount 1Account 2Account 3Account 4Account 1Account 2Account 3Account 4
ExpenseExpense 1Expense 2Expense 3Expense 4Expense 1Expense 2Expense 3Expense 4
Plant 1000002,253.0000
Plant 20561,411.00451,152.00005,254.0004,451.00
Plant 39,774.6045,411.000009,883.1045,111.000
Plant 45,624.3000000220
Plant 500000000
Plant 6025,200.0000192.9004,551.00
Plant 75,000.0048,636.1052,614.0004,356.6044,141.0000
Plant 83,200.0020,194.8052,561.003,547.509,941.5008,145.000

 

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"

View solution in original post

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.

 

 

Imke Feldmann (The BIccountant)

If you liked my solution, please give it a thumbs up. And if I did answer your question, please mark this post as a solution. Thanks!

How to integrate M-code into your solution -- How to get your questions answered quickly -- How to provide sample data -- Check out more PBI- learning resources here -- Performance Tipps for M-queries

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors