cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kfschaefer
Helper IV
Helper IV

Calculate column handling Null values

Need to add 2 columns together, however one of the columns may contain a Null value and if Null the return the original value.

 

what is the proper syntax?  I need to add the Revenue columns and also the Cost columns together and if one is null the return the column that contains an actual value.

Capture1.PNG

 

 

1 ACCEPTED SOLUTION
Sean
Community Champion
Community Champion

@kfschaefer

select the column => Transform tab => Replace Values => Value To Find: null Replace With: 0

 

You may have do this step "Replaced Value" before the "Added Custom" where you add the 2 columns

View solution in original post

8 REPLIES 8
Rickmaurinus
Resolver I
Resolver I

There's plenty of ways to do it. I like the List.Sum option. But one could also catch the Nulls through conditional-if statements. Or one could also replace null by 0.  Take one that feels comfortable and if the performance is bad, try another. Also check out: 

 

3 Ways to SUM null values in Power Query 

 

Cheers,

Rick

try this (case sensitive)

 

=if [column 1] = null then [column 2] else [column 1] + [column 2]



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Sean
Community Champion
Community Champion

@kfschaefer

select the column => Transform tab => Replace Values => Value To Find: null Replace With: 0

 

You may have do this step "Replaced Value" before the "Added Custom" where you add the 2 columns

View solution in original post

Wherever possible I avoid changing data.

Try this instead:

each List.Sum({[ColA], - [ColB], [ColC]})

 

to perform ColA - ColB + ColC

My full line being:

= Table.AddColumn(#"Previous Step Name", "Sum", each List.Sum({[ColA], - [ColB], [ColC]}), Int64.Type)

Very elegant solution - thanks!
LP135
Frequent Visitor

This helped me a lot, @lolouk44

Thanks!

Anonymous
Not applicable

Thank you Lolo,

I will give it a try!

Anonymous
Not applicable

Hello,

I have tried this and it worked for the sum column, thank you.

However, I also need to add a custom column that concatenates those same number type columns (plus others that are text type), so for that I need to replace nulls with "" rather than "0", otherwise the custom column captures the "0" as a text, so it doesn't 'ignore' it. I have tried to sort this out (the type mix type problem) by using Text.From, but this function doesn't accept null values, since it returns null as long as one column contains null in that row... unless one of you guys come up with an easy solution I couldn't figure out I think I will up and turn to Excel (after the query) or DAX when I load to the model. Do you know where I could find a guide or in which language use for each case and in which order should the transformations be applied?

Many thanks in advance

Javier

 

 

Helpful resources

Announcements
MBAS on Demand

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.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Urdu Hindi D365 Bootcamp 768x460.png

Urdu Hindi D365 Bootcamp

Dont miss our very own April Dunnam’s The Developer Guide to the Galaxy! Find out what the Power Platform has to offer for the traditional developer.

Top Solution Authors
Top Kudoed Authors