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.
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.
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!
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
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
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
@@bcti-bcti Much appreciated and one more question, Do you have a video on a dynamic file name for the data source in QUERY, So we can change the file name in the document and a new table will be loaded
@@bcti-bcti When I import data into Power Query through a folder, I can change the file in the folder and the table in Excel will be updated, but if I pull an Excel file and its path or the name of the file changes, I want to be able to write the name of the file in the Excel sheet and let the query know to turn to this path.
@@yehuditmahalal2070 If I understand your question, the below (scary looking) formula should be able to retrieve the file's full PATH and FILENAME. You could use this as a regular parameter to feed your query's source. Let me know if I misunderstood. =LEFT(CELL("filename"), FIND("[", CELL("filename") ) - 1) & MID(CELL("filename"), FIND("[", CELL("filename") ) + 1, FIND("]", CELL("filename") ) - FIND("[", CELL("filename") ) - 1)
Where are you getting the data in the drop downs for each of the fields within parameter table? I didn't see any individual queries for each of the fields to get a distinct list.
There is a hidden sheet in the download file called "Lists" that contains all of the lists used by Data Validation. Right-click the sheet tab and select "Unhide..." to get to that sheet. Thanks for watching.
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?
Sorry for the delayed response. This question slipped by me. Once you have a macro stored in either the current file or the Personal Macro Workbook, you should be able to right-click any icon/shape/image and select "Assign Macro..."
Love this, but my modification would be to replace the runner icon with a worksheet selection event macro that would run the macro whenever a selection is made.
That is definitely another approach. I'm glad you found part of this useful and that you could tweak it to better suit your needs. Thanks for watching.
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]
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]
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]
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.
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.
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.
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.
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
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.
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)
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.
the newest excel god to mount olympus. these are fantastic.
Very easy to understand each topic.
Glad you found it helpful.
Fantastic. Thank you so much. Very informative
Excellent. Exactly what I was looking for. Thanks👏
Glad it helped.
@@bcti-bcti Much appreciated and one more question,
Do you have a video on a dynamic file name for the data source in QUERY, So we can change the file name in the document and a new table will be loaded
@@yehuditmahalal2070 are you wanting to just be able to adapt to when the file name (and/or file path) changes, or something else?
@@bcti-bcti When I import data into Power Query through a folder, I can change the file in the folder and the table in Excel will be updated, but if I pull an Excel file and its path or the name of the file changes, I want to be able to write the name of the file in the Excel sheet and let the query know to turn to this path.
@@yehuditmahalal2070 If I understand your question, the below (scary looking) formula should be able to retrieve the file's full PATH and FILENAME. You could use this as a regular parameter to feed your query's source. Let me know if I misunderstood.
=LEFT(CELL("filename"), FIND("[", CELL("filename") ) - 1) & MID(CELL("filename"), FIND("[", CELL("filename") ) + 1, FIND("]", CELL("filename") ) - FIND("[", CELL("filename") ) - 1)
Where are you getting the data in the drop downs for each of the fields within parameter table? I didn't see any individual queries for each of the fields to get a distinct list.
There is a hidden sheet in the download file called "Lists" that contains all of the lists used by Data Validation. Right-click the sheet tab and select "Unhide..." to get to that sheet. Thanks for watching.
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 did you create the running icon?
That was inserted from the icons library (click INSERT tab, then ICONS button).
How do you assign macro in Run icon?
Sorry for the delayed response. This question slipped by me.
Once you have a macro stored in either the current file or the Personal Macro Workbook, you should be able to right-click any icon/shape/image and select "Assign Macro..."
Love this, but my modification would be to replace the runner icon with a worksheet selection event macro that would run the macro whenever a selection is made.
That is definitely another approach. I'm glad you found part of this useful and that you could tweak it to better suit your needs. Thanks for watching.
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. 👍
Brilliant videos! Thanks
Our pleasure. Thanks so much for watching. 👍🏼👍🏼👍🏼
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]
Good videos, thanks
Thanks for watching and commenting. You've made us happy!
Outstanding content!
Thank you. That’s very nice of you to say. We appreciate you taking the time to watch.
awesome❤👍🌹
Thanks for taking the time to watch.
❤❤❤