cancel
Showing results for
Did you mean:
Helper I

## Calculated column

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.

1 ACCEPTED SOLUTION
Super User III

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.

``````= 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``````

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
6 REPLIES 6
Super User I

Another option:

``````Column =
IF (
TableName[A]
&& TableName[B]
&& TableName[C],
CALCULATE (
SUM ( TableName[A] )
+ SUM ( TableName[B] )
- SUM ( TableName[C] )
)
)``````

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!

Super User III

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 &&.

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting
Helper I

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."

Super User I

@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``````

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

Proud to be a Super User!

Helper I

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

Super User III

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.

``````= 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``````

Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling

Proud to be a Super User!

MCSA: BI Reporting

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

#### Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors