ฝัง
- เผยแพร่เมื่อ 9 พ.ย. 2023
- Learn how to check missing parameters using Power Query functions. Also, see how to make your M Code look beautiful.
File Download Link:
www.bcti.com/wp-content/YT_Do...
00:25 Overview of the Problem
01:55 Reviewing the Original Mission
02:19 Error Checking Date Parameters
05:54 Error Checking Text Parameters
09:19 Making M Code Beautiful - วิทยาศาสตร์และเทคโนโลยี
Brilliantly done. You've got a great voice, delivery and pace. I do a lot of these kinds of videos internally for my company and I like your style! Always great content and always appreciated!
Also, if you have a large data set and want to save a step: I'm pretty sure you can combine your "Table.ReplaceValue" steps into one by doing something like:
= Table.ReplaceValue( Table.ReplaceValue(#"Changed Type",
null, #date(1899, 12, 30), Replacer.ReplaceValue ("{Start Date}")
null, #date(2100, 12, 31), Replacer.ReplaceValue ("{End Date}"))
Thanks so much for your kind words and code contribution. I like to combine steps whenever possible, but I sometimes leave that out of my videos for the sake of clarity. Thanks for watching.
Your channel is so underrated! PQ learners really don't know what they are missing. Thank you very much for sharing. I find it hard to give a short name to your vids, as each vid has too much info.
Thanks so much for being a viewer. It gives me cause to do better. Thanks.
I super love your clarity and pace! Looking forward to watching more of your valuable lessons. And thank you so very much for sharing your wisdom.
Thank you for the nice comment.
Very nice error trapping techniques! This 2nd part came with a bang! Thanks again for such great content. I also use the Powerquery formatter for the code. The code in powerquery its very daunting at the beginning of your Mcode journey. Another thing that i do is that I rename all my steps without any spaces in order to avoid the "# " prefix which is also "scary " and this keeps a good hygiene in your code
I love the Power Query formatter. Good-looking code is a MUST! I also rename my steps to get rid of quotes and #. I teach all my students to do the same. Give people good habits from the beginning.
The transpose way is awesome and this is an amazing filter/clear filter way to do it, I used to do it in two steps, the filter then if the filter is empty refer to the previous step
Thanks. Glad it helped improve things for you. Thanks for watching.
I liked you GROUPBY video. It was detail and easy to understand.
I've been using PQ for work but this Parameter tricks got me learning also from your other videos.
I'm liked & subscribed. Thanks!
Thank you so much for subscribing. It makes us SOOooo happy to see the subs number go up.
Fantastic. Thank you so much. Very informative
i very much love this series. thanks a lot for this great storyline. looking forward for more !! 🥰🥰
Thank you!!!
Awsome ! I love your tutos. Thank you (Pascal from France)
Brilliant videos! Thanks
Our pleasure. Thanks so much for watching. 👍🏼👍🏼👍🏼
Again, a fantastic well explained video. I really appreciate the clear and straight-forward explanation of what steps are involved. I've been using Power Query for about 18 months, after having been a long-time advanced user of excel.
I have found Power Query offers a great new dimension to data analysis, but my stumbling block has been around M Code and setting parameters. More often than not the instructional videos tend to over-complicate explanations on these aspects. However, your vid opens the door on this. I have learnt more from this vid and the preceding one than I have from months of dredging through other vids on the same topics. Thank you and pleased to have discovered you
As an educator, that is the highest compliment anyone could offer. You have made my year! I'm so thankful for your kind words.
You should take it as a compliment. Unfortunately, when it comes to learning new things I've been "the slow ship in the convoy" all my life. My break throughs have been down to nothing else but the great talents of people like yourself who have a knack of tapping into my wavelength. Thanks again
the newest excel god to mount olympus. these are fantastic.
Outstanding content!
Thank you. That’s very nice of you to say. We appreciate you taking the time to watch.
Good videos, thanks
Thanks for watching and commenting. You've made us happy!
awesome❤👍🌹
Thanks for taking the time to watch.
Hello, when you say, click on the runner to run the macro, you meant a real VBA macro, so your file is an xlsm type ? Or do you have another mean to trigger the PQ refresh? Like an office script?
There is a VBA macro "attached" to the runner icon that triggers a Power Query refresh. Thanks for watching.
How do you assign macro in Run icon?
❤❤❤
I thought I might get there with the FILTER function. I could deal with the dates similar to your example, but I couldn't think of an easy way to deal with nulls in the other parameters. =SORT(FILTER(Table4,(IF($C$3="",DATE(1899,12,30),Table4[Date]>=$C$3))*(IF($C$4="",Table4[Date]
You should check out the follow-up video to this one that addresses nulls in other parameters. (Parameters with Error Checking)
I took the initial Dataset and created a table called Sales. Select anywhere on the dataset and CTRL + T and go to the Name Manager and rename it to "Sales."
This is what I did using your formula as a start.
=SORT(CHOOSECOLS(FILTER(Sales,(IF($C$3="",DATE(1899,12,30),Sales[Date]>=$C$3))*(IF($C$4="",Sales[Date]
Great Video. I was trying to use the new Groupby function you explained in another video to achieve the same result.
This the formula for the Groupby function
=GROUPBY(CHOOSECOLS(Sales,2,3,5,6),Sales[Sales],SUM,3,0,1,(Sales[Date]>=$AC$3)*(Sales[Date]
My guess is you’ll have to do something similar to the “check for blank” error checking as performed in the video.
This is my solution.
=SORT(GROUPBY(CHOOSECOLS(Sales,2,3,5,6),Sales[Sales],SUM,3,0,1,(Sales[Date]>=$AC$3)*(Sales[Date]
@@michaeldingee743 Great job!!! Glad to see you putting the pieces together. 👍
how did you create the running icon?
That was inserted from the icons library (click INSERT tab, then ICONS button).