Power Query question

[cr731]

I have a table with columns like this,

Col1 , Col2
Yellow , John
Red , John
Blue , John
Orange , Joe
Green , Joe

I want to reconfigure this table to list each unique value of Col2 and then have every value of Col1 listed as comma separated value, like this,

Col1 Col2
John Yellow, Red, Blue
Joe Orange, Green

Can Power Query do this for me?

 

[anvg]

Hi
Yes it can!

let
    source = Table.FromRows({ 
        {"Yellow","John"}, {"Red", "John"}, {"Blue", "John"},
        {"Orange","Joe"},{"Green","Joe"}
    }),
    grouped = Table.Group(source, {"Column2"},{ {"Col2", each Text.Combine(_[Column1], ", "),type text} }),
    result = Table.RenameColumns(grouped, { {"Column2","Col1"} })
in
    result

Regards,

 

[cr731]

Hi
Yes it can!

let
    source = Table.FromRows({ 
        {"Yellow","John"}, {"Red", "John"}, {"Blue", "John"},
        {"Orange","Joe"},{"Green","Joe"}
    }),
    grouped = Table.Group(source, {"Column2"},{ {"Col2", each Text.Combine(_[Column1], ", "),type text} }),
    result = Table.RenameColumns(grouped, { {"Column2","Col1"} })
in
    result

Regards,

Thanks; that is perfect! Could you help me understand the bold part?

grouped = Table.Group(source, {"Column2"},{ {"Col2", each Text.Combine(_[Column1], ", "),type text} }),

Thanks!

 

[cr731]

Also... it might not make much sense for me to be asking this, but... how would I perform the reverse operation? So if I have a column with a delimited list, how do I transform this into one row for each of the delimited values? I am aware of the SplitColumns feature, but it forces you to specify the number of resulting columns, which I don't know. I need it to split at each delimiter into a new column, and sometimes there might be two delimiters, other times ten. How can I make that dynamic?

Thanks

 

[anvg]

Hi

Could you help me understand the bold part?

Some PQ's functions have an internal iteration on their items. A current item of an iteration step has anonymous name _. The anonymous _ is subtable of a source table for a Table.Group function (it is a first argument in that one) which is defined by a list of column names (it is a second argument in that one). _[[Column1]] is a value list of that one. Text.Combine function join the value list to a single string with appropriated delimeter.

Regards,