Forum Discussion

John1234's avatar
John1234
Frequent Visitor
5 years ago
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 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

AddressEmail
123 West Ln.[email protected]
123 West Ln.[email protected]
 [email protected]
 [email protected]
321 East Ln.[email protected]

 

Desired Result

 

AddressEmail
123 West Ln.[email protected], [email protected]
 [email protected]
 [email protected]
321 East Ln.[email protected]

 

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!

Need Help
  • v-xiaotang's avatar
    v-xiaotang
    5 years ago

    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.

4 Replies

  • DataInsights's avatar
    DataInsights
    Super User
    5 years ago

    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

     

     

  • v-xiaotang's avatar
    v-xiaotang
    Community Support
    5 years ago

    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.