Want to combine rows with same id into one row with merged column in power Query

[SanjeevA]

I have got this table as below

Reference	Role	Contact .value	Division	Branch
DO250328_0811	Contacts	Peter, Aaron	Delivery	Sustainability
DO250328_0811	Contacts	Smith, Terry	Delivery	Sustainability
DO250402_1049	Director	Singh, Adele	Delivery	Sustainability
DO250402_1151	Secretary	Piper, Marina	Delivery	Sustainability
DO250414_1068	Functional lead	Boson, Jeroen	Delivery	Sustainability
DO250523_1155	Operations Support	luke, Izzy	Delivery	Sustainability

and I want to achieve the results as per below, having a unique record based on the reference. I want to be able to achieve this in power Query. The records originally got duplicated because it was from the share point table and by expanding the column it created 2 records.

Reference	Role	Contact .value	Division	Branch
DO250328_0811	Contacts	Peter, Aaron ; Smith, Terry	Delivery	Sustainability
DO250402_1049	Director	Singh, Adele	Delivery	Sustainability
DO250402_1151	Secretary	Piper, Marina	Delivery	Sustainability
DO250414_1068	Functional lead	Boson, Jeroen	Delivery	Sustainability
DO250523_1155	Operations Support	luke, Izzy	Delivery	Sustainability

Any help would be Appreciated.

[v-achippa]

Hi @SanjeevA,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @ronrsnfld, @SundarRaj, @Royel and @m_dekorte for the prompt response. 

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[m_dekorte]

Here's another approach.
This assumes the column order is fixed and the first two are your keyColumns

let
    colNames = Table.ColumnNames(Source),
    keyCols = 2,
    Source = Table.FromRows({
        {"DO250328_0811", "Contacts", "Peter, Aaron", "Delivery", "Sustainability"},
        {"DO250328_0811", "Contacts", "Smith, Terry", "Delivery", "Sustainability"},
        {"DO250402_1049", "Director", "Singh, Adele", "Delivery", "Sustainability"},
        {"DO250402_1151", "Secretary", "Piper, Marina", "Delivery", "Sustainability"},
        {"DO250414_1068", "Functional lead", "Boson, Jeroen", "Delivery", "Sustainability"},
        {"DO250523_1155", "Operations Support", "luke, Izzy", "Delivery", "Sustainability"}
    }, type table[Reference = text, Role = text, #"Contact .value" = text, Division = text, Branch = text]),
    GroupBy = Table.Group(Source, List.FirstN(colNames, keyCols), {{"t", each Table.FromColumns(List.Transform(Table.ToColumns(Table.SelectColumns(_, List.Skip(colNames, keyCols))), (x)=>  {Text.Combine(List.Distinct(x), "; ")}), List.Skip(colNames, keyCols)) }}),
    Expand = Table.ExpandTableColumn(GroupBy, "t", List.Skip(colNames, keyCols))
in
    Expand

[Royel]

Hi @SanjeevA 

you can use this m-code to get the expected results 

let
    Source = Table.FromRows({
        {"DO250328_0811", "Contacts", "Peter, Aaron", "Delivery", "Sustainability"},
        {"DO250328_0811", "Contacts", "Smith, Terry", "Delivery", "Sustainability"},
        {"DO250402_1049", "Director", "Singh, Adele", "Delivery", "Sustainability"},
        {"DO250402_1151", "Secretary", "Piper, Marina", "Delivery", "Sustainability"},
        {"DO250414_1068", "Functional lead", "Boson, Jeroen", "Delivery", "Sustainability"},
        {"DO250523_1155", "Operations Support", "luke, Izzy", "Delivery", "Sustainability"}
    }, type table[Reference = text, Role = text, #"Contact .value" = text, Division = text, Branch = text]),
    
    // Group by Reference and Role, combining Contact values
    GroupedRows = Table.Group(
        Source, 
        {"Reference", "Role"}, 
        {
            {"Contact .value", (t) => Text.Combine(List.Distinct(t[Contact .value]), " ; "), type text},
            {"Division", (t) => Text.Combine(List.Distinct(t[Division]), " ; "), type text},
            {"Branch", (t) => Text.Combine(List.Distinct(t[Branch]), " ; "), type text}
        }
    )
in
    GroupedRows

Results: 

Find this helpful? ✔ Give a Kudo • Mark as Solution – help others too!

[SundarRaj]

Hi @SanjeevA,

Here's another solution to your query. Do have a look. I'll attach the file link for you to see the code and the output.

let
Source = #table(
{"Reference", "Role", "Contact .value", "Division", "Branch"},
{
{"DO250328_0811", "Contacts", "Peter, Aaron", "Delivery", "Sustainability"},
{"DO250328_0811", "Contacts", "Smith, Terry", "Delivery", "Sustainability"},
{"DO250402_1049", "Director", "Singh, Adele", "Delivery", "Sustainability"},
{"DO250402_1151", "Secretary", "Piper, Marina", "Delivery", "Sustainability"},
{"DO250414_1068", "Functional lead", "Boson, Jeroen", "Delivery", "Sustainability"},
{"DO250523_1155", "Operations Support", "luke, Izzy", "Delivery", "Sustainability"}
}
),
ColNames = Table.ColumnNames(Source),
Reference = Table.Group(
Source,
{"Reference"},
{
"Tables",
each Table.FromColumns(
List.Transform(
List.Transform(Table.ToColumns(_), each Text.Combine(List.Distinct(_), " ; ")),
each {_}
),
ColNames
)
}
)[[Tables]],
Expand = Table.ExpandTableColumn(Reference, "Tables", ColNames)
in
Expand

Thanks,

[ronrsnfld · Accepted solution]

You can do this using the Table.Group function. Not sure exactly what might be duplicated, but assuming Reference and Role are not, you can group by those and return a demarcated list from the results

Original Data

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("lZDBisJADIZfZeh5Dp3aLvWoFmEXFoV6E5FsDRp2dqakqaBPb8YH0O4tCV8+/mS/z5pNUeWzoj7mtXOZzVYxCHQyaLlFQbZmARyDtg16uiLftGzHQYAC/JAnuWUH+9LT/pFcrNkhP5cneMq8OLq8nCeaGDuJnGgKZ/UsTujxHx5XpTwtdowCT3xLfTrsG1h3JppcqYk+akXWY+iEYgBvPMJJJ8s4xGDNF3LEiZ+qillKVimy0TCQhINpx76PLDr04y9a83m/v/nY4QE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Reference = _t, Role = _t, #"Contact .value" = _t, Division = _t, Branch = _t]),
    
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Reference", type text}, {"Role", type text}, {"Contact .value", type text}, {"Division", type text}, {"Branch", type text}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Reference", "Role"}, {
        {"Contact .value", (t)=>Text.Combine(List.Distinct(t[Contact .value]),"; "), type text},
        {"Division", (t)=>Text.Combine(List.Distinct(t[Division]),"; "), type text},
        {"Branch", (t)=>Text.Combine(List.Distinct(t[Branch]),"; "), type text}
        })
in
    #"Grouped Rows"

 Results