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 ap...
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

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