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!
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.
You could use a power app visual to input a value, then refresh via power automate called from power app. But since you only have 8 refreshes, you're better to pull all data into Power BI, then filter it via slicer.
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?
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?
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.
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
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?
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. Content=Json.FromValue([parameter]). 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?
Great video.... I have a simple question: what if I have multiple files that are created and come to me on semi monthly that I need to use power querry to merge into one table. If the reports are similar month to month but they named differently (i.e. 1ReportJuly2020 vs. 1ReportAug2020), can I use this process? Thank you very much !
I am stucked in here. My source is folder. And I need to make exactly the same what is in video. If it is possible. Can You explain how it mus to be set up when the source is folder?
@@HowToExcelBlog Have no idea, when I am trying to do this trick woth folder path I got an error. Hawe no idea what I could do more, already tried all. :(
Been a while since I made the video, but I probably meant I named the table ParameterTable. You can name any table in the table tools tab that appears when a table is selected.
How To Excel what if it is published ? I’m looking a way for connecting a field in the dash board with the query something like a variable that can be used either in DAX or Lenguage M environment
![[UNCUT]"ทักษิณ" โชว์ฤทธิ์-ครอบครอง "อุ๊งอิ๊ง" ประเทศไทยมีนายกฯ 1แถม1 I คนดังนั่งเคลียร์ I 23 ส.ค.67](http://i.ytimg.com/vi/Uyl4y59R-Sk/mqdefault.jpg)
Check out my full courses and ebooks here
👉 www.howtoexcel.org/courses/
Says coming soon
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!
Hello Geert! How do you do it directly from Excel? Thanks
I think he's referring to the power query editor, home tab, manage parameters.
The down side is you have to open the editor to update parameters.
Gents,
I meant it literally the way I wrote it.
Watch this video right here on how it's done:
ruclips.net/video/0NX-GctfZuU/видео.html
You're welcome.
@@GeertDelmulle Agree that's easier.
It's useful to have an alternative, though I prefer the solution here. Thanks Geert for the pointer to an alternative...
Thanks for such a great demonstration. Very easy to follow and understand, and I was easily able to adapt to my own specifc need.
Thank you! Finally! I've been trying to search this for 3 hours now. This really helped.
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.
Exactly what I was looking for. Thank you so much!
Very useful and basic, just what I was looking for. Thank you.
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.
You could use a power app visual to input a value, then refresh via power automate called from power app.
But since you only have 8 refreshes, you're better to pull all data into Power BI, then filter it via slicer.
Thank you so much! Exactly what I'm looking for.
Thanks! This is exactly what I was looking for!!
Exactly what I wanted, thank you !
Great to hear!
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?
Very very helpful. Thanks a lot.
Very clear tutos. Many thanks!
You're welcome Philippe!
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?
No, you can use the parameters in the table across any of the queries in your workbook.
Thank you for your work, very helpful
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.
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
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!
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?
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?
Sounds like you have a spelling error between the function input and what's in the table. These need to be exact matches including case.
I had the same issue. Reason was the empty space in the column name. Try to use: #"Column Name"; value = Source{[#"Parameter "=ParameterLable]}[Value]
Or avoid spaces in your column names 👍
i have the same error and cant find why is it
Thanks a lot
Bless you
Thanks Breyonna!
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. Content=Json.FromValue([parameter]). 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?
Hi there! What if we have a file that we need to Connect to only and not load to?
Great video.... I have a simple question: what if I have multiple files that are created and come to me on semi monthly that I need to use power querry to merge into one table. If the reports are similar month to month but they named differently (i.e. 1ReportJuly2020 vs. 1ReportAug2020), can I use this process? Thank you very much !
Are your files in a folder? Can combine them using the folder query.
@@HowToExcelBlog Giid Afternoon...Yes files will be placed in folder each time reports are created. Will this process work? Thank you again!!
It's the first tip here ruclips.net/video/Hj4PVFYhqhQ/видео.html
nice idea!
Thanks Steven!
Sources are very easy to update. I came here to find out what parameters are used for but found nothing.
Use them for anything you want to change in the query.
Can we download this workbook?
I am stucked in here. My source is folder. And I need to make exactly the same what is in video. If it is possible. Can You explain how it mus to be set up when the source is folder?
Just set up a parameter in your table for the folder path. Then replace the source step with this parameter.
Where did you go wrong?
@@HowToExcelBlog Have no idea, when I am trying to do this trick woth folder path I got an error. Hawe no idea what I could do more, already tried all. :(
All good. Find that, I did not name parameter table. So good lesson. Thanks!
Good to hear you sorted it out!
What do you mean when you say ‘I have labelled this as parameter lable’
Been a while since I made the video, but I probably meant I named the table ParameterTable. You can name any table in the table tools tab that appears when a table is selected.
useful!
Wow
Thanks!
Please i cant download the file from example
How this could be done in power BI?
You could use the enter data feature to create a table.
How To Excel what if it is published ? I’m looking a way for connecting a field in the dash board with the query something like a variable that can be used either in DAX or Lenguage M environment
Use enter data, then show in a slicer on the report, then use SELECTEDVALUE DAX function.
👍
How about you 'prepare' all of this and just upload the picture of power query logo instead. Lol.