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.
Hi Anonymous
What do you mean exactly by transpose rows 1 and 2? Can you show what the the final desired table would look like?
Can you also paste here the sample of the initial table you have above in text format (rather than screen cap) so that we can give you a full solution?
Cheers
|
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. |
Hello AlB , I have updated my original post, thank you.