Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

PQ question - change rows to columns

Hi, Can anyone help me out with some applied steps for the below? I've tried a mixture of transpose, promote first row to header, unpivot, etc; however, so far am not having any luck! In the sa...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    1 year ago

    After loading your table, you can insert this step where Source is your previous step

    = Table.FromRecords(List.Transform(Table.Split(Source, 3), (x)=> Record.FromList(x[Column2], x[Column1])))

     

    The sample code in action here

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi4pTUnNK1HwS8xNVdJRclSK1YlWcs4vLSpOVQjJLMkBC0JEXRJLUhU8i4tLU1OAgoYG+oZG+kYGRiZgSTRznLCZ4+SExRwTfUNDnMY4YzPG2RmHc8DmmGIzxwWbOS4u+MwxVoqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    Result = Table.FromRecords(List.Transform(Table.Split(Source, 3), (x)=> Record.FromList(x[Column2], x[Column1])))
in
    Result

     

    If you are looking for a complete point and click solution, below is one which use Pivot feature

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCi4pTUnNK1HwS8xNVdJRclSK1YlWcs4vLSpOVQjJLMkBC0JEXRJLUhU8i4tLU1OAgoYG+oZG+kYGRiZgSTRznLCZ4+SExRwTfUNDnMY4YzPG2RmHc8DmmGIzxwWbOS4u+MwxVoqNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 1, 1, Int64.Type),
    #"Divided Column" = Table.TransformColumns(#"Added Index", {{"Index", each _ / 3, type number}}),
    #"Rounded Up" = Table.TransformColumns(#"Divided Column",{{"Index", Number.RoundUp, Int64.Type}}),
    #"Pivoted Column" = Table.Pivot(#"Rounded Up", List.Distinct(#"Rounded Up"[Column1]), "Column1", "Column2"),
    #"Removed Columns" = Table.RemoveColumns(#"Pivoted Column",{"Index"})
in
    #"Removed Columns"

     

  • slorin's avatar
    slorin
    1 year ago

    Hi Anonymous 

     

    = #table(List.FirstN(Source[Column1],3), List.Split(Source[Column2],3))

    Stéphane 

Omid_Motamedise's avatar
Omid_Motamedise
Icon for Super User rankSuper User
1 year ago

Interesting question.
Consider the next table as Source

 

 

 

 

the result of next formula would be alist includineg every three rows of Sourec table.
= Table.Split(Source,3)

 

 

 

while we neeed to transpose evert tables, so instead of the previous formula use the next formula which result in the next image.

 

= List.Transform(Table.Split(Source,3),each Table.PromoteHeaders(Table.Transpose(_)))

 

 

 

 

based on the above explanation, use the next formula instead of the previos formula which solve the problem as below.

 

= Table.Combine(List.Transform(Table.Split(Source,3),each Table.PromoteHeaders(Table.Transpose(_))))

 

 

 

 

 

 

 

If this answer helped resolve your issue, please consider marking it as the accepted answer. And if you found my response helpful, I'd appreciate it if you could give me kudos. Thank you!