Forum Discussion
Solved
Summarize by different column when blank
Hi, I'm trying to create household level data for our users by using 'summarize' to group users with the same address. For example, if user A ([email protected]), and user B ([email protected]), live...
Hi John1234
you can take steps below for reference.
1. Open the Advanced editor:
2. Empty the contents in the Advanced editor, and copy the following code into it
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSkxJKUotLjZU0lFKzU3MzDFUitXBFDUCi8J4xig8E2QdpjBRU6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Address = _t, Email = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Address", type text}, {"Email", type text}}), #"Filtered Rows" = Table.SelectRows(Source, each ([Address] <> "")), #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Address"}, {{"Email", each Text.Combine([Email],", "), type text}}), table1= Table.SelectRows(Source, each ([Address] = "")), #"appendtable"=Table.Combine({table1, #"Grouped Rows"}) in #"appendtable"Result:
Hope this helps.
Best Regards,
Community Support Team _ Tang
If this post helps, please consider Accept it as the solution to help the other members find it more quickly.
Try this in Power Query. The original table is named Table.
1. Create query AddressNotNull:
let
Source = Table,
FilterRows = Table.SelectRows(Source, each ([Address] <> null)),
ConcatEmail = Table.Group( FilterRows, {"Address"}, {{"Email", each Text.Combine([Email], ", "), type text}})
in
ConcatEmail
2. Create query AddressNull:
let
Source = Table,
FilterRows = Table.SelectRows(Source, each ([Address] = null))
in
FilterRows
3. Create query Append:
let
Source = Table.Combine({AddressNotNull, AddressNull})
in
Source
Thank you!