Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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!!
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.
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
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}),
I should clarify - it's the "Name" field, I'm trying to get out of this:
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!!
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |