Summarize by different column when blank

[John1234]

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 (userA@gmail.com), and user B (userB@gmail.com), live at the same address, they will be grouped together. 

The problem I'm running into is for users with a blank address. If a user has a blank address, I'd instead like to just use their email as the identifier, instead of the address. Example below:

Initial Table

Address	Email
123 West Ln.	UserA@gmail.com
123 West Ln.	UserB@gmail.com
	UserC@gmail.com
	UserD@gmail.com
321 East Ln.	UserE@gmail.com

Desired Result

Address	Email
123 West Ln.	UserA@gmail.com, UserB@gmail.com
	UserC@gmail.com
	UserD@gmail.com
321 East Ln.	UserE@gmail.com

I'm not having any trouble with the concatenatex part, but getting UserC and UserD to appear on separate lines has been a challenge.

Apologies if this has posted multiple times, this is my first time posting and I can't seem to find the post after I submit it!

[v-xiaotang · Accepted solution]

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.

[John1234]

Thank you!

[DataInsights]

@John1234,

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

[John1234]

Thank you!