Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Transpose one column except first

I have a table as below.    I would like to change this to: Column1:    Column2    Column3    Column4   Column5 213              86               2 446              83              4      ...
Need Help
  • HotChilli's avatar
    HotChilli
    6 years ago

    Here's my attempt.

    I think you need to add a column (to index each value within the group) that can be used as the pivot column to make the  headings in the final table

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY7JDcAwCAR74e1HOGySWiz330bkS5rfjGBhexdTlyJvk1Gu2OKINgcOCTCXdtirzZ0HctJbKtjA/kGSA7AyoTylATlNEv8Sp/KWGj8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"all", each _, type table [Column1=number, Column2=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([all], "ind", 1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Column1", "Column2", "ind"}, {"Custom.Column1", "Custom.Column2", "Custom.ind"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Custom", {{"Custom.ind", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Expanded Custom", {{"Custom.ind", type text}}, "en-GB")[Custom.ind]), "Custom.ind", "Custom.Column2", List.Sum)
in
    #"Pivoted Column"

     

    Tip: for quicker answers, please post the data (not a picture)

kentyler's avatar
kentyler
Solution Sage
6 years ago

It looks like you could go into power query and pivot the table on column one, which would give you a column each for the different values.

It looks like you don't want to pivot on column 2, you should be able to leave it alone when you do the pivot on column 1

  • HotChilli's avatar
    HotChilli
    Community Champion
    6 years ago

    Here's my attempt.

    I think you need to add a column (to index each value within the group) that can be used as the pivot column to make the  headings in the final table

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("TY7JDcAwCAR74e1HOGySWiz330bkS5rfjGBhexdTlyJvk1Gu2OKINgcOCTCXdtirzZ0HctJbKtjA/kGSA7AyoTylATlNEv8Sp/KWGj8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", Int64.Type}, {"Column2", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Column1"}, {{"all", each _, type table [Column1=number, Column2=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([all], "ind", 1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Custom"}),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Other Columns", "Custom", {"Column1", "Column2", "ind"}, {"Custom.Column1", "Custom.Column2", "Custom.ind"}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Expanded Custom", {{"Custom.ind", type text}}, "en-GB"), List.Distinct(Table.TransformColumnTypes(#"Expanded Custom", {{"Custom.ind", type text}}, "en-GB")[Custom.ind]), "Custom.ind", "Custom.Column2", List.Sum)
in
    #"Pivoted Column"

     

    Tip: for quicker answers, please post the data (not a picture)

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Thank you so much. This worked perfect