Forum Discussion
Merging multiple persons residing at the same address into one household address
Sadly, you might have to buy a new version of 365 for this to work
= LET(
addr, UNIQUE(Address),
occupants, MAP(addr,
LAMBDA(a, TEXTJOIN(", ",,FILTER(Name,Address=a)))),
CHOOSE({1,2}, addr, occupants))My test data wasn't exactly spectacular
I assume Power Query would also do the job, but it wasn't immediately obvious how.
PeterBartholomew1 Indeed, PQ can do this very easily. Couldn't resist to demonstrate it. See attached.
Thanks for that. When I tried 'Group', PQ returned a column of Tables, each containing entire records.
Did you get the combined text from the GUI or is it a typed command?
PeterBartholomew1 The Text.Combine step is manipulated by me. Every now and then you need to do that as not everything can get done by the GUI only. When you do the grouping you can first let PQ do a Sum of the names. That creates a piece of M-code like:
each List.Sum([Name])
But that results in Errors as you can't sum texts. But most of the code generated is there. Now, you edit the code to:
each Text.Combine([Name], ",")
A neater way would have been to group without any particular operation. That will produce a column of tables that you then can use to Text.Combine the [Name] columns from.
The full M-code for these two steps look like this:
#"Grouped Rows" = Table.Group(Source, {"Address", "Zip", "City", "State"}, {{"Grouped", each _, type table [Name=text, Address=text, Zip=text, City=text, State=text]}}), #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Text.Combine ( [Grouped] [Name], "," ))But, since I'm lazy, I prefer the shorter option.
