Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Expand Multiple Values as One Row

I have a set of data that has multiple values in one field (see below). When I expand that data it creates multiple rows and duplicates the rest of the data. However, this is causing me issues for bo...
Need Help
  • amitjzaveri's avatar
    amitjzaveri
    5 years ago

    Yes you can extract a column from the table here is the syntax:

     

    #"Extract_List" = Table.TransformColumns(#"Removed Columns, {"ProjectLead", each Text.Combine(List.Transform(Table.TransformRows(_,each [Name]), Text.From), ","), type text})

     

    Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

     

Ashish_Mathur's avatar
Ashish_Mathur
Super User
5 years ago

Hi,

This M code works

let
    Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Project Number"}, {{"All Locatinos", each Text.Combine(List.Distinct([Location]), ", "), type text}}),
    Joined = Table.Join(Source, "Project Number", #"Grouped Rows", "Project Number"),
    #"Removed Columns" = Table.RemoveColumns(Joined,{"Location"}),
    #"Removed Duplicates" = Table.Distinct(#"Removed Columns")
in
    #"Removed Duplicates"

Hope this helps.