M Language

[Bomino]

Hello,
I have a table like the following

Using Power Query, I would like the desired output to be


Any help will be greatly appreciated.
thank you.

 

[GraH - Guido]

Hi,
follow these steps:

1. load table in PQ
2. group by Project, use ALL ROWS as Operation and call this column "All"
3. Apply a custom column with formula: = Table.Column([All], "Customers") => this creates a list.
4. Expand this "List" column using extract values and a comma as separator.
5. Save and Load
   
   

   let
       Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
       GroupByProject = Table.Group(Source, {"Projects"}, {{"All", each _, type table [Projects=text, Customers=text]}}),
       GetList = Table.AddColumn(GroupByProject, "List", each Table.Column([All],"Customers")),
       ExtractList = Table.TransformColumns(GetList, {"List", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
       DeleteCol_All = Table.RemoveColumns(ExtractList,{"All"})
   in
       DeleteCol_All

 

[Bomino]

Found a solution:

Hi,
follow these steps:

1. load table in PQ
2. group by Project, use ALL ROWS as Operation and call this column "All"
3. Apply a custom column with formula: = Table.Column([All], "Customers") => this creates a list.
4. Expand this "List" column using extract values and a comma as separator.
5. Save and Load
   
   

   let
       Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
       GroupByProject = Table.Group(Source, {"Projects"}, {{"All", each _, type table [Projects=text, Customers=text]}}),
       GetList = Table.AddColumn(GroupByProject, "List", each Table.Column([All],"Customers")),
       ExtractList = Table.TransformColumns(GetList, {"List", each Text.Combine(List.Transform(_, Text.From), ","), type text}),
       DeleteCol_All = Table.RemoveColumns(ExtractList,{"All"})
   in
       DeleteCol_All

Thank you so much.