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.
Hi there! I hope someone can help me out on this one.
Suppose that you are given a table of the following form:
Key | Character |
sa | F |
sa | J |
bv | L |
sa | M |
nc | D |
nc | Z |
How can I transform it into the follwing table which groups the Character entries by the Key entries and delimited by semicolon:
Key | Character |
sa | F; J; M |
bv | L |
sa | M |
nc | D; Z |
Thanks in advance.
Solved! Go to Solution.
I put your data in Excel in a table called Table1. (orginal, I know). Just change your source to be your table in Power BI.
The following M code will do what you want:
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Key", type text}, {"Character", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Key"}, {{"All Rows", each _, type table}}), #"Added Characters Only" = Table.AddColumn(#"Grouped Rows", "Characters Only", each Table.Column([All Rows],"Character")), #"Extracted Values" = Table.TransformColumns(#"Added Characters Only", {"Characters Only", each Text.Combine(List.Transform(_, Text.From), ";"), type text}), #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"All Rows"}) in #"Removed Columns"
All of that is through the user interface except "Added Characters Only" - that function I had to type in manually. Let me know if you have any questions on how it works.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingI put your data in Excel in a table called Table1. (orginal, I know). Just change your source to be your table in Power BI.
The following M code will do what you want:
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{{"Key", type text}, {"Character", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Key"}, {{"All Rows", each _, type table}}), #"Added Characters Only" = Table.AddColumn(#"Grouped Rows", "Characters Only", each Table.Column([All Rows],"Character")), #"Extracted Values" = Table.TransformColumns(#"Added Characters Only", {"Characters Only", each Text.Combine(List.Transform(_, Text.From), ";"), type text}), #"Removed Columns" = Table.RemoveColumns(#"Extracted Values",{"All Rows"}) in #"Removed Columns"
All of that is through the user interface except "Added Characters Only" - that function I had to type in manually. Let me know if you have any questions on how it works.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingBrilliant. Thanks a lot!
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 |
---|---|
107 | |
93 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |