Forum Discussion
Romy_Yourex
Nov 23, 2021Copper Contributor
Merging multiple persons residing at the same address into one household address
Hello, Does anyone happen to know of a way to merge multiple household members, residing at the same address, into one address for the household? I.e. Jane Doe, 4 Cherry Lane, T0M X02, Calgary, ...
PeterBartholomew1
Nov 23, 2021Silver Contributor
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.
Riny_van_Eekelen
Nov 24, 2021Platinum Contributor
PeterBartholomew1 Indeed, PQ can do this very easily. Couldn't resist to demonstrate it. See attached.
- PeterBartholomew1Nov 24, 2021Silver Contributor
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?
- Riny_van_EekelenNov 24, 2021Platinum Contributor
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.
- SergeiBaklanNov 25, 2021MVP
As a comment, working with texts we are more safe if at least trim them and use case-insensitive texts for grouping. One variant is to make helper columns, another one add comparer function to grouping. In our case it could be
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Grouped Rows" = Table.Group( Source, {"Address", "Zip", "City", "State"}, { {"Name(s)", each Text.Combine([Name], ", "), type text}}, GroupKind.Local, (current, other) => Number.From( List.ContainsAll( List.Transform( Record.FieldValues( current ), each Text.Trim( Text.Lower(_) )), List.Transform( Record.FieldValues( other ), each Text.Trim( Text.Lower(_) )) ) ) -1 ), #"Reordered Columns" = Table.ReorderColumns( #"Grouped Rows", {"Name(s)", "Address", "Zip", "City", "State"}) in #"Reordered Columns"
Plus misprint with line break is corrected: