Forum Discussion

problematic111's avatar
problematic111
New Member
8 years ago
Solved

Rows to Columns

Hi,

 

i've been looking for solution for some days and not yet succeeded. Task is simple, or atleast it should be:

 

This is what i have:

 

place_IDtag
1X
1Y
2X
2Y
……
1900X
1900

Y

 

This is what i want:

 

place_IDtag XTag Y
1XY
2XY
………
1900XY

 

I need this to be done at Query editor.  Thanks!

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    problematic111

     

    Give this a try

    File attached as well

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"place_ID", Int64.Type}, {"tag", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"place_ID"}, {{"AllRows", each _, type table}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom.1", each Table.Transpose(Table.SelectColumns([AllRows],"tag"))),
    #"Expanded Custom.1" = Table.ExpandTableColumn(#"Added Custom1", "Custom.1", {"Column1", "Column2"}, {"Column1", "Column2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom.1",{"AllRows"})
in
    #"Removed Columns"

2 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    problematic111

     

    Give this a try

    File attached as well

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"place_ID", Int64.Type}, {"tag", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"place_ID"}, {{"AllRows", each _, type table}}),
    #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom.1", each Table.Transpose(Table.SelectColumns([AllRows],"tag"))),
    #"Expanded Custom.1" = Table.ExpandTableColumn(#"Added Custom1", "Custom.1", {"Column1", "Column2"}, {"Column1", "Column2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded Custom.1",{"AllRows"})
in
    #"Removed Columns"