Power Query Secrets: Use coalesce (??) to handle null values

[@ExcelOffTheGrid]

Sign up for our free Insiders program and get the example file:
exceloffthegrid.com/insiders-signup/
File Reference for this video: 0228 Coalesce in Power Query

[@timtunbridge]

Example 3 is genius over tested ifs. Love it Mark.

[@zingmo]

This is a great way to have values in one column override the values in another if they are present without having to create a conditional column.

[@keylanoslokj1806]

This😊

[@ExcelOffTheGrid]

Yep, you got it 👍

[@williamelliott5999]

In your trial balance example, the "each [Debit] ?? 0 - [Credit] ?? 0" only seems works when one column or the other is, in fact, null. If there is a risk that there are numbers in each column, the this formula only returns the first column.
A safer variant would be to surround the elements with brackets "each ([Debit] ?? 0) - ([Credit] ?? 0" ) .
My favoured route is to use List.Sum which acts like Sum in Excel so is quite forgiving (if that's what you want) and i would write "each ListSum({[Debit],[Credit]})" This has the flexibility to work across adding several columns with some safety!

[@ExcelOffTheGrid]

Good point about the calculation precedence. With my data it would be impossible to have a debit and a credit at the same line, so it will work.
Good use of brackets and List.Sum to avoid it. 👍

[@DaveIsAtWork-Really]

@@ExcelOffTheGrid Normally it is not possible to have both a Debit and a Credit balance for an account, but I sometimes work with multiple divisions or companies listed side by side, giving something like Co1.Dr, Co1.Cr, Co2.Dr, Co2.Cr ..... = Grand Total Account Balance. Enclosing each element with () would get to the right total.

[@rpopecpa]

Hi Mark, I love your videos. I just ran into a scenario where I was filtering out rows with data that contained specific text. In my case, it was the word "DEPOSIT". So I created a filter that said 'each not Text.Contains([Description], "DEPOSIT")'. This did not work as my [Description] column contained nulls that I wanted to keep. Therefore, I added the Coalesce formula. 'each not Text.Contains([Description] ?? "", "DEPOSIT")' Now, the formula works as intended. Thanks!!

[@ExcelOffTheGrid]

Awesome, great work. Glad I could help 😁

[@michaelbrown8821]

OK, i subscribed. I coalesced. Thx for this great feature.

[@stefankirst3234]

That´s a really, really useful concept 😃 Never even heard of it before 🤯 Thank´s a bunch!

[@ExcelOffTheGrid]

It’s a niche piece of underground knowledge - I hope you can put it to good use.

[@mohammedelsakally540]

Thank you Mark for the valuable content you are providing to your followers 👍 🙏

[@ExcelOffTheGrid]

Thank you - I’m glad you like it.

[@zzota]

Great trick. Thanks, Mark.

[@ExcelOffTheGrid]

Glad you liked it! 😁

[@Ratnakumarwrites]

Great learning... Thanks 😀

[@ExcelOffTheGrid]

My pleasure

[@GrainneDuggan_Excel]

Intriguing examples. Thanks Mark

[@pratiklpatel]

I already subscribed, this would have saved one more record being null :)
Great video by the way.

[@ExcelOffTheGrid]

The less nulls the better 😁

[@Trucpq]

Another good practice. Thanks

[@mirrrvelll5164]

This was a neat one. ☝️ I find it useful

[@extraktAI]

Great video, thanks!

[@ExcelOffTheGrid]

Glad you liked it!

[@ziggle314]

Thanks Mark. Great job.

[@ramruttunaubeeluck9235]

Thank you Mark

[@ExcelOffTheGrid]

You’re welcome 😁

[@gonzuic]

Great information, really great.
Thanks

[@raimundojs9547]

Perfect! Thank you!

[@yousrymaarouf2931]

amazing

[@sagunmaghaia4831]

Awesome

[@ExcelOffTheGrid]

Thank you.

[@kebincui]

👍❤

[@ExcelOffTheGrid]

Thanks Kebin.

[@darrengraham27]

Awesome