I've heard other instructors take on the issue of parameters in other ways, and yours is a lot easier to understand and robust. Also, your pacing and clarity is EXCELLENT. I love your teaching style and have become an immediate follower. I will also look for any courses that you may have. Thank you very much.
Excellent video! I had no clue what I was doing, but I was able to follow every click. The explanation was thorough, concise, and articulate and presented in a nice tone of voice.
I've watched many Power Query videos and learned from some of the top experts on TH-cam, but I can confidently say this is the best video I've seen. Your explanations are so clear that even someone like me, with limited understanding of Power Query, can easily grasp the concepts from the start. Thank you for creating such high-quality content. It's incredibly helpful, and you've made the learning process so much easier. Keep up the great work!
@@IrfanChanna Wow!!! You have made my day! Those are some of the most flattering, confidence boosting comments I’ve been given. Thank you so much for your thought and viewers.
Your channel has the most unique solutions to several different platforms. I took your sample file and added 2 different ways to use your parameters selections. Used the new Filter Function and Groupby. I also created another panel using checkboxes. I checked one of my customers to see what they had in their Current version of 365. They had Checkboxes and Filter. I used the Let function for Checkboxes and Filtering. I can send you a copy if you wish to see what I have done and maybe for your subscribers how to improve or share.
@@michaeldingee743 wow! That is so great that you’re mixing these skills. That is the hope I have for all users. I’d be happy to take a look at what you’ve done. Send it to training@bcti.com
I just chanced upon your video and I must say, it's fantastic! I will check the other videos too. Thanks a lot for your channel, wishing you all the best and lot of success!
What a great explanation and elegant at the same time without much Mcode. Thanks for the idea. I have a handful of queries pulling from SQL that I wanted to create parameters to bring the required data. Thanks for such a great posting. You are the best source in YT for Powerquery.
Thank you so much for your high praise. Make sure you watch the follow-up video to this one where I introduce error-checking into the process. th-cam.com/video/xHi8ofM5GSg/w-d-xo.html
@@bcti-bcti Thanks... I will look at it... Man.... you post great content about powerquery: and to the point! And your pace is superb !!! Happy Memorial weekend!
I forgot: Do you have any video about on how to work with columns: In order words, i am trying NOT to hard code the columns so that if new columns are added, the query won't break. I know that you can use Table.ColumnNames but i am getting hangup on the argument which is a list. Let me know and thanks again for your posting such great content
@@jazzista1967 Let me see what I can put together for you on this topic. Any way we can make Power Query more dynamic is always a winning direction. (Down with red text in M Code.)
@@bcti-bcti Haha! Completely Agreed! Down with the red text... Hardcoded columns have gotten my in trouble many times however . Thanks in advance for the vudeo...
In boxing, this video would have been a knock-out punch! We used a similar trick in designing Microsoft Access forms/reports where referenced fields are hidden. Thank you so much.
Man i loved this video a lot. I was using multiple 2 rows filters like from 2017 , the time when i started using this hidden gem PQ. And after these many years i understood this amazing way of filtering.❤. Its a game changer for me. As i apply like 4 5 different filters in one sheet , so had create like 4 5 different connection queries. So my query tab has lot of queries which is a different issue, but i am basically missing a simplified approach. Well this is very exciting. Also, there are many other experts on PQ and yet they have almost not covered this way filtering
Excellent! Excellent strategy for creating a filtered report and excellent presentation style (timing, rationale, steps, etc). I am curious about one aspect: In your initial demonstration it appeared that you clicked on the runner icon to refresh the query. I'm assuming there was some VBA behind that action. I'm wondering if there's a non-VBA way to "automate" the query refresh (vs Refresh all), where the user doesn't have to right-click / refresh the query results. Also, it appeared that your initial demo showed only the query results, but I'm assuming that was because your data table was on another sheet.
Q1 - Query Updates: You could set the properties of the query to automatically refresh when the Excel file is opened, and/or refresh every N number of minutes. Unfortunately, there is no "event" refresh (i.e., detecting a change in the data), that is built-in at the query level. Thus, the need for a VBA macro. Q2 - Absence of Source Data: Yes; I had the data on a different sheet to simplify the mission overview, and to show how it will likely appear when used in the "real world". The source data is typically hidden from the user, and all they see is the question interface and results. Thanks for watching!!!!
Excellent explanation. Thanks. How can I clear the table from the filters, that is, return it to the state without filters? Should I create a new parameter?
Brilliant. my current query with parameter table, then split into five separate queries, then drilled down to the respective parameter. This way from BCTI is much, much simpler. Requires familiarity with referencing table rows, though.
For all of you (like me) who are starting out with some of this, here's a tip for us amateurs: Clean & Trim your data as early as possible. I've been trying to replicate this work above for 3 hours and couldn't get it to work - simply because I had an extra space at the end of some of my data. I wouldn't mind but I've had this experience before and not learnt from it! Hopefully, the pain from this will help 🙂. Regardless, thanks for this it is really valuable.... 👍
lovely ...yes great ...will build it also like this ...have thousand sor Row ...and this sounds great to speed up the changes ...thanks for your effort to tech us ....
Sure; the original data can be on another sheet and then that sheet can be hidden. There’s no requirement that the source data be on the same sheet as the query/output sheet. I have them together in the video just for demonstration purposes; to make it easy to cross-check the results to the source. Thanks for watching.
I didn't know that Date.From (and it's companions) slowed the query down so much, good to know this! Could be a good video idea: things slowing down power query (especially for larger datasets)
I have used named ranges (cells) for importing parameters into PQ. It is hit and miss for me, as they don't always update with a refresh all. I like the idea of creating a table (final tip) for that. Thanks! I always appreciate learning about different approaches. How would you code the filter steps when you want a blank to mean return all items that match the other parameters. For example just filter by dates, or Product or (Region and Supplier) (if searching by Region you have to specify a Supplier)?
Thank you for taking the time to watch. Make sure to check out the follow-up video to this one: "Error Checking Parameters". This one will answer your question. th-cam.com/video/xHi8ofM5GSg/w-d-xo.html
Awesome way of teaching! Content is also first class! I would like to ask one question regarding the refresh. We refresh only one query (the output of filtered SalesInfo table) without refreshing the Parameters table after we make changes to parameters. Does it mean that Power Query implicitly refreshes also other queries used in the one that we triggered the refresh on? I am curious as we could simply of course click "refresh all" and then it would be understandable that all the queries get refreshed. I would be thankful for Your guidence on this topic. Many thanks again!
The macro being run in this example refreshes every query in the workbook. I like to put some sort of "run" button for the user since they will likely be unfamiliar with the "Refresh" requirement. I used an icon because I thought it was cute, but I usually place a labeled button that says something like, "Click here to update report". There are ways to write macros that only refresh specific queries, thus avoiding unnecessary delays in refreshing queries unrelated to this objective (i.e., queries on other sheets for other reports.) If the report were just for me, I would just use the built-in refresh mechanisms. Thanks for your kind words and for taking the time to watch the video(s).
The underlying VBA code attached to the “running man” that refreshes the query is in the file download. The like is in the video description. Thanks for watching.
@@bcti-bcti I did download the file but this is what I found "The macros have been removed as many companies will not allow users to download and/or open files containing macros." Can you show the code here please?
@@MsMikeful Sorry. Forgot about that. Here you go: Public Sub UpdatePowerQueries() Dim lTest As Long, cn As WorkbookConnection On Error Resume Next For Each cn In ThisWorkbook.Connections lTest = InStr(1, cn.OLEDBConnection.Connection, "Provider=Microsoft.Mashup.OleDb.1") If Err.Number 0 Then Err.Clear Exit For End If
Very nice. Another 'single query' approach would be to ingest the three custom slicers with multiple source statements plus a few inline merges & you're done.
interesting topic addressed with clarity and professionalism. a question! I couldn't understand how you associated the query refresh with the icon... With a macro? and then, the Product and Region fields that you take via drop-down fields, how did you create them... with data validation? thanks for your answers.
Fantastic tutorial on parameters ! thank you! Can you tell me if you created the created the drop down lists for product region and supplier prior to this ? I wondered if you could also have a dynamic drop down list from the output table Example - if you only picked a date rate - then refresh and then (example) the region selection is only from the result of the date output ?
The dropdown lists were created using the Data Validation tool's "list" option. For your other question: I'm sure something could be created to do that secondary filtering, but I'd need to play around with a few methods to see what is the best way to handle that.
Very nice video. However, what if each condition chooses 2 or more? Product example: select two products Basketballs and Stepper Machines, what is your proposed plan
Is it possible to call the value to use it in an add Table function? For example, I neeed to call several parameters to calculate columns with those parameters as drivers
@@bcti-bctithank you I am French and I was a little bit anxious not to well understand you but no 👍🏻 you do not speak too quickly, everything is fine 😊
Instead of hardcoding the row you could always use Parameters{[Option = "Product"]}[Selection] Now the only problem is if you change the name of an option in Excel's table xD Or you can have the parameters in single cells (named ranges) and import the names to power query. Mores queries to import but no problem having to select row or column or conversions to date or any other type
Great ideas!!! That's what we love about PQ: there's always more than one way to solve the same problem. You just have to weigh the pros & cons of each strategy and go with what you prefer. Thanks for sharing.
I have a question: Is there a way if we want one of the parameters to include all records on that field? I mean, all other parameters in place for 'all products' for example.. Is it possible that power query understands this when I left this field blank in the parameter table? Or any other way?
Make sure you watch the follow-up video to this one where I introduce error-checking into the process. th-cam.com/video/xHi8ofM5GSg/w-d-xo.html This will solve your problem. Thanks for watching.
Does it use wildcard if you dont want to filter by a certain field. The video assumes you are always using 5 filters. What if I only want to use 3 or 4?
Not in this example, but if you watch the follow-up video to this one ("Parameters with Error Checking"), you will see how to deal with empty selections.
You are correct. This would be useful for single selections. I think a followup video showing multiple selections is needed. I'll see if I can make one up. Thanks for the suggestion.
Great with one comment. What if one of the parameters is empty? Query will break. I think you should use if clause in M-code if the parameter is null. I hope you got the point.
@@ewilner there is no specific video for that part. All you need to do is, in the second table (green table), just enter an equals sign, then click on the cell in the blue tables you wish to carry over to the green table. Download the sample file to examine it in more detail.
Yeah, this solution is only good for 1 selection in each field. I’d have to work something up to see how to deal with multiple selections per field. Thanks for watching.
@@bcti-bcti I scrape website with type (1...3 ) to collect data, the data finance have 3 statement. So I create 3 statement with source website different type, and then merge 3 query that finished statement. But I want to ID table parameter. How should i do
@@DauTuDaiHan999 I think this is something I would need to work on first-hand to explore the nuances of the needs and issues. Without access to the data and a full understanding of the goal, it's difficult to advise properly.
I may not understand your question as intended, but if you want only a single row from the "Parameters" table, you'd have to highlight only the needed row and bring it in as a range.
This filter preemption,will be a nightmare when you have say over a handful of columns. The amount of filter steps that you need to setup initially may take an enormous amount of unnecessary time, don't you think?
@@bcti-bcti yeah I agree that this serves a purpose when there are only a handful of columns as we are prempting which column the filters will apply. Have you another effective approach that can apply to the preemption of say 50 columns which could potentially have needs of filtering please?
I've heard other instructors take on the issue of parameters in other ways, and yours is a lot easier to understand and robust. Also, your pacing and clarity is EXCELLENT. I love your teaching style and have become an immediate follower. I will also look for any courses that you may have. Thank you very much.
Thank you SOOOO much for the kind words!!! You have absolutely made my day (maybe even my week.)
I second that
Excellent video! I had no clue what I was doing, but I was able to follow every click. The explanation was thorough, concise, and articulate and presented in a nice tone of voice.
Thank you so much for the kind words. I’m glad it helped. Thanks for taking time to watch and comment. 👍🏻
You just created so much work for this perfectionist Excel user.
I hope that’s a good thing. Thanks for watching.
I've watched many Power Query videos and learned from some of the top experts on TH-cam, but I can confidently say this is the best video I've seen. Your explanations are so clear that even someone like me, with limited understanding of Power Query, can easily grasp the concepts from the start.
Thank you for creating such high-quality content. It's incredibly helpful, and you've made the learning process so much easier. Keep up the great work!
@@IrfanChanna Wow!!! You have made my day! Those are some of the most flattering, confidence boosting comments I’ve been given. Thank you so much for your thought and viewers.
I know this is a year old but just watched this and found it to be excellent, thank you!
@@chrism9037 Better late than never. Thanks for the support!!!
Your channel has the most unique solutions to several different platforms. I took your sample file and added 2 different ways to use your parameters selections. Used the new Filter Function and Groupby. I also created another panel using checkboxes. I checked one of my customers to see what they had in their Current version of 365. They had Checkboxes and Filter. I used the Let function for Checkboxes and Filtering. I can send you a copy if you wish to see what I have done and maybe for your subscribers how to improve or share.
@@michaeldingee743 wow! That is so great that you’re mixing these skills. That is the hope I have for all users. I’d be happy to take a look at what you’ve done. Send it to training@bcti.com
Wow! This is a game changer for a newbie to Power Query like me! Thanks for the video!
So glad you found this helpful. You won't be a newbie for long. Thanks for watching.
subscribed because this deserves a nobel prize. i have so many uses for from this.
So glad to hear. I'm glad it has helped you.
outstanding tutorial, clear, concise and yet thorough, I am very appreciative of your work, thanks
Thank you. We appreciate your time.
I just chanced upon your video and I must say, it's fantastic! I will check the other videos too. Thanks a lot for your channel, wishing you all the best and lot of success!
Thank you! We're still in the infancy of TH-cam videos, but we're hoping to grow big and strong.
If you say this video as infancy, then there are exciting avenues for us subs in future 😅
Excellent Video. Cristally clear and elegantly presented. Looking forward to more advanced power query videos like this. Thanks for sharing 🌹👍
Thank you for watching and taking the time to say such nice things. You've made our day!
Great video. The ideas can used in an interactive dynamic way in varied contexts with the illustrated PARAMETER approach. Thank You.
Thank you for taking the time to watch. Make sure to check out the follow-up video to this one: "Error Checking Parameters".
Thanks for letting me know. @@bcti-bcti
This deserves a lot more like and subs. Excellent tutorial and examples.
Thank you. We appreciate the thoughtful comment.
What a great explanation and elegant at the same time without much Mcode. Thanks for the idea. I have a handful of queries pulling from SQL that I wanted to create parameters to bring the required data. Thanks for such a great posting. You are the best source in YT for Powerquery.
Thank you so much for your high praise. Make sure you watch the follow-up video to this one where I introduce error-checking into the process.
th-cam.com/video/xHi8ofM5GSg/w-d-xo.html
@@bcti-bcti Thanks... I will look at it... Man.... you post great content about powerquery: and to the point! And your pace is superb !!! Happy Memorial weekend!
I forgot: Do you have any video about on how to work with columns: In order words, i am trying NOT to hard code the columns so that if new columns are added, the query won't break. I know that you can use Table.ColumnNames but i am getting hangup on the argument which is a list. Let me know and thanks again for your posting such great content
@@jazzista1967 Let me see what I can put together for you on this topic. Any way we can make Power Query more dynamic is always a winning direction. (Down with red text in M Code.)
@@bcti-bcti Haha! Completely Agreed! Down with the red text... Hardcoded columns have gotten my in trouble many times however . Thanks in advance for the vudeo...
Thank you. This is excellent presentation. You make it easy to follow along.
Thank you so much for saying so. Make sure you check out the follow-up video to this one titled "Error Checking Parameters".
really very informative lecture and helpful in daily work. Explanation is remarkable. Thanks
Thank you for saying so. Much appreciated.
In boxing, this video would have been a knock-out punch!
We used a similar trick in designing Microsoft Access forms/reports where referenced fields are hidden.
Thank you so much.
Thanks for such a nice (and creative) comment! We appreciate you taking the time to watch.
Magnificent video and explanation.
I personally will change my method of filtering Data tables to this one instead of using slicers.
Top, top, top!!
Don't give up completely on Slicers; they're still wonderful. Thanks for watching.
Man i loved this video a lot. I was using multiple 2 rows filters like from 2017 , the time when i started using this hidden gem PQ. And after these many years i understood this amazing way of filtering.❤. Its a game changer for me. As i apply like 4 5 different filters in one sheet , so had create like 4 5 different connection queries. So my query tab has lot of queries which is a different issue, but i am basically missing a simplified approach.
Well this is very exciting.
Also, there are many other experts on PQ and yet they have almost not covered this way filtering
@@shirsN We’re so very happy that you found this helpful. It’s comments like yours that make it all worth while. Thanks for watching and commenting.
Man. Parameters is not an easy topic but the way you explained ❤
Thank you so much for the compliment. I struggle at the beginning as well. Hope it helped. Thanks for watching.
Excellent video - parameters rock!
@@petercompton538 They do, indeed.
Just got a subscriber! I’ve never edited in the code bar to have it reference something dynamic like that. Really fun to learn!
Thanks. Glad you enjoyed it. We appreciate your time.
This channel deserves more views. Good job!
Thank you for saying so. We're new, so it will take time for viewers to discover us. But we completely agree :)
I have a follow-up video releasing Thursday to this one. Don't miss it; it will help tremendously.
Excatly what I was looking for. Thanks!
@@johnnewman5339 Glad it helped!!!
Excellent! Excellent strategy for creating a filtered report and excellent presentation style (timing, rationale, steps, etc).
I am curious about one aspect: In your initial demonstration it appeared that you clicked on the runner icon to refresh the query. I'm assuming there was some VBA behind that action. I'm wondering if there's a non-VBA way to "automate" the query refresh (vs Refresh all), where the user doesn't have to right-click / refresh the query results.
Also, it appeared that your initial demo showed only the query results, but I'm assuming that was because your data table was on another sheet.
Q1 - Query Updates: You could set the properties of the query to automatically refresh when the Excel file is opened, and/or refresh every N number of minutes. Unfortunately, there is no "event" refresh (i.e., detecting a change in the data), that is built-in at the query level. Thus, the need for a VBA macro.
Q2 - Absence of Source Data: Yes; I had the data on a different sheet to simplify the mission overview, and to show how it will likely appear when used in the "real world". The source data is typically hidden from the user, and all they see is the question interface and results.
Thanks for watching!!!!
Clear and concise. Thanks. Very helpful.
Thank you for saying so. We appreciate your time. Cheers!
Impressive, thank you so much for showing a very good alternative as I've been converting as List first then setting them as variables
Yeah, that's the way I was doing it for years. Never again.
Excellent explanation. Thanks.
How can I clear the table from the filters, that is, return it to the state without filters?
Should I create a new parameter?
Thanks! Check out my follow-up video to this one where I address that (and many other) concerns.
th-cam.com/video/xHi8ofM5GSg/w-d-xo.html
@@bcti-bcti Excellent. Exactly what I was looking for. Thanks
Brilliant. my current query with parameter table, then split into five separate queries, then drilled down to the respective parameter. This way from BCTI is much, much simpler. Requires familiarity with referencing table rows, though.
@@davidfamilydoctor9430 Thanks for taking the time to watch. We’re glad it helped.
Keep going bro I subscribed after watching this video. You really surprised me with that magnificent tutorial. Waiting for more vids 😊
@@Hemo2YoYo Thank you for your sub. We really appreciate your support.
For all of you (like me) who are starting out with some of this, here's a tip for us amateurs: Clean & Trim your data as early as possible. I've been trying to replicate this work above for 3 hours and couldn't get it to work - simply because I had an extra space at the end of some of my data. I wouldn't mind but I've had this experience before and not learnt from it! Hopefully, the pain from this will help 🙂.
Regardless, thanks for this it is really valuable.... 👍
ABSOLUTELY. You should always be thinking of solving the problems that will appear TOMORROW, not just the issues of today. Wonderful idea!
lovely ...yes great ...will build it also like this ...have thousand sor Row ...and this sounds great to speed up the changes ...thanks for your effort to tech us ....
Great! Glad you found it useful. Thanks for watching!
This was a really well presented tutorial, many thanks pal 👍🏽
Many thanks to you, too. We appreciate your time.
Super helpful! Is there a way to hide the original data table so the sheet would only show your query parameters and the resulting data?
Sure; the original data can be on another sheet and then that sheet can be hidden. There’s no requirement that the source data be on the same sheet as the query/output sheet. I have them together in the video just for demonstration purposes; to make it easy to cross-check the results to the source.
Thanks for watching.
I didn't know that Date.From (and it's companions) slowed the query down so much, good to know this! Could be a good video idea: things slowing down power query (especially for larger datasets)
Agreed. The topic is on my list. Thanks for watching.
I have used named ranges (cells) for importing parameters into PQ. It is hit and miss for me, as they don't always update with a refresh all. I like the idea of creating a table (final tip) for that. Thanks!
I always appreciate learning about different approaches. How would you code the filter steps when you want a blank to mean return all items that match the other parameters. For example just filter by dates, or Product or (Region and Supplier) (if searching by Region you have to specify a Supplier)?
Thank you for taking the time to watch. Make sure to check out the follow-up video to this one: "Error Checking Parameters". This one will answer your question. th-cam.com/video/xHi8ofM5GSg/w-d-xo.html
Awesome way of teaching! Content is also first class! I would like to ask one question regarding the refresh. We refresh only one query (the output of filtered SalesInfo table) without refreshing the Parameters table after we make changes to parameters. Does it mean that Power Query implicitly refreshes also other queries used in the one that we triggered the refresh on? I am curious as we could simply of course click "refresh all" and then it would be understandable that all the queries get refreshed. I would be thankful for Your guidence on this topic. Many thanks again!
The macro being run in this example refreshes every query in the workbook. I like to put some sort of "run" button for the user since they will likely be unfamiliar with the "Refresh" requirement. I used an icon because I thought it was cute, but I usually place a labeled button that says something like, "Click here to update report".
There are ways to write macros that only refresh specific queries, thus avoiding unnecessary delays in refreshing queries unrelated to this objective (i.e., queries on other sheets for other reports.)
If the report were just for me, I would just use the built-in refresh mechanisms.
Thanks for your kind words and for taking the time to watch the video(s).
Very clear instructions, easy to follow. I noticed you clicked on the running man to perform the refresh, can you please share how you do that also?
The underlying VBA code attached to the “running man” that refreshes the query is in the file download. The like is in the video description. Thanks for watching.
@@bcti-bcti I did download the file but this is what I found
"The macros have been removed as many companies will not allow users to download and/or open files containing macros."
Can you show the code here please?
@@MsMikeful
Sorry. Forgot about that. Here you go:
Public Sub UpdatePowerQueries()
Dim lTest As Long, cn As WorkbookConnection
On Error Resume Next
For Each cn In ThisWorkbook.Connections
lTest = InStr(1, cn.OLEDBConnection.Connection, "Provider=Microsoft.Mashup.OleDb.1")
If Err.Number 0 Then
Err.Clear
Exit For
End If
If lTest > 0 Then cn.Refresh
Next cn
End Sub
Very nice. Another 'single query' approach would be to ingest the three custom slicers with multiple source statements plus a few inline merges & you're done.
@@drewbbc7074 That’s a cool way to do it. I love to see how others solve the same problem. Thanks for watching.
Correction 2 - inline appends + promote headers
That’s the video I’m looking for long time. ❤
Glad to hear. Did you watch the follow-up video to this one? (Parameters with Error Checking)
This is so good!!!! Thanks for sharing it with us 😊
Our pleasure.
interesting topic addressed with clarity and professionalism. a question! I couldn't understand how you associated the query refresh with the icon... With a macro? and then, the Product and Region fields that you take via drop-down fields, how did you create them... with data validation? thanks for your answers.
Both of your assumptions are correct. The refresh was triggered via a macro, and the dropdowns were implemented using Data Validation lists.
Fantastic tutorial on parameters ! thank you!
Can you tell me if you created the created the drop down lists for product region and supplier prior to this ?
I wondered if you could also have a dynamic drop down list from the output table
Example - if you only picked a date rate - then refresh and then (example) the region selection is only from the result of the date output ?
The dropdown lists were created using the Data Validation tool's "list" option. For your other question: I'm sure something could be created to do that secondary filtering, but I'd need to play around with a few methods to see what is the best way to handle that.
@@bcti-bcti Thanks so much for your reply - I look forward to seeing the outcome !
Amazing and the easiset one I have seen thank you
@@SystemsDeptKIOCL Thanks for nice comment. We appreciate you taking the time to watch.
Very nice video. However, what if each condition chooses 2 or more? Product example: select two products Basketballs and Stepper Machines, what is your proposed plan
Yes, this is good for single selections. If multiple selections are needed, a different solution is in order. I'll see what I can come up with.
How about slicers? Apart from selection of columns, slicers would fix all filtering with products etc. dates could be filtered with a Timeline.
Is it possible to call the value to use it in an add
Table function? For example, I neeed to call several parameters to calculate columns with those parameters as drivers
I don't see why not. I'd have to see the specific example to be 100% certain. Give it a shot; you may get what you're searching for. Nothing to lose.
Hi, thank you, very clear, you’ve got a new happy subscriber 😊
Thank YOU! I always like to see the subscriber number go up. Make sure to check out tomorrow's video (Nov 16). It's a follow-up to this one.
@@bcti-bctithank you I am French and I was a little bit anxious not to well understand you but no 👍🏻 you do not speak too quickly, everything is fine 😊
Instead of hardcoding the row you could always use Parameters{[Option = "Product"]}[Selection] Now the only problem is if you change the name of an option in Excel's table xD Or you can have the parameters in single cells (named ranges) and import the names to power query. Mores queries to import but no problem having to select row or column or conversions to date or any other type
Great ideas!!! That's what we love about PQ: there's always more than one way to solve the same problem. You just have to weigh the pros & cons of each strategy and go with what you prefer. Thanks for sharing.
I have a question: Is there a way if we want one of the parameters to include all records on that field? I mean, all other parameters in place for 'all products' for example.. Is it possible that power query understands this when I left this field blank in the parameter table? Or any other way?
Make sure you watch the follow-up video to this one where I introduce error-checking into the process.
th-cam.com/video/xHi8ofM5GSg/w-d-xo.html
This will solve your problem. Thanks for watching.
Excellent tutorial! Thank you 👍🏽
Thank you for the thumbs up.
Thank you for sharing this !!☺
You're very welcome. Make sure to watch the video coming out this Thursday (Nov 16) as it is a follow-up to this one.
Very informative. good effort. can we get this working file for practice?
The download link has been posted in the video description. Enjoy!
@@bcti-bcti Thanks
Thanks! It is the one I’m looking for!
Thanks for watching. Make sure to watch this week's video (releasing on Nov 16th, Thursday). It's a follow-up video to this one. More good stuff.
I appreciate this so much it hurts. thank you.
Thank YOU for taking the time to watch and comment!!!
Excellent video, Thanks
Thank you so much for watching. Be sure to watch the next video (Thursday, Nov 16) as it is a follow-up to this one.
Excellent !! 🏆
Thank you. Be sure to watch the follow-up video to this one being released Thursday (Nov 16). You'll love it.
I can't wait to watch it. Thank you already
exactly what I'm looking for
Glad to hear we helped. Thanks for watching.
Great exapmle!! Thanks.
Thanks for watching!!!
really good video, thanks a lot!
Thank you for watching!!!
Very good content 👍
Thank you for taking the time to say so. Cheers!
Thank you very much !
Our pleasure. Thanks for watching.
Does it use wildcard if you dont want to filter by a certain field. The video assumes you are always using 5 filters. What if I only want to use 3 or 4?
Unfortunately, Power Query lacks support for wildcards.
@@bcti-bcti thanks for responding. So will it work if i leave one of the parameter fields blank?
Not in this example, but if you watch the follow-up video to this one ("Parameters with Error Checking"), you will see how to deal with empty selections.
How is this setup for multiple selections in the parameter table?
Picking a SINGLE selection is too simplistic for my real world needs.
You are correct. This would be useful for single selections. I think a followup video showing multiple selections is needed. I'll see if I can make one up. Thanks for the suggestion.
One question, what should be the solution when you have multiple items in just one parameter?
Yeah, this solution is only good for 1 selection in each field. I’d have to work something up to see how to deal with multiple selections per field.
What if I don't want to fill all the fields. If I leave a field blank it wontcome so how to overcome that.
Thanks for the video.
Did you watch the follow-up video to this one? (Parameters with Error Checking). This video directly addresses your question. Thanks for watching.
Great with one comment.
What if one of the parameters is empty?
Query will break. I think you should use if clause in M-code if the parameter is null.
I hope you got the point.
You are EXACTLY CORRECT! Tomorrow's video (Nov 16) is a follow-up to this one that addressed that very issue. Thanks for watching.
@@bcti-bctiThank you. Kindly notify me when you post the modification👍🏼
which video shows how you created dependants for the table as that was the last section of the video
@@ewilner there is no specific video for that part. All you need to do is, in the second table (green table), just enter an equals sign, then click on the cell in the blue tables you wish to carry over to the green table. Download the sample file to examine it in more detail.
Very useful, really thanks a lot
Our pleasure. Thanks for watching. Check out the upcoming follow-up video to this being release Thursday (Nov 16).
Marvelous 🎉
Thank you. And thanks for watching.
PROBLEMO! When you zoom in we can't see where you click on the side bar, so we lose track of what's pressed/selected.
Thanks for pointing that out. I try to make sure that I don't do that, but that one slipped by me. Thanks for keeping me on my toes.
One question if I want to filter more than one region at once
Yeah, this solution is only good for 1 selection in each field. I’d have to work something up to see how to deal with multiple selections per field. Thanks for watching.
Awesome! Thanks
Glad you liked it. We appreciate your support.
Excellent sir
Thank you for taking the time to say so. We appreciate it.
learning video for me
Glad to hear. Thanks for watching.
cool tips, thanks
Thank YOU!
Brilliant!
Thanks!
Excellent VideoE
Thank you! That's very nice of you to say. I appreciate you taking the time to watch.
How about merge queries with selection table ,Sir ? if the data is merge queries from website.
@@DauTuDaiHan999 Are you requesting a video on merging query data from websites?
@@bcti-bcti I scrape website with type (1...3 ) to collect data, the data finance have 3 statement. So I create 3 statement with source website different type, and then merge 3 query that finished statement. But I want to ID table parameter. How should i do
@@bcti-bcti the result 183 column is correct not 110 col. it miss 2 source website
@@DauTuDaiHan999 I think this is something I would need to work on first-hand to explore the nuances of the needs and issues. Without access to the data and a full understanding of the goal, it's difficult to advise properly.
Very cool 😁
Thanks
Nice video, thanks
Thank you. This Thursday (Nov 16th) is a follow-up video to that one. You won't want to miss it.
ty vry much.
Im going to see it
@@bcti-bcti
Wonderful
Thank you for taking the time to watch and comment. 👍
Brilliant!
How can i call this parameter to the grid ? Ie =ParameterTable[SettingName]{0} I can only call the whole table. This video is amazing
I may not understand your question as intended, but if you want only a single row from the "Parameters" table, you'd have to highlight only the needed row and bring it in as a range.
Thank you for your nice compliment.
@@bcti-bcti yes that would make more sense to make a single named range. Many thanks
thank you
You are very welcome. Thanks for watching.
This filter preemption,will be a nightmare when you have say over a handful of columns.
The amount of filter steps that you need to setup initially may take an enormous amount of unnecessary time, don't you think?
I would say that this serves a purpose. When the scenario changes, so shall the solution.
@@bcti-bcti yeah I agree that this serves a purpose when there are only a handful of columns as we are prempting which column the filters will apply. Have you another effective approach that can apply to the preemption of say 50 columns which could potentially have needs of filtering please?
I would need to see the data file to come up with a strategy. Do you have one for examination? @@patrickkinbonso1809
subscribed
Thank you so very much. We really appreciate your time.
I love you
"Flattery will get you everywhere" - Lynn Anderson (1969)
You can go a step faster, put your green parameter table as one row from start, no need to transpose in PQ
@@JoBédard-x3o I agree. It all comes down to the number of parameters and the layout aesthetic. Thanks for watching.