Forum Discussion

scottmcarroll's avatar
scottmcarroll
Copper Contributor
Aug 19, 2024

Consolidating Information with Duplicates in Some Cells

In the process of flattening a JSON file I wound up with a table that had duplicates in some cells but not others. See Below:

PowerQuery created a duplicate for ID, Name, City, and Age for every friends.name. How do I consolidate the friends.names into a single row and delete the duplicates? Thank you!

 

 

 

excel

4 Replies

  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Aug 19, 2024

    scottmcarroll 

    You may Group By on id, city, and age selecting any aggregation from user interface (e.g. SUM) and after that change in formula bar List.Sum([friends.name]) on Text.Combine( [friends.name], ", ").

    • scottmcarroll's avatar
      scottmcarroll
      Copper Contributor
      Aug 19, 2024

      Thank you very much! That is very helpful.

      I realized after posting this that each friend also had unique hobbies.
      My thought was to first combine both the friends and their hobbies in a new column, maybe using parentheses - then to use the Group By command with the combination.
      Ex) Sergei (Snowboarding, Giving great answers)

      Any thoughts about how to manage the hobbies as well?

      Thank you Sergei!

      • SergeiBaklan's avatar
        SergeiBaklan
        Diamond Contributor
        Aug 19, 2024

        scottmcarroll 

        Perhaps it's better to use another characters to make so long texts more readable, like

        with

        let
    Source = Excel.CurrentWorkbook(){[Name="Source"]}[Content],
    face = Character.FromNumber(128512),
    MergeNameHobbies = Table.CombineColumns(
        Source,
        {"friends.name", "friends.hobbies"},
        Combiner.CombineTextByDelimiter(" (", QuoteStyle.None),
        "friend.names (hobbies)"
    ),
    AddSuffix = Table.TransformColumns(
        MergeNameHobbies,
        {
            {"friend.names (hobbies)", each _ & ")", type text}
        }),
    AddPrefix = Table.TransformColumns(
        AddSuffix,
        {
            {"friend.names (hobbies)", each face & _, type text}
        }),
    #"Grouped Rows" = Table.Group(AddPrefix, {"id", "name", "city", "age"},
        {
            {"friend.names (hobbies)",
             each  Text.Combine([#"friend.names (hobbies)"],  " "), type text}})
in
    #"Grouped Rows"

Resources