Power Query - COALESCE Operator (Shorten those formulas with a cool "mystery" operator)

Learn how to write more efficient formulas using the COALESCE operator when dealing with NULLs in the source data. This is a great way to shorten If...Then...Else statements as well as Try...Otherwise statements.
File Download Link:
www.bcti.com//wp-content/YT_Downloads/BCTI_PQ_COALESCE.xlsx
00:49 File Download Instructions
00:58 Problems when working with NULLs
02:22 Detecting and Replacing Missing Data
06:18 Using COALESCE to Replace IF...THEN...ELSE Statements
09:37 Conclusion

[@PatrickAngwin]

This is GREAT. I don't know how long your channel has been going; I have been watching Excel tutorials for many years and only recently come across you, but BCTI is fast becoming one of my favourite resources. Not only because your teaching style is so well paced, clear and easy to follow but, more importantly, because you often cover and introduce me to really useful, practical techniques I haven't seen elsewhere. Really good work, keep it up!

[@bcti-bcti]

WOW!!! You've made my day. Such high praise. We just had our 1-year anniversary a few weeks ago, so we are still in our infancy. We hope to grow big and strong like the other channels. With your help, we'll get there. Thanks for watching.

[@AJ-fd3yj]

... this is the third epiphany i had, thanks to you, how to shorten and improve my queries. thank you!

[@bcti-bcti]

FANTASTIC! So glad to hear it helped.

[@baskis69]

I was completely unaware of this operator. Very useful. You always deal with issues that help us in our day to day! Thank you very much for sharing.

[@bcti-bcti]

My pleasure. Thanks for watching.

[@IvanCortinas_ES]

Excellent explanation. Thank you!!

[@sharmarudra]

Worth in Gold. Thank you.

[@bcti-bcti]

You are very welcome! Thanks for watching.

[@RichardJones73]

The number of times I created another column for null values to become 0, is replaced value null with 0, when I didn't need to! Awesome tip

[@bcti-bcti]

Yeah, I used to do the very same thing. Now my queries have fewer steps. I love being able to get to the objective with less work. Thanks for watching.

[@Donkeys_Dad_Adam]

this is EPIC.

[@bcti-bcti]

Thanks! I think so, too.

[@leewism]

Never saw this option before, love it. I think I will be using it a lot.

[@bcti-bcti]

Same here. I find it quite useful. Thanks for watching.

[@suvojitghoshal1453]

Truly Truly Awesome content. Just Brilliant. !!

[@LilLinh]

Thank you very much, your video were so interesting, smart and so unique.

[@bcti-bcti]

Thank YOU!!!!! 🥰

[@cuongtrieuduy4916]

That’s awesome. Never see it before. Thank men

[@bcti-bcti]

@@cuongtrieuduy4916 glad you liked it. Thanks for watching

[@user-dn5gd1rn9f]

This is great!!! I really struggle with blank date fields. Can’t wait to try this!

[@bcti-bcti]

So glad to hear that may help. Thanks for watching.

[@Undistinguished-ux9eg]

Amazing explanation of this little-known Power Query operator. I have already used it at work today, shortening my rather longer If formula. My use case: My imported bank account data has the debits and credits in separate columns (both being positive figures). I like them combined into 1 column. So instead of saying “If” debit column has a figure then give me that figure, “Else” give me the credit figure multiplied by minus 1, I just say: [Debit] ?? -[Credit]. Aside of being shorter and more efficient - it just make me feel so much cooler and gives me some unexplainable satisfaction. Thank you sir, and adding to my other comment today, you’re now the GOAT of Power Query too:)

[@jazzista1967]

Very elegant trick with the ?? 0 if null technique. When i deal with scenarios like those, I would usually replace the null with zeros by using the find and replace user interface that way, PQ will perform the operation i need ( Lets say add or subtract). Just subscribe to your channel .Thanks

[@bcti-bcti]

Thanks for your time and for subscribing!

[@Jill_Liu]

Such an amazing tricks. I have been following your channel for a while i noticed that your tutorials are quite advanced explaining in a simple way. Great thanks for your sharing.

[@bcti-bcti]

Thank you so much for taking the time to watch. I appreciate your thoughts. Very nice comment.

[@djl8710]

Cool thanks!

[@bcti-bcti]

You are most welcome. Thanks for watching.

[@larmondoflairallen4705]

Gadzooks! I wish I had known about this capability years ago, but I am going to wear this out now.

[@bcti-bcti]

AWESOME!!! Wear it out, my man.

[@ChiMickE]

Didn't know that existed, very useful

[@bcti-bcti]

I couldn't agree more. Thanks for watching.

[@Giridharan952]

wow .... great time saving ..sir,really useful for m code learners and also high practical use applicability

[@bcti-bcti]

I couldn't agree more!

[@jerrydellasala7643]

After retiring in June 2020, I learned about Power Query, and have watched well over a thousand PQ videos. This is the first time I heard of the Coalesce operator! Nice!!

[@bcti-bcti]

Yeah, it’s something I had never seen or heard until just a few months ago. I’m not sure why this is such an overlooked feature. Thanks for watching.

[@GosCee]

I have watched numerous videos as well, and this is the first time I've heard of coalesce. Such a useful tip!

[@bcti-bcti]

@@GosCee I agree 100%. Thanks.

[@hichamhadj9640]

Your videos are amazing 😭

[@bcti-bcti]

Thank you. That’s a very complementary thing to say. Thanks for watching.

[@subbu_ca]

Just awesome

[@bcti-bcti]

Thanks!!!!

[@thelastfry23]

Fantastic video! I didn't even know PQ behaved like that on additions, makes complete sense now why sometimes I run into issues. Will definitely put this to good use in the future! Keep up the excellent videos!

[@bcti-bcti]

Glad to have helped!

[@RonDavidowicz]

I haven’t used this before, unfortunately I probably won’t know what I was doing if I looked at it later!😊

[@bcti-bcti]

I suffer from that syndrome as well. If I don't use it often enough, I'll just end up confusing myself later. But I think this is cool enough to try working into my day-to-day operations. Thanks for watching.

[@serdip]

This was AMAZING!! Thank you so much for demonstrating this very powerful M Code technique, which greatly simplifies formulas in many common scenarios. I thought COALESCE() was just a function in T-SQL. :-) I wanted to try and make the summation of the Interantional and Domestic Sales table dynamic, meaning that if for some reason the source dataset was updated with another column, e.g. [Local Sales] (I couldn't think of a good name, LOL) or perhaps the source column names were changed. I noticed that the solution you provided, since it has hard coded column names, will not update to reflect values in the new [Local Sales] column or if the names of the columns changed. My solution was to convert each row of the table to a Record (actually each row *is* a record but in my thought process I had to "convert" it explicitly to a Record object), convert that Record to a List, select on the numeric values in the list and finally apply List.Sum() to the result. My solution is not very appealing visually, but it does handle the new [Local Sales] column automatically. It also works if any of the numeric columns in the source data gets renamed. So, at least in some limited cases, my solution might be helpful. Dynamic Summation - Column Names Not Hard Coded = Table.AddColumn(#"Removed Columns", "Sum Numeric Columns", each List.Sum( List.Select( Record.ToList(_), each Value.Is(_, Number.Type) ) ) ) Again, this video (and all the rest of your content, quite frankly) is incredible! 😎 Thank you kindly.

[@bcti-bcti]

Thank you so much for taking the time to contribute to the channel. I think it's great that you are taking the idea to a whole other level. I'm sure other readers will appreciate your contribution. It's always a good idea to try to make things dynamic if possible.👍👍

[@lesterpotts6142]

Thank you, much appreciated. You can add Cole Lesch now to your list of sales reps.

[@bcti-bcti]

You are SOOOO right! That's a good one!

[@williamarthur4801]

New to the channel, will subscribe,

[@bcti-bcti]

Thank you 👍🏻👍🏻👍🏻

[@ManthaarJanyaro]

That's good 👍🏻😊

[@bcti-bcti]

Thanks 😄

[@txreal2]

Thanks 😊

[@bcti-bcti]

Welcome 😊