Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jmeccles
Helper I
Helper I

Group table of strings by key and extract one row per group

Hi there! I hope someone can help me out on this one.

Suppose that you are given a table of the following form:

KeyCharacter
saF
saJ
bvL
saM
ncD
ncZ


How can I transform it into the follwing table which groups the Character entries by the Key entries and delimited by semicolon:

KeyCharacter
saF; J; M
bvL
saM
ncD; Z

 

Thanks in advance.

1 ACCEPTED SOLUTION
edhans
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

2 REPLIES 2
edhans
Super User
Super User

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Brilliant. Thanks a lot!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors