Create a Custom Column based on multiple columns

[jiexika24]

Hello, I'm trying to create a custom column (column E) that evaluates columns A through D to find cells with a whole number that is greater than or equal to 0 and puts that number into column E. If the cells in columns A through D only contain 'null' values, then column E will return 'null'. Note: Columns A through D will only contain 1 number or say 'null' (no aggregation is needed).

Here's an example of my end goal:

I tried the following formulas that both returned 'Error' in all the cells within column E.

= let
columns = { [A], [B], [C], [D] },
concatenated = Text.Combine(List.Transform(columns, each if _ = null then "" else Text.From(_)), ""),
validNumbers = List.Select(columns, each _ <> null and Number.RoundDown(Number.From(_)) = Number.From(_) and Number.From(_) >= 0),
firstValidNumber = if List.Count(validNumbers) > 0 then List.First(validNumbers) else null
in
if concatenated = "" then null else firstValidNumber

= let
columns = { [A], [B], [C], [D] },
nonNullNumbers = List.Select(columns, each _ <> null and Number.RoundDown(_) = _ and _ >= 0)
in
if List.Count(nonNullNumbers) > 0 then List.First(nonNullNumbers) else null

Thanks for any support you can provide!

[v-xinruzhu-msft]

Hi,

Thanks for the solutions @wdx223_Daniel  and @watkinnc  provided and i want to offer some more information for user to refer to.

hello @jiexika24 , based on your description, you can merge the columns directly, you can refer to the following code to advanced editor in power query.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtJRsgRiEIrVAXMhyNAAlY/EM4JxTbHJEsMzxCFpohQbCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [A = _t, B = _t, C = _t, D = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"A", Int64.Type}, {"B", Int64.Type}, {"C", Int64.Type}, {"D", Int64.Type}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Changed Type", "Merged", each Text.Combine({Text.From([A], "en-US"), Text.From([B], "en-US"), Text.From([C], "en-US"), Text.From([D], "en-US")}, ""), type text),
    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Merged Column",{{"Merged", Int64.Type}})
in
    #"Changed Type1"

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jiexika24]

Thank you @v-xinruzhu-msft ! I really appreciate your help and see how this would work, but I should have mentioned that the example table I provided above isn't my actual data (since mine has PII).

Would it be possible to rework the expression so that it's not linked to a Json file? I can plug in my variable names, since they are lengthy (e.g., [#"Trainer: First Last Name - Techniques"]).

Thanks for any additional support you can provide!

[v-xinruzhu-msft · Accepted solution]

Hi @jiexika24  

Thanks fot your quick reply, you can refer to the following steps.

In power query, select all columns, then select 'Merge column' 

Then you can input the new column name

After merging, change the column type to whole number.

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[wdx223_Daniel]

=List.RemoveNulls(yourlist){0}?

[watkinnc]

= [A] ?? [B] ?? [C] ?? [D] 

--Nate