Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |