Transpose 2 rows

[Anonymous]

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.

	Month: Dec 2019
	YTD								MTD
Crane	Pos	Neg	Incoming (net)	Moves	Hoist	Trolley	Gantry	Crane On(HOURS)	Kwh reading	kwh/moves	Crane on Hr	Moves MTD
PRM7101	3456	34555	65654	96753	127852	158951	190050	221149	252248	283347	314446	345545
PRM7102	3456	34555	65654	96753	127852	158951	190050	221149	252248	283347	314446	345545
PRM7201	3456	34555	65654	96753	127852	158951	190050	221149	252248	283347	314446	345545
PRM7202	3456	34555	65654	96753	127852	158951	190050	221149	252248	283347	314446	345545
PRM7301	3456	34555	65654	96753	127852	158951	190050	221149	252248	283347	314446	345545

[v-yanjiang-msft]

Hi @Anonymous ,

Is your problem solved?? If so, Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

Best Regards,
Community Support Team _ kalyj

[AlB · Accepted solution]

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

[AlB]

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.

[Anonymous]

Hello @AlB , I have updated my original post, thank you.