Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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!
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
91 | |
87 | |
84 | |
68 | |
49 |
User | Count |
---|---|
131 | |
111 | |
96 | |
71 | |
67 |