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

[@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 👍

[@GrainneDuggan_Excel]

Intriguing examples. Thanks Mark

[@michaelbrown8821]

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

[@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.

[@Trucpq]

Another good practice. Thanks

[@ziggle314]

Thanks Mark. Great job.

[@pratiklpatel]

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

[@ExcelOffTheGrid]

The less nulls the better 😁

[@raimundojs9547]

Perfect! Thank you!

[@gonzuic]

Great information, really great. Thanks

[@mirrrvelll5164]

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

[@mohammedelsakally540]

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

[@ExcelOffTheGrid]

Thank you - I’m glad you like it.

[@ramruttunaubeeluck9235]

Thank you Mark

[@ExcelOffTheGrid]

You’re welcome 😁

[@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.

[@zzota]

Great trick. Thanks, Mark.

[@ExcelOffTheGrid]

Glad you liked it! 😁

[@Ratnakumarwrites]

Great learning... Thanks 😀

[@ExcelOffTheGrid]

My pleasure

[@yousrymaarouf2931]

amazing

[@sagunmaghaia4831]

Awesome

[@ExcelOffTheGrid]

Thank you.

[@kebincui]

👍❤

[@ExcelOffTheGrid]

Thanks Kebin.