Forum Discussion
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 eve...
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!
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"