Forum Discussion
Transpose 2 rows
Hi all, would it be possible for to transpose my row 1 and 2, and make my row 3 the header? Thank you. This is how I hope it would look like. Here is a sample data. Mont...
Anonymous
Place the following M code in a blank query to see the steps. Note the first two steps are only to replicate your sample input data
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vVBNa8MwDP0rJqcWCrNlKR+7LtCMkbV02WGUHkJnmtLWBies7N/st+yXTfbo2A6jt4DRexJ6T5LX6ySZJbWzQ3crSrMVIFXBlStvM4u6l6b8r6Vuys+Pv4I731rDydL1HB/NjuO93brT3u7ExJphGld5M31UVm7fD4yNd8ejeWc2b+3gA4lOYmEn1eJ59TSN7Q/nTnjTvrIZZ4dzd3MKVj/dzorKXwYIXi/utFzVmZKK6xop/QYixpRSQsYizUgzKshygkAoLygIVCElSSYASmH4NCAAzAPJtcYsuClEvNgi/RoJY4+E8a+E8a/UI165+QI=", 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, Column10 = _t, Column11 = _t, Column12 = _t, Column13 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}}), month_ = #"Changed Type"[Column2]{0}, period_ = #"Changed Type"[Column2]{1}, removeTop_ = Table.Skip(#"Changed Type",2), #"Promoted Headers" = Table.PromoteHeaders(removeTop_, [PromoteAllScalars=true]), #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Crane", type text}, {"Pos", Int64.Type}, {"Neg", Int64.Type}, {"Incoming (net)", Int64.Type}, {"Moves ", Int64.Type}, {"Hoist", Int64.Type}, {"Trolley", Int64.Type}, {"Gantry", Int64.Type}, {"Crane On(HOURS) ", Int64.Type}, {"Kwh reading", Int64.Type}, {"kwh/moves", Int64.Type}, {"Crane on Hr", Int64.Type}, {"Moves MTD", Int64.Type}}), #"Added Custom" = Table.AddColumn(#"Changed Type1", "Month", each Text.AfterDelimiter(month_, "Month: "), type text), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Period", each period_, type text), #"Reordered Columns" = Table.ReorderColumns(#"Added Custom1",{"Crane", "Month", "Period", "Pos", "Neg", "Incoming (net)", "Moves ", "Hoist", "Trolley", "Gantry", "Crane On(HOURS) ", "Kwh reading", "kwh/moves", "Crane on Hr", "Moves MTD"}) in #"Reordered Columns"Please accept the solution when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Anonymous
Place the following M code in a blank query to see the steps. Note the first two steps are only to replicate your sample input data
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("vVBNa8MwDP0rJqcWCrNlKR+7LtCMkbV02WGUHkJnmtLWBies7N/st+yXTfbo2A6jt4DRexJ6T5LX6ySZJbWzQ3crSrMVIFXBlStvM4u6l6b8r6Vuys+Pv4I731rDydL1HB/NjuO93brT3u7ExJphGld5M31UVm7fD4yNd8ejeWc2b+3gA4lOYmEn1eJ59TSN7Q/nTnjTvrIZZ4dzd3MKVj/dzorKXwYIXi/utFzVmZKK6xop/QYixpRSQsYizUgzKshygkAoLygIVCElSSYASmH4NCAAzAPJtcYsuClEvNgi/RoJY4+E8a+E8a/UI165+QI=", 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, Column10 = _t, Column11 = _t, Column12 = _t, Column13 = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}, {"Column7", type text}, {"Column8", type text}, {"Column9", type text}, {"Column10", type text}, {"Column11", type text}, {"Column12", type text}, {"Column13", type text}}),
month_ = #"Changed Type"[Column2]{0},
period_ = #"Changed Type"[Column2]{1},
removeTop_ = Table.Skip(#"Changed Type",2),
#"Promoted Headers" = Table.PromoteHeaders(removeTop_, [PromoteAllScalars=true]),
#"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Crane", type text}, {"Pos", Int64.Type}, {"Neg", Int64.Type}, {"Incoming (net)", Int64.Type}, {"Moves ", Int64.Type}, {"Hoist", Int64.Type}, {"Trolley", Int64.Type}, {"Gantry", Int64.Type}, {"Crane On(HOURS) ", Int64.Type}, {"Kwh reading", Int64.Type}, {"kwh/moves", Int64.Type}, {"Crane on Hr", Int64.Type}, {"Moves MTD", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type1", "Month", each Text.AfterDelimiter(month_, "Month: "), type text),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Period", each period_, type text),
#"Reordered Columns" = Table.ReorderColumns(#"Added Custom1",{"Crane", "Month", "Period", "Pos", "Neg", "Incoming (net)", "Moves ", "Hoist", "Trolley", "Gantry", "Crane On(HOURS) ", "Kwh reading", "kwh/moves", "Crane on Hr", "Moves MTD"})
in
#"Reordered Columns"
|
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |