Power Query Parameters - Dynamically Change the Output of Your Queries Depending on Their Value
ฝัง
- เผยแพร่เมื่อ 25 มิ.ย. 2024
- Power Query Parameters give you the flexibility to dynamically change the output of your queries depending on their value. As Power Query records the transformation steps, it includes many hardcoded values within the M code.
What if we want to change these values? We could edit the query each time, which would be very time-consuming. Or we could create parameters!
Parameters in Power Query are really easy. In this video you will learn how to create a Parameter in Power Query as well as create a parameter in an Excel Cell.
You can download the practice example here: buymeacoffee.com/missmicrosof...
00:00 Introduction to Parameters
01:15 Creating the Query
02:31 Creating the Parameter
03:43 Assigning the Parameter to the Query
05:42 Creating More than One Parameter
07:43 Creating a Power Query Parameter in an Excel Cell
✅ Please see link to video mentioned in this video:
🎦 Power Query Advanced Editor: • Getting Started with t...
🎦 Create a Dynamic File Path - Change the Power Query Source Based on a Cell Value: • Create a Dynamic File ...
🎦 Power Query Custom Functions: • Power Query Custom Fun...
✅ If you would like to subscribe to The Query Editor Newsletter for more Power Query content:
🗞️ thequeryeditor.beehiiv.com/
✅ If you would like to connect on LinkedIn:
🤝 www.linkedin.com/in/missmicrosoft
✅ If you would like to buy me a coffee, so I can get my caffeine in to keep creating as many videos as possible for you :). It's really quick and easy, please use this link:
☕www.buymeacoffee.com/missmicr...
✅ For more videos please subscribe:
🔔 / @missmicrosoft
✅ For more Power Query videos please use the links below:
🎦 Power Query in Excel Beginners Tutorial: Combine Multiple Excel Sheets, Automate & Transform Data: • Excel's Power Query: T...
🎦 Power Query Age Analysis - Create an Excel Age Analysis Report Using Power Query: • Power Query Age Analys...
🎦 Power Query Error Handling - How to Deal with Power Query Errors Part 1: • Power Query Error Hand...
✅ Please also check out my Excel course on Udemy, please use this link:
📖 www.udemy.com/course/excel-fo...
Be among the first 100 to jump on board "The Query Editor" newsletter 🚀. It's packed with super-actionable tips and insights. Why should you be excited? 🌟 Because the first 100 subscribers will receive an exclusive 50% discount on my upcoming mini-course, set to drop early next year (or perhaps even sooner)! 🤞Just hit this link to subscribe thequeryeditor.beehiiv.com/subscribe
🚀🔥
Excellent! Thank you. You always explain things clearly.
You’re most welcome Ian, glad I could help!
Thanks for creating this vedio and adding multiple scenarios into it. This one vedio cover parameter well. Appreciate your effort.
Thanks, that was a great video, really helpfull
Very nice! Well explained and useful information. Thanks for demonstrating. Thumbs up!!
Hi Wayne, thank you so much!!! And I took your suggestion and added the workbook in the description :)
@@MissMicrosoft Great! Always helps to be able to follow along and practice :)) Thanks!!
Your channel is underrated! Good job. 👏 I shall recommend your channel in my network!
Thank you so much for the support Charlie Chaplin! It’s highly appreciated :)
Excellent, there's nothing more to say. Thank you for that professional tutorial. Greetings from Bavaria.
Thank you very much! Glad you liked it.
thank you for video
👍👍👍👍
Excellent 👍🏻 thanks
Thank you Kebin :) you’re most welcome
Medam, Nice Teaching.. Clear speech.. 👍🏻👍🏻
Thank you Hiren :)
Really useful. I haven’t used parameters before.
For the product table, instead of pulling in a table you could pull in a named range to power query.
Glad the video was helpful. Yes you could definitely use a named range instead.
Great! Thanx!
You’re most welcome!
Great! Thank you
You are welcome!
Hello Miss 🙂nicely expained...thanks a lot...keep uploading such videos...love from India
Thank you Sudipta for your kind words! I'm so glad the videos are helpful! If you haven't yet subscribed please subscribe as it really helps the channel grow, thank you so much for your support!
Awesome thank you
It's a pleasure Faisal, glad it was helpful :)
Great video / just subscribed!
Glad you enjoyed the video Greg. Thanks for subscribing!
Excuse me, one question! But if instead of the month we had taken the day as a dynamic variable, would it have worked the same? Or does this dynamic filter only apply to text variables? Thank you for the great video and your answer!
Thanks for sharing ❤
You’re welcome! 😊
Thank you so much. 7:45 saved me from dying in an assignment 😭
I’m so glad I could help!!!
how to query more than one value dynamically? for example I want jan and feb data to appear
What if I want to see two months data....
Does this work in PowerBI?
cant seem to do this with date values any tips?
How to use create parameter in version 2016
Can this be a multiple selection?
Yes it can, please see 05:44 of this video for the second parameter created in the same query, so you can follow the same method to create as many parameters as you require in the same query.
Hi 👋..actually, very nice but could you distribute ur video into 2 or 3 videos with shortage muints and each video explain one point in order to understand for immediate levels 😀
Thanks 😊
Hi 👋 thanks for the suggestion 😊
i want this concept from sql server. plz explain
Can I use the dynamic parameter for 6 different sheets in the same workbook? I am building a workbook with 6 tables and I want to do the dynamic parameter in another sheet and set it to connection only.
Yes you can most definitely do that.
@@MissMicrosoft Thanks. I was able to do it. I thought it was not possible at first because the filter was not applying to all sheets. The problem was in the data type configuration of some sheets .
@@edwinruizcolon5707 Glad to hear you were able to do it :) thanks for letting me know!
@@MissMicrosoft I am having the following issue. Not related to original question but hopefully you can help me. I developed an excel file with power query reading files from a folder on a shared folder of my organization. I, creator of the file, can refresh the file and query steps run without issue. However others members of my team cannot do it. They see the following error
“Query references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.”
@@edwinruizcolon5707 this can definitely be fixed, you can either ignore the error or fix it, fixing it is definitely the safer option, please watch this video here for the fix: th-cam.com/video/SLn5sAL3WnQ/w-d-xo.html but if you want to ignore the error, please check out this video here: th-cam.com/video/T5aWvqf9SnM/w-d-xo.html. I hope it helps!
10/10 on choosing timestamps of referrals. Liked. Subscribed. What's your twitter handle?
Thank you for the support, it’s much appreciated! I’m not yet on Twitter, I’ll share it when I get an account.
@@MissMicrosoft I have a small power query assignment to outsource and you seem a potential candidate. Please DM or connect.
Hi Danish, let's chat, please email me at missmicrosoft@ignite-work.com