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

[jmeccles]

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.

[edhans · Accepted 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.

[jmeccles]

Brilliant. Thanks a lot!