cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
problematic111 Frequent Visitor
Frequent Visitor

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Rows to Columns

@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 2
Super User
Super User

Re: Rows to Columns

@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"
problematic111 Frequent Visitor
Frequent Visitor

Re: Rows to Columns

Works like a charm! 5/5