Create A Parameter Table For Your Power Queries

Learn how to create a parameter in power query either using the native parameter functionality or by creating a table of parameters in your worksheet. 👇

Check out my full courses and ebooks here

DOWNLOAD the example workbook here

SUBSCRIBE & get my 3 FREE eBooks

CONNECT with me on social

Thanks for all your support!

[HowToExcelBlog]

Check out my full courses and ebooks here

[Crashdownfully]

Exactly what I was looking for. Thank you so much!

[Ryan-zzhh]

Bless you, sir. This is a good video! I knew there must be a better way to change data sources in PowerQuery... this will help immensely.

[SonJboy]

Thank you! Finally! I've been trying to search this for 3 hours now. This really helped.

[kirkyit]

Thanks for such a great demonstration. Very easy to follow and understand, and I was easily able to adapt to my own specifc need.

[GeertDelmulle]

I guess that’s one way of doing this.
I prefer reading the parameters directly from Excel into the appropriate step. No need for additional queries.
Currently I use this trick to read a folder path and I added another parameter to be used as a keyword to only include those files where their file names include said keyword.
Super easy and IMO way simpler that your solution.
Regardless, PQ rocks!

[paulmartinlife]

Very useful and basic, just what I was looking for. Thank you.

[kageri]

Thank you so much! Exactly what I'm looking for.

[GreenTrogo]

Thanks! This is exactly what I was looking for!!

[graemegourlay]

Great tutorial. Worked perfectly using a parameter to generate a string for a JSON API call. Used a data validation list into the parameter table and a macro in button to refresh the data. From a user perspective means picking a value from a list and clicking a refresh button. Now...to figure out if I can to the equivalent of this in Power BI.

[dobrinyanicitich]

Thank you for your work, very helpful

[Quint]

I kinda get the same sort of error as LK Lai, just slightly different:


An error occurred in the ‘fParameter’ query. Expression.Error: The key didn't match any rows in the table.
Details:
Key=
Parameter=Folder
Table=[Table]


I did look for exact matches including case and tried it a few times from scratch but keep getting this error at the last step. Any idea what might cause it?

[renatovianello]

Good video John! I am one of your faithful followers from Italy. Unfortunately the link to download the example workbook doesn't work. I subscribed many times, but no download followed.

[kimbilton]

Hi John
Nice video. I switched the code around because I was using get folders. I worked great.

My question is If am using folders in one query and single files in another query.

How does that work with the table or does this only work once and I would have to have another table for a different query?

[jerrydellasala]

I've used parameters in cells and tables before, but I love this technique! BTW, the download page isn't working. Keep up the good work!

[salleymartinez]

Hi there! What if we have a file that we need to Connect to only and not load to?

[enricomendiola]

Hello Sir. Great Video tutorial. This will solve my problem. Is it possible Sir to create a video like this but with an additional parameter which allows you to put a start date and end date? Thank you

[zequnchen]

Thanks a lot for sharing. I did the same steps but got an error "We cannot convert a value of type Table to type Function.
".. any idea about why?

[DhasseLOL]

I have my post api query up and running successfully thanks to your other video, now I just need to add parameters which brought me here! :)
I need my parameter to be inside the Content of the API call. E.g. The parameter is locations={{1, 1}, {2, 2}}. I have tried having it all as the parameter as written here, but it doesn't work. I also tried only having the list {{1, 1}, {2, 2}} as a parameter, writing ([locations=parameter]) as the function argument. It did not work either. What's wrong?

[lklai]

I was hit by this error:


An error occurred in the ‘fParameter’ query. Expression.Error: The key didn't match any rows in the table.
Details:
Key=Record
Table=Table


How do I resolve it?