Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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!
Solved! Go to 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.
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.
=List.RemoveNulls(yourlist){0}?
= [A] ?? [B] ?? [C] ?? [D]
--Nate
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
13 | |
8 | |
8 | |
7 |
User | Count |
---|---|
17 | |
13 | |
7 | |
6 | |
6 |