Forum Discussion
Create a Custom Column based on multiple columns
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!
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.
5 Replies
- wdx223_Daniel
Community Champion
=List.RemoveNulls(yourlist){0}?
Hi,
Thanks for the solutions wdx223_Daniel and Anonymous 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.
Thank you Anonymous ! 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!
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.
