it´s not just that your solutions are great, the way your videos are presented is fantastic!

You are a legend! Used the same principle on a web query to pass multiple stations to a string. Love it!

To avoid the Native Query Edit permissions just go to the Security tab in Options and deselect 'Require user approval for new native database queries' - BAM.

4 years later and you are still helping me!

I just started with Power BI and this helped me with a solution. Thank you very much. I did notice that parameters now have an option for Query in addition to a list or a value. But I couldn't figure out how to use it. Your solution worked great for me.

Being new to Power BI, had been looking for a solution for exactly same problem for days and i stumbled upon this video and solved it within few minutes! Thank you so much!!

Very nice vid! As you said, you can use for so many purposes: Fill in some URLs in your Excel workbook & you have a web-scraper: Create a query that takes a URL as a parameter and turn that into the function that you call instead.

Patrick.......I just feel like giving you a hug. This was fantastic

Patrick, this was wonderful! These explanations are very well thought out and contextual to common issues I personally run into often with Power BI.

First time watching your video and paused the video to write this comment before I finish the rest of the video. LOVE your presentation style. I can feel the same frustration sometimes when stakeholders ask random challenging stuff but it's fun and I always accept it. Kudos to your hard work and especially the way you present it. Hollywood next stop maybe?

Inadvertently stumbled upon how to remove prefix for column headers. Was wondering about that and had to remove them in M-code the slow painful way for a dozen queries. So happy to see how to resolve this here. Had to slowdown the speed, back up and pause many times to make sure I didn't miss anything. As a constructive feedback, it's kinda distracting and makes it hard to watch when the video switches rapidly snapping us out of the computer screen and watching Patrick's expressions explaining something although it makes it more interesting and less monotonous. It's like a sudden jolt, not complaining. Nevertheless learn a lot from this invaluable training which I have bookmarked for future reference.

Finally.. a clear usage of parameter and function.

I've used like 10 of your videos to combine all of this magic into one solution. I've subscribed. I have an API URL that shows a monthly agreement details for all of my clients. To get more details of what is covered under that agreement, I needed to enter in the agreement # into the API URL. "finance/agreements/agreementID/whatscovered" I want it so that the report has two tables, the agreements and when you select the agreement ID, the bottom table shows those details. Basically, clicking the top table column ID will set a parameter and re-run a query. I can't figure out how to do that, but this method allowed me to gather those details right now and create a table with them so that they can be referenced later.

Nice video Patrick! It is not a solution to the problem what i'm searching, but takes me to a mind blown about how can i create good reports.

Exactly what I needed. Thanks for growing my knowledge with a smile!

You are awesome man! I actually understood this first time through, unlike the 1 on 1 tutorial I had at work.

Some of this is above my head but I'm learning a lot and enjoying your videos. Thanks man!

This was a phenomenal tutorial Patrick. Everything you said was clear and easy to follow. I'm currently working on a POC at work where we want to retrieve data using a REST API, which takes in 5 parameters. The users will need to be able to modify the report based on the parameters. I was able to modify your example to use a REST API and I created an excel to store different combinations of parameters. I followed along and was able to invoke the API for different combinations of parameters.

This was awesome, and I was able to pass two parameters in the same table. Thank you!

Brilliant, this was the only comprehensible explanation of parameters I could find! Thank you!

Patrick!!! You are about to take me to school! I love the way you present. This video was very helpful. Thank you!

Thank you so much! This video just solved a problem I was having trying to filter down rows of data. I didn't realize I could just use a sql statement. Awesome!

Its first time to use parameters in Power BI your video really helped to understand the basic thanks.

I wish all tutorial videos just like "guys in a cube". Thanks !!!

Legend, this revolves some of the issues with not being able to use dynamic M parameters / slicer input with queries (in import mode) as you can try to pre-fetch the data using the function and give the user the "slicer" experience too (in a Rest API scenario, where the API is very restrictive in terms of data slice)

Great video! I did something like this importing the parameter excel table, and establishing a relationship with the main table that limits the report. Probably not the most efficient way as relationships add run time. Did not use parameters. What I like about this video is how it casually introduces ways to use parameters and custom functions. Thanks!

Really good videos guys. Keep up the great work, this stuff is really useful for people. One suggested improvement would be to keep your student list in an Excel Table (Ctrl + t), to avoid accidents if someone inserts a row or column in that Excel file.

You're born for it! Brilliant way to present the solutions, as well!

I'm looking forward to seeing a video on parameters.

Patrick! you should have your own comedy show. You are really funny. I think I like your comedy more than the content, but don't get me wrong the content is really good too.

Patrick...you're awesome man!!! What a clever solution.

Absolutely love your presentation style. Really informative content and very entertaining too. Cheers.

It´s my first time visiting this channel, it´s a ingenius solution to your problem, also the way the video is presented is very fun! Congrats. Have you ever try to make dynamic the report, reciving parameters for the report URL?

Oh and by the way Patrick the videos are great and just the right speed

Great work Patrick! You’re very clear on your explanations and I enjoyed seeing your videos! You have a new subscriber...

You are a good teacher!

Great example. Used this basis to connect to an API with a changing variable. Thanks for the help.

Unbelievable !!! Thank you very much, this is very much helping

This is really Awesome Patrick. Kudos to your guys.

Great video, thanks. I've been wanting to know how to do this for ages.

Love the Triumph dog impression at 2:33, Patrick :-)

i loved the way you present and make the things very easy. great

Nicely done Patrick!

Question: Can PowerBI be embedded into a Unity 3d application? And/Or can a Unity 3d application be embedded into a PowerBI tile/report? Sorry the question was a little off topic. I'm just having trouble finding out if this is possible and you guys are are PowerBI gurus. By the way, love the videos. Very clear, concise, and engaging.

Great stuff!! This is exactly what I was looking for the other day!

Loved the form and the function, thanks Patrick!

Hi Patrick. The video was good. Can you also show how report columns can be dynamically filled as per dynamic query in parameter? say sometimes 5 column & sometimes 10 columns etc. depending on dynamic query. Thanks in advance.

Great stuff, Patrick!

Loved the video! Is there an advantage to doing this using a stored proc instead of an inner join with the SQL table to the excel file.Also, I noticed your connection imports VS live query. Can you explain why (I am a powerBI newbie)THX

Patrick, Awesome job! You're exceptional at it. Keep sharing.

Great video on calling stored procedure from Power BI. Thank you very much.

Awesome video and clever simple solution!

Awesome Video!. Thanks. Could you pass those parameters from a textbox or dropdown button and call the storedprocedure from a button?

You have just blown my mind, I mean the potential of this tool, I just can't stop thinking about the posibilities thanks a lot!!!!

This was an amazing tutorial! Love the delivery! You got yourself a new fan!

Hey @patrick you are super genius bro.

You are a Super Power BI Expert. Thank you.

This was so helpful! Exactly what I was looking for

Hi Patrick, Great video. How would you do the same report but instead of using a spreadsheet, let the user choose Students from a list on the report?

Thank you. PATRICK, YOU ARE A GENIUS!!!

Awesome trick Patrick. Thanks!

This video saved me hours and hours and hours :) Thanks a lot!

Thanks for this great video! Would be nice if we could pass parameters via variable in the power bi report link

How can this be done when not using a stored procedure, just a regular query imported from SQL?

Hey Patrick, thank you for this lovely tutorial. Can you pls suggest me how to do the same with multiple parameter. Please reply, stuck in the situation for few days

Hey Cubesters, great video! I'm in awe of your creativity in coming up with solutions that automatize everything so neatly and concise. If I may, I have an issue of my own which I simply cannot find the solution to. I pass a custom query in advanced editor to a MYSQL server and would like to make it dynamic by passing a parameter inside the query. However, I'm unaware of how I'm supposed to that inside the ""WHERE"" part of the query. For example, I've created a parameter (LUNA) which resembles the month number and would like to pass it to my query in this section ** WHERE MONTH(data)

For scenarios that demand on the fly dynamic reporting, Parameters should be used by end users in a meaningful and easy way, allowing for complex filtering of the data on the RDBMS side. for example check out how BIRT does this without breaking a sweat

Definitely has some interesting implications. I was just asked about something like this yesterday and wasn't even looking for this video. kinda spooky.

Hello Patrick, great video thanks. This video makes me think of this scenario: Imagine a business expense report on PBI and add end users who would like to comment on this result using only the PBI site, I wonder if it is possible to combine this solution for this case: Maybe 1 some visual gets the comment on the page (will be my parameter), 2 identify the other parameters (filters on the page), 3 call the store procedure using this parameters and insert the comment in the database, 4 update the data set (I do not know how to do this for each entry) . I have not tried to do this, I'm just thinking of another applicability to this new knowledge I've received.

Great video. Great idea

wow...that's hot great out of the box solution. thanks !

Thanks for a great video! Is it possible to use this with DirectQuery or is this only supported by Import Mode? It would be awsome to use the functions and parameters to limit the number of rows returned from large datasets, making PowerBi an effective tool for big data!

Wonderful. Instead of parameter values from Excel source, can we have the value passed from URL? Once submitted, the filtered data set must be returned from database itself?

Great Video Is there a way to use your report with parameters, but passing values directly in the URL?

This is Just AWSOME!

Awesome! Thanks a lot for the explanation! Greetings from Israel!

Hi Patrick..Superb video.. I have a requirement in which I need your suggestion. I need to dynamically pass the parameters so that the report automatically filter out with respect to a ChatBOT prediction. Is it possible?

This is so great. You guys are kings.

Great job, thanks for the input. I need to do the exact same thing with Snowflake, how could that be done?

Great stuff - well done!

Hi Patrick, it was great explaination and solved my doubts too, but is there any way I can pass two parameters in the single function, and my parameters are coming from two different SQL tables. Thanks

thanks for this amazing video man . its solved a same problem i was struggling from last couple of days api call with with hundreds of parameters

Hi, great video !!! But I am facing an error when i'm trying to pass two parameters my code: [Query="Exec CustomersList ", &StartDate,&EndDate -- but it is giving me syntax error .. can you please help me out with passing two parameters ?

Absolutely amazing. Thanks dude!

I have take a class. This is so good I actually taste it.

Great video, my mind is blown!

This was made my day thank you very much

Very nice explained! I will adapt this technique to use values from another query and not Excel :) Thanks, Patrick!

Nicely explained. Can I place the Excel document in a shared location such as Azure blob storage or Sharepoint document library or some other better place you could recommend? Thank you

I was looking for this a year ago... Awesome-sauce!

Great video! You've helped me a lot. Quick question, if I am making a report that has model numbers and their related data, how could I go about making the report appear with no data until the user uses the filter function to show data for model numbers they choose to filter?

Very nice! Any chance of seeing what that stored procedure looks like?

Nice Job! Just what I was looking for! Tks!

Like your video very much. How to use your method to have a date range as a parameter to pull the dataset, interact from the web? thx again!

Awesome video, it solves a big part of my project request. On top of parameters, Patrick, my user wanted to supply a date to be inserted to the report as a new column, like something they do in excel. Is it doable in Power BI and if so, how would you do that?

Great stuff! Nice presentation - very easy to follow : )

Lovely video, great feature!

Hey Cubesters, Its Super cool.i have a question here.i want to make it as a filter ,intead of gng and refresh the things then how it work

Thank you very much Patrick !!! How we should proceed if we call a function from sql and we parsing the parameter from a slicer ?

I'm a bit confused. In your custom function, is it the StudentID Parameter that is called, or the StudentID query?