Expand Multiple Values as One Row

[enswitzer]

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 both displaying the data and creating relationships. How can I show these in one row?

*Because project number is my unique identifier, I only want this project number listed once so I do not end up creating many to many relationships.

Current State:

Project Number	Project Title	Sponsor	Location
1	Project 1	Executive A	Indiana
2	Project 2	Executive B	New York
3	Project 3	Executive A	Virginia
3	Project 3	Executive A	Indiana
4	Project 4	Executive B	New York

Desired Output:

Project Number	Project Title	Sponsor	Location
1	Project 1	Executive A	Indiana
2	Project 2	Executive B	New York
3	Project 3	Executive A	Virginia, Indiana
4	Project 4	Executive B	New York

[Ashish_Mathur]

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.

[amitjzaveri]

Instead of "Expand to New Rows" you can Extract Values in comma-delimited format.

Alternatively, you can write m query to extract list as comma-delimited value in new column:

    Extract_List = Table.TransformColumns(PrevStep, {"Listxyz", each Text.Combine(List.Transform(_, Text.From), ","), type text}),

[newpowerbiuser8]

Hi, I have got a similar situation. My share point is giving person field with multiple people. In powerbi, it is coming as a table. Once I extract values from the table, I have multiple rows 

ID 1     Name 1    Red    Sunday

ID 1     Name 2    Red    Sunday.

I want in my matrix to come as only 1 row

TIA

[enswitzer]

Does the m query work if it is a "table" I am expanding rather than a list?

I am getting an error with this:

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

[enswitzer]

I should clarify - it's the "Name" field, I'm trying to get out of this:

[amitjzaveri · Accepted solution]

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!!

[newpowerbiuser8]

Hi, I have got a similar situation. My share point is giving person field with multiple people. In powerbi, it is coming as a table. Once I extract values from the table, I have multiple rows 

ID 1     Name 1    Red    Sunday

ID 1     Name 2    Red    Sunday.

I want in my matrix to come as only 1 row

TIA