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!
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.
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.
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!!
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.
Î never heard of COALESCE operator before. I use to use (nested) if statements. Obviously COALESCE reduces the lines of code drastically. I will give it a try. I recently stumbled across this channel and I think the content and tutorials are really great and interesting. Please keep up the good work.
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!
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:)
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.
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.👍👍
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
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.
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!
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.
All Excel licenses should come with a BCTI link
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
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.
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!!
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.
I have watched numerous videos as well, and this is the first time I've heard of coalesce. Such a useful tip!
@@GosCee I agree 100%. Thanks.
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.
Thank you so much for taking the time to watch. I appreciate your thoughts. Very nice comment.
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.
My pleasure. Thanks for watching.
this is EPIC.
Thanks! I think so, too.
Î never heard of COALESCE operator before. I use to use (nested) if statements. Obviously COALESCE reduces the lines of code drastically. I will give it a try.
I recently stumbled across this channel and I think the content and tutorials are really great and interesting. Please keep up the good work.
We're so glad you have found the content useful. Thanks for your support.
Please please please make more Power Query videos. Your teaching style is fantastic!
@@noneyabeezwax8865 I’ll have a Power Query video out in a day or two. You’ll love it!!!! (Thanks for the compliment)
Here you go, as requested:
ruclips.net/video/UYHrLO1-iyE/видео.html
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!
Glad to have helped!
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:)
[Debit] ?? -[Credit] is great. Thank you!
... this is the third epiphany i had, thanks to you, how to shorten and improve my queries. thank you!
FANTASTIC! So glad to hear it helped.
Never saw this option before, love it. I think I will be using it a lot.
Same here. I find it quite useful. Thanks for watching.
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.
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.👍👍
Worth in Gold. Thank you.
You are very welcome! Thanks for watching.
Truly Truly Awesome content. Just Brilliant. !!
Thanks for the great explanation and examples, especially the last one.
@@brianxyz You’re quite welcome. We appreciate your support.
This is great!!! I really struggle with blank date fields. Can’t wait to try this!
So glad to hear that may help. Thanks for watching.
Your videos are amazing 😭
Thank you. That’s a very complementary thing to say. Thanks for watching.
Excellent explanation. Thank you!!
Thank you for sharing these insightful tips.
So glad you found them useful. Thanks for the views.
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
Thanks for your time and for subscribing!
Gadzooks! I wish I had known about this capability years ago, but I am going to wear this out now.
AWESOME!!! Wear it out, my man.
Thank you, much appreciated.
You can add Cole Lesch now to your list of sales reps.
You are SOOOO right! That's a good one!
wow .... great time saving ..sir,really useful for m code learners and also high practical use applicability
I couldn't agree more!
Great content and very practical. Thank you!
We appreciate you taking the time to watch and comment. 👍👍👍👍
This is something really great....Thank you GRAY !!!!
It IS rather cool!!! Thanks for watching.
That’s awesome. Never see it before. Thank men
@@cuongtrieuduy4916 glad you liked it. Thanks for watching
This is Great... Thank you very much for saharing.
You are very much welcome. Thank you for taking the time to watch and comment.
Cool thanks!
You are most welcome. Thanks for watching.
Didn't know that existed, very useful
I couldn't agree more. Thanks for watching.
Just awesome
Thanks!!!!
Thank you very much, your video were so interesting, smart and so unique.
Thank YOU!!!!! 🥰
I haven’t used this before, unfortunately I probably won’t know what I was doing if I looked at it later!😊
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.
You can add a note for yourself (or others) in the step properties to remind yourself about the ??
@@1qtaz great idea. Documentation is always a good thing.
How do I get the number formats to be included when I send the PQ data into Excel ?
Power Query has no responsibility to make the data look "pretty", That is the responsibility of Excel.
Thanks 😊
Welcome 😊
New to the channel, will subscribe,
Thank you 👍🏻👍🏻👍🏻
That's good 👍🏻😊
Thanks 😄
I can't believe it can be solved so easily. "?? 0"
Yes; it's crazy simple. Thanks for watching.