Forum Discussion

Hyuna_8000's avatar
Hyuna_8000
Helper I
2 years ago
Solved

Conditionally fill blank cells based on another column in Power Query

Hi,

 

I have a table that has two columns (Username and User Email). I was wondering how can I fill in the blank cells in "User Email" based on the following conditions:
1. If the same username appears in duplicated/multiple rows, one of them has email and the other has email as blank, fill the blank cells in "User Email" column with the existing emails for the same username
2. If the user name only appear in one row and its "User Email" is blank, then "User Email" as blank

I've tried to use group by rows, Fill down but I haven't been able to figure out correctly in Power Query. Any tips would be appreciated. Thank you.

Original table:

UsernameUser Email
Amy Shane[email protected]
Brat Pitt[email protected]
Katy Perry[email protected]
Tom Brady[email protected]
Vince Lawrance[email protected]
Tom Brady 
Amy Shane 
Jay Chou 

 

Expected Output:

UsernameUser Email
Amy Shane[email protected]
Brat Pitt[email protected]
Katy Perry[email protected]
Tom Brady[email protected]
Vince Lawrance[email protected]
Tom Brady[email protected]
Amy Shane[email protected]
Jay Chou 



Need Help
  • OwenAuger's avatar
    OwenAuger
    2 years ago

    Hi Hyuna_8000 

    Here's an example using a self-join.

    Create blank query and paste into advanced editor:

     

    let
  Source = #table(
    type table [Username = text, User Email = text],
    {
      {"Amy Shane", "[email protected]"},
      {"Brat Pitt", "[email protected]"},
      {"Katy Perry", "[email protected]"},
      {"Tom Brady", "[email protected]"},
      {"Vince Lawrance", "[email protected]"},
      {"Tom Brady", null},
      {"Amy Shane", null},
      {"Jay Chou", null}
    }
  ),
  #"Self-join" = Table.NestedJoin(
    Source,
    {"Username"},
    Source,
    {"Username"},
    "SelfJoin",
    JoinKind.LeftOuter
  ),
  #"Aggregated SelfJoin" = Table.AggregateTableColumn(
    #"Self-join",
    "SelfJoin",
    {{"User Email", List.Min, "User Email 2", type text}}
  ),
  #"Use non-null email" = Table.CombineColumns(
    #"Aggregated SelfJoin",
    {"User Email", "User Email 2"},
    // Custom Combiner function that returns first non-null value of the two columns
    (Columns) as nullable text => Columns{0} ?? Columns{1},
    "User Email"
  )
in
    #"Use non-null email"

     

    Regards

1 Reply

  • OwenAuger's avatar
    OwenAuger
    Super User
    2 years ago

    Hi Hyuna_8000 

    Here's an example using a self-join.

    Create blank query and paste into advanced editor:

     

    let
  Source = #table(
    type table [Username = text, User Email = text],
    {
      {"Amy Shane", "[email protected]"},
      {"Brat Pitt", "[email protected]"},
      {"Katy Perry", "[email protected]"},
      {"Tom Brady", "[email protected]"},
      {"Vince Lawrance", "[email protected]"},
      {"Tom Brady", null},
      {"Amy Shane", null},
      {"Jay Chou", null}
    }
  ),
  #"Self-join" = Table.NestedJoin(
    Source,
    {"Username"},
    Source,
    {"Username"},
    "SelfJoin",
    JoinKind.LeftOuter
  ),
  #"Aggregated SelfJoin" = Table.AggregateTableColumn(
    #"Self-join",
    "SelfJoin",
    {{"User Email", List.Min, "User Email 2", type text}}
  ),
  #"Use non-null email" = Table.CombineColumns(
    #"Aggregated SelfJoin",
    {"User Email", "User Email 2"},
    // Custom Combiner function that returns first non-null value of the two columns
    (Columns) as nullable text => Columns{0} ?? Columns{1},
    "User Email"
  )
in
    #"Use non-null email"

     

    Regards