Power Query - Faster & Easier Parameters

Create robust, easy-to-use, and SUPER FAST Parameter tables for more dynamic reporting.
File Download Link:
www.bcti.com//wp-content/YT_D...
00:41 Common Method for Collecting Parameters
06:10 Robust Parameters with Mixed Data Types
11:44 Custom Interface Sent to Parameters

[@ariarc]

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.

[@bcti-bcti]

Thank you SOOOO much for the kind words!!! You have absolutely made my day (maybe even my week.)

[@elipomerantz6980]

I second that

[@carmelinechemmanoor5394]

This is so good!!!! Thanks for sharing it with us 😊

[@bcti-bcti]

Our pleasure.

[@balrajvirdee1087]

This was a really well presented tutorial, many thanks pal 👍🏽

[@bcti-bcti]

Many thanks to you, too. We appreciate your time.

[@markhenderson3771]

Clear and concise. Thanks. Very helpful.

[@bcti-bcti]

Thank you for saying so. We appreciate your time. Cheers!

[@ThePattersonPod]

Just got a subscriber! I’ve never edited in the code bar to have it reference something dynamic like that. Really fun to learn!

[@bcti-bcti]

Thanks. Glad you enjoyed it. We appreciate your time.

[@user-kh1sp4lq5h]

This deserves a lot more like and subs. Excellent tutorial and examples.

[@bcti-bcti]

Thank you. We appreciate the thoughtful comment.

[@ErickTun]

Excellent tutorial! Thank you 👍🏽

[@bcti-bcti]

Thank you for the thumbs up.

[@alicemorili1330]

That’s the video I’m looking for long time. ❤

[@bcti-bcti]

Glad to hear. Did you watch the follow-up video to this one? (Parameters with Error Checking)

[@ChiMickE]

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)

[@bcti-bcti]

Agreed. The topic is on my list. Thanks for watching.

[@dougmphilly]

subscribed because this deserves a nobel prize. i have so many uses for from this.

[@bcti-bcti]

So glad to hear. I'm glad it has helped you.

[@MaydayAggro]

You just created so much work for this perfectionist Excel user.

[@bcti-bcti]

I hope that’s a good thing. Thanks for watching.

[@vijayarjunwadkar]

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!

[@bcti-bcti]

Thank you! We're still in the infancy of KZbin videos, but we're hoping to grow big and strong.

[@willbedeadsoon]

This channel deserves more views. Good job!

[@bcti-bcti]

Thank you for saying so. We're new, so it will take time for viewers to discover us. But we completely agree :)

[@bcti-bcti]

I have a follow-up video releasing Thursday to this one. Don't miss it; it will help tremendously.

[@pascaljoly5752]

really good video, thanks a lot!

[@bcti-bcti]

Thank you for watching!!!

[@alpeshshah891]

Impressive, thank you so much for showing a very good alternative as I've been converting as List first then setting them as variables

[@bcti-bcti]

Yeah, that's the way I was doing it for years. Never again.

[@Swizzletwice]

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.

[@bcti-bcti]

Thank you so much for the kind words. I’m glad it helped. Thanks for taking time to watch and comment. 👍🏻

[@fabianmolyneux2308]

Thank you. This is excellent presentation. You make it easy to follow along.

[@bcti-bcti]

Thank you so much for saying so. Make sure you check out the follow-up video to this one titled "Error Checking Parameters".

[@vwij1736]

Great video. The ideas can used in an interactive dynamic way in varied contexts with the illustrated PARAMETER approach. Thank You.

[@bcti-bcti]

Thank you for taking the time to watch. Make sure to check out the follow-up video to this one: "Error Checking Parameters".

[@vwij1736]

Thanks for letting me know. @@bcti-bcti

[@kebincui]

Excellent Video. Cristally clear and elegantly presented. Looking forward to more advanced power query videos like this. Thanks for sharing 🌹👍

[@bcti-bcti]

Thank you for watching and taking the time to say such nice things. You've made our day!

[@LosoIAm]

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.

[@bcti-bcti]

Thanks for such a nice (and creative) comment! We appreciate you taking the time to watch.

[@naveenpragash7584]

Thank you for sharing this !!☺

[@bcti-bcti]

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.

[@rpopecpa]

Great exapmle!! Thanks.

[@bcti-bcti]

Thanks for watching!!!

[@duydiep3909]

Thanks! It is the one I’m looking for!

[@bcti-bcti]

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.

[@robbe58]

Magnificent video and explanation. I personally will change my method of filtering Data tables to this one instead of using slicers. Top, top, top!!

[@bcti-bcti]

Don't give up completely on Slicers; they're still wonderful. Thanks for watching.

[@StefanoVerugi]

outstanding tutorial, clear, concise and yet thorough, I am very appreciative of your work, thanks

[@bcti-bcti]

Thank you. We appreciate your time.

[@jazzista1967]

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.

[@bcti-bcti]

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. kzbin.info/www/bejne/rnnMaaKcgpp6ick

[@jazzista1967]

@@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!

[@jazzista1967]

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

[@bcti-bcti]

@@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.)

[@jazzista1967]

@@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...

[@girishpadshala9689]

Excellent video, Thanks

[@bcti-bcti]

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.

[@zahoorsarbandi2982]

really very informative lecture and helpful in daily work. Explanation is remarkable. Thanks

[@bcti-bcti]

Thank you for saying so. Much appreciated.

[@cathtravel7034]

Hi, thank you, very clear, you’ve got a new happy subscriber 😊

[@bcti-bcti]

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.

[@cathtravel7034]

@@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 😊

[@gloriawong1698]

Very useful, really thanks a lot

[@bcti-bcti]

Our pleasure. Thanks for watching. Check out the upcoming follow-up video to this being release Thursday (Nov 16).

[@bojanjovanovic756]

Thank you very much !

[@bcti-bcti]

Our pleasure. Thanks for watching.

[@colinchiu7113]

Brilliant!

[@RajaMaja-zk3dz]

exactly what I'm looking for

[@bcti-bcti]

Glad to hear we helped. Thanks for watching.

[@nicor1501]

Excellent !! 🏆

[@bcti-bcti]

Thank you. Be sure to watch the follow-up video to this one being released Thursday (Nov 16). You'll love it.

[@nicor1501]

I can't wait to watch it. Thank you already

[@SamehRSameh]

Marvelous 🎉

[@bcti-bcti]

Thank you. And thanks for watching.

[@martyc5674]

Very good content 👍

[@bcti-bcti]

Thank you for taking the time to say so. Cheers!

[@ivanbork4175]

cool tips, thanks

[@bcti-bcti]

Thank YOU!

[@Paladin101]

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.... 👍

[@bcti-bcti]

ABSOLUTELY. You should always be thinking of solving the problems that will appear TOMORROW, not just the issues of today. Wonderful idea!

[@descubriendopowerbi7899]

Nice video, thanks

[@bcti-bcti]

Thank you. This Thursday (Nov 16th) is a follow-up video to that one. You won't want to miss it.

[@descubriendopowerbi7899]

ty vry much. Im going to see it @@bcti-bcti

[@viviennesmithdorf3244]

Very cool 😁

[@bcti-bcti]

Thanks

[@DanKnight]

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.

[@bcti-bcti]

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!!!!

[@rajeshmajumdar4999]

Wonderful

[@bcti-bcti]

Thank you for taking the time to watch and comment. 👍

[@chakralamurali]

Excellent sir

[@bcti-bcti]

Thank you for taking the time to say so. We appreciate it.

[@carlocalingasan998]

thank you

[@bcti-bcti]

You are very welcome. Thanks for watching.

[@rubenmunozverdu7528]

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

[@bcti-bcti]

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.

[@MsMikeful]

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?

[@bcti-bcti]

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.

[@MsMikeful]

@@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?

[@bcti-bcti]

@@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

[@luigisir6893]

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.

[@bcti-bcti]

Both of your assumptions are correct. The refresh was triggered via a macro, and the dropdowns were implemented using Data Validation lists.

[@maciejkopczynski55]

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!

[@bcti-bcti]

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).

[@tonybarker4240]

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 ?

[@bcti-bcti]

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.

[@tonybarker4240]

@@bcti-bcti Thanks so much for your reply - I look forward to seeing the outcome !

[@Alexis-xq7gq]

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?

[@bcti-bcti]

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.

[@FRANKWHITE1996]

subscribed

[@bcti-bcti]

Thank you so very much. We really appreciate your time.

[@McIlravyInc]

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)?

[@bcti-bcti]

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. kzbin.info/www/bejne/rnnMaaKcgpp6ick

[@letuananhkhoi7439]

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

[@bcti-bcti]

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.

[@dagfinnsjvik5195]

How about slicers? Apart from selection of columns, slicers would fix all filtering with products etc. dates could be filtered with a Timeline.

[@talha946]

I love you

[@bcti-bcti]

"Flattery will get you everywhere" - Lynn Anderson (1969)

[@ExcelIron]

Very informative. good effort. can we get this working file for practice?

[@bcti-bcti]

The download link has been posted in the video description. Enjoy!

[@ExcelIron]

@@bcti-bcti Thanks

[@pratik0013]

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.

[@bcti-bcti]

Did you watch the follow-up video to this one? (Parameters with Error Checking). This video directly addresses your question. Thanks for watching.

[@JonathanRodriguez-mf5cv]

One question, what should be the solution when you have multiple items in just one parameter?

[@bcti-bcti]

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.

[@ankitsaxena5966]

One question if I want to filter more than one region at once

[@bcti-bcti]

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.

[@abdullahquhtani8058]

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.

[@bcti-bcti]

You are EXACTLY CORRECT! Tomorrow's video (Nov 16) is a follow-up to this one that addressed that very issue. Thanks for watching.

[@abdullahquhtani4247]

@@bcti-bctiThank you. Kindly notify me when you post the modification👍🏼

[@spreadsheetdoctor]

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?

[@bcti-bcti]

Unfortunately, Power Query lacks support for wildcards.

[@spreadsheetdoctor]

@@bcti-bcti thanks for responding. So will it work if i leave one of the parameter fields blank?

[@bcti-bcti]

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.

[@PippyJim]

How is this setup for multiple selections in the parameter table? Picking a SINGLE selection is too simplistic for my real world needs.

[@bcti-bcti]

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.

[@jusuflazami9580]

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.

[@bcti-bcti]

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.

[@barryc7443]

How can i call this parameter to the grid ? Ie =ParameterTable[SettingName]{0} I can only call the whole table. This video is amazing

[@bcti-bcti]

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.

[@bcti-bcti]

Thank you for your nice compliment.

[@barryc7443]

@@bcti-bcti yes that would make more sense to make a single named range. Many thanks

[@patrickkinbonso1809]

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]

I would say that this serves a purpose. When the scenario changes, so shall the solution.

[@patrickkinbonso1809]

@@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?

[@bcti-bcti]

I would need to see the data file to come up with a strategy. Do you have one for examination? @@patrickkinbonso1809

[@ericrobbins6953]

Brilliant!

[@bcti-bcti]

Thanks!