Forum Discussion
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 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 |
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!!
7 Replies
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}),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
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.
