Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
What is the formula to create a calculated column using columns that have null values?
If have three columns: A, B, C. All three can have nulls. I want to create a calculated column with the formula: (A+B) - C. So something like:
If (A or B or C) is null then do not return a value else calculate (A+B) - C.
Solved! Go to Solution.
Ahhh... the confusion is this is not a Calculated Column in the Power BI Desktop (DAX) side, but a Custom Column in Power Query. Totally different syntax.
Your formula should be:
= if [#"JB8 - SHP-Unscheduled"] <> null and [#"Y8B - STAT Processing fee"] <> null and [Mvmt Cost] <> null
then [#"JB8 - SHP-Unscheduled"] + [#"Y8B - STAT Processing fee"] - [Mvmt Cost]
else null
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingAnother option:
Column =
IF (
TableName[A]
&& TableName[B]
&& TableName[C],
CALCULATE (
SUM ( TableName[A] )
+ SUM ( TableName[B] )
- SUM ( TableName[C] )
)
)
Proud to be a Super User!
Use this.
New Column =
IF(
ISBLANK('Table'[Column1]) || ISBLANK('Table'[Column2]) || ISBLANK('Table'[Column3]),
BLANK(),
'Table'[Column1] + 'Table'[Column2] - 'Table'[Column3]
)
The || operator acts as an OR and can have unlimited numbers. The OR() function only allows 2, which won't work for your 3 column scenario. For AND() with unlimited, you would use &&.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingAlso, I tried just creating a column with the IF function.
= Table.AddColumn(#"Changed Type", "Margin", each IF([#"J8B - SHP-Unscheduled"]=null, 0,[#"J8B - SHP-Unscheduled"]))
And it displays the following error: Expression.Error "The name 'IF' wasn't recognized. Make sure its spelled correctly."
@TomSinAA wrote:Also, I tried just creating a column with the IF function.
= Table.AddColumn(#"Changed Type", "Margin", each IF([#"J8B - SHP-Unscheduled"]=null, 0,[#"J8B - SHP-Unscheduled"]))
And it displays the following error: Expression.Error "The name 'IF' wasn't recognized. Make sure its spelled correctly."
In this case 'IF' should be lowercase 'if' (Power Query). Shown as:
if 2 > 1 then
2 + 2
else
1 + 1
Proud to be a Super User!
Here is the formula with the actual column names:
IF([#"J8B - SHP-Unscheduled"]) || ISBLANK([#"Y8B -STAT Processing Fee"]) || ISBLANK([Mvmt Cost]), BLANK(),
[#"J8B - SHP-Unscheduled"] + [#"Y8B -STAT Processing Fee"] - [Mvmt Cost])
It display a syntax error: Token eOF expected
With a ~ under the B in the second ISBLANK function
Ahhh... the confusion is this is not a Calculated Column in the Power BI Desktop (DAX) side, but a Custom Column in Power Query. Totally different syntax.
Your formula should be:
= if [#"JB8 - SHP-Unscheduled"] <> null and [#"Y8B - STAT Processing fee"] <> null and [Mvmt Cost] <> null
then [#"JB8 - SHP-Unscheduled"] + [#"Y8B - STAT Processing fee"] - [Mvmt Cost]
else null
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingUser | Count |
---|---|
86 | |
82 | |
68 | |
67 | |
55 |
User | Count |
---|---|
123 | |
100 | |
90 | |
83 | |
66 |