• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

M Language

Bomino

Member
Hello,
I have a table like the following
67169
Using Power Query, I would like the desired output to be
67170

Any help will be greatly appreciated.
thank you.
 
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

    Code:
    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
 
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

    Code:
    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.
 
Back
Top