Create emails & exclude null values - Power Query

[cherrypbi]

Is there a way to create the custom column in yellow, excluding the null values? I'm not sure if this is even ahievable in Power Query.. any advice would be appreciated. 

[ImkeF]

Sure:
The _ gets all fields from the current row in a record format.

But we don't need the ID-column. So we the Record.RemoveFields function removes just that field.
Then the Record.FieldValues-function transforms that record into a list, containing only the field values.

But there are nulls in there that shouldn't be used to create the final string.
Therefore we use the List.Select function with (l) => l <> null as function argument: l stands for each item in the list and I <> null means that only items that are not null will be kept.

[AlexisOlson]

@ImkeF's answer looks good except that the ID = 444 row returns "@gmail.com".

Here's a slightly different approach that applies the "%2C" and "@gmail.com" separately:

Text.Combine(List.Transform({[name1], [name2], [name3], [name4]}, each _ & "@gmail.com"), "%2C")

 Note that null & "@gmail.com" returns null.

[ImkeF · Accepted solution]

Hi @cherrypbi ,
you can add a custom column with the following formula:

Text.Combine(List.Select( Record.FieldValues( Record.RemoveFields(_, {"ID"}) ), (l) => l <> null), "@gmail.com%2C") & "@gmail.com"

[cherrypbi]

this worked like a charm! could you explani what the query is doing? I'm trying to understand this part "

 Record.RemoveFields(_, {"ID"}) ), (l) => l <> null