Forum Discussion
Merging multiple persons residing at the same address into one household address
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.
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:
Thank you for that. You confirmed what I thought might be the case. I followed the solutions as you described them well enough. Whether I could reproduce them from a blank sheet when needed is another matter entirely!
