cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jmeccles Frequent Visitor
Frequent Visitor

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

Accepted Solutions
edhans New Contributor
New Contributor

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

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.

2 REPLIES 2
edhans New Contributor
New Contributor

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

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.

jmeccles Frequent Visitor
Frequent Visitor

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

Brilliant. Thanks a lot!