Conditionally fill blank cells based on another column in Power Query

[Hyuna_8000]

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:

Username	User Email
Amy Shane	amy.shane@123.com
Brat Pitt	brad.pitt@123.com
Katy Perry	katy.perry@123.com
Tom Brady	tom.brady@123.com
Vince Lawrance	v.lawrance@123.com
Tom Brady
Amy Shane
Jay Chou

Expected Output:

Username	User Email
Amy Shane	amy.shane@123.com
Brat Pitt	brad.pitt@123.com
Katy Perry	katy.perry@123.com
Tom Brady	tom.brady@123.com
Vince Lawrance	v.lawrance@123.com
Tom Brady	tom.brady@123.com
Amy Shane	amy.shane@123.com
Jay Chou

[OwenAuger · Accepted solution]

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", "amy.shane@123.com"},
      {"Brat Pitt", "brad.pitt@123.com"},
      {"Katy Perry", "katy.perry@123.com"},
      {"Tom Brady", "tom.brady@123.com"},
      {"Vince Lawrance", "v.lawrance@123.com"},
      {"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