[PQ] - Row Values into single cell - How to?

[Bullstrik1]

Hi everyone!

I have the folowing query in PQ:

Nº <colgroup><col></colgroup><tbody> </tbody>	ID
2000002 <colgroup><col></colgroup><tbody> </tbody>	111
2000002 <colgroup><col></colgroup><tbody> </tbody>	112
2000002 <colgroup><col></colgroup><tbody> </tbody>	113
2000003 <colgroup><col width="64"></colgroup><tbody> </tbody>	114
2000003 <colgroup><col width="64"></colgroup><tbody> </tbody>	115
2000003 <colgroup><col width="64"></colgroup><tbody> </tbody>	122
2000003 <colgroup><col width="64"></colgroup><tbody> </tbody>	117
2000003 <colgroup><col width="64"></colgroup><tbody> </tbody>	125
2000004 <colgroup><col></colgroup><tbody> </tbody>	156
2000005 <colgroup><col width="64"></colgroup><tbody> </tbody>	148
2000005 <colgroup><col width="64"></colgroup><tbody> </tbody>	146
2000005 <colgroup><col width="64"></colgroup><tbody> </tbody>	177

<tbody>
</tbody>

I would like to retrive the folowing table out of PQ:

Nº	ID
2000002	111|112|113
2000003	114|115|122|117|125
2000004	156
2000005	148|146|177

<tbody>
</tbody>

Is it possible?
I was messing arround with Lis.Transform, but i didn't make it.
Does anyone have an idea how this could be done?

Tank you!

 

[MarcelBeug]

Yes, I have.

let
    Source = Excel.CurrentWorkbook(){[Name="Tabel1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Nº ", Int64.Type}, {"ID", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Nº "}, {{"IDs", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "ID", each Text.TrimStart(List.Accumulate([IDs][ID],"",(x,y) => x & "|" & Text.From(y)),"|")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"IDs"})
in
    #"Removed Columns"

Or even simpler if you use type text for the original ID column.

let
    Source = Excel.CurrentWorkbook(){[Name="Tabel1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Nº ", Int64.Type}, {"ID", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Nº "}, {{"IDs", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "ID", each Text.Combine([IDs][ID],"|")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"IDs"})
in
    #"Removed Columns"

 

[Bullstrik1]

Yes, I have.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Nº ", Int64.Type}, {"ID", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Nº "}, {{"IDs", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "ID", each Text.TrimStart(List.Accumulate([IDs][ID],"",(x,y) => x & "|" & Text.From(y)),"|")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"IDs"})
in
    #"Removed Columns"

Or even simpler if you use type text for the original ID column.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Nº ", Int64.Type}, {"ID", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Nº "}, {{"IDs", each _, type table}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "ID", each Text.Combine([IDs][ID],"|")),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"IDs"})
in
    #"Removed Columns"

This works a like a charm!
Tank you so much for your help!
How come i didnt remember the "All rows" option in Group By? God dam it! :P

cheers!

P.S.: Some forum admin, pls mark this as solved pls! Thank you!

 

[anvg]

Hi

Or even simpler if you use type text for the original ID column.

Yes!

let
    source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    typed = Table.TransformColumnTypes(source,{{"Nº", Int64.Type}, {"ID", type text}}),
    grouped = Table.Group(typed, {"Nº"}, { {"ID", each Text.Combine([ID], "|"), type text} })
in
    grouped

Regards,