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
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!
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. 👍
@@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.
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!!
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
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.
This😊
Yep, you got it 👍
Example 3 is genius over tested ifs. Love it Mark.
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!
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. 👍
@@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.
Thank you Mark for the valuable content you are providing to your followers 👍 🙏
Thank you - I’m glad you like it.
OK, i subscribed. I coalesced. Thx for this great feature.
Great video, thanks!
Glad you liked it!
Great trick. Thanks, Mark.
Glad you liked it! 😁
That´s a really, really useful concept 😃 Never even heard of it before 🤯 Thank´s a bunch!
It’s a niche piece of underground knowledge - I hope you can put it to good use.
Thanks Mark. Great job.
Thank you Mark
You’re welcome 😁
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!!
Awesome, great work. Glad I could help 😁
I already subscribed, this would have saved one more record being null :)
Great video by the way.
The less nulls the better 😁
Intriguing examples. Thanks Mark
Great learning... Thanks 😀
My pleasure
This was a neat one. ☝️ I find it useful
Perfect! Thank you!
Another good practice. Thanks
amazing
Great information, really great.
Thanks
Awesome
Thank you.
👍❤
Thanks Kebin.
Awesome