Dynamic Power BI reports using Parameters

did you know that you can dynamically filter data in power bi using parameters that are stored in an Excel workbook well if you didn't you're about to find out all coming up next hey guys Patrick kind of cute it's been a crazy week just got a crazy crazy mind-blowing request from one of my customers we were we were talking about parameters and stored procedures and parameters in the power bi desktop and parameters passing data from Excel and what he wanted to do was he want to give an end user of our VI desktop allow them to call the stored procedure passing parameters but then when they were done they want to publish it to the power bi service and once it was up in the service enter some data in the spreadsheet and when they refresh it will pull the data from the spreadsheet and automatically filter the report based on the values in that spreadsheet so like well sounds like a good challenge let me show you how to do this all right give me some time and so it's crazy so let me show you how you guys how to do this alright so the first thing you need is a stored procedure so open up the power bi desktop and get your stored procedure so I'm going to go over here in management studio I love me in studio you can't see it on the other screen but I'm going to choose get data get data and then choose sequel server and grab your server name paste it there give me a database name higher-ed PW I work in education and Microsoft I'm always working with education data right the next thing you do we may have a lot of people may even know this you a lot of people may not even have no idea that this was possible you can actually call the stored procedure from the desktop so I'm going to grab an execution of a stored procedure what I'm going to do is paste it right there okay so I'm connecting to this sequel server this database and I'm going to execute the store procedure go ahead and click OK if you click ok a couple of seconds what's going to happen voila the data from that stored procedure don't click low click edit so we'll click Edit I'm excited okay until now I have my data but the request was hey they need to pass in their own parameters their own values for that execution so the first thing I said hey go to advanced editor go here change it he's like really Patrick and I do that I was like okay okay I'm just kidding I care that kid all right so what you want to do is first let's create a parameter if you guys haven't use parameters I'm going to talk about them a little more and kind of demystify them in another video but for now let's just focus on this type of parameter so we're going to do manage parameters clicking in parameter and what they were passing is a student ID so when they call this guy student ID and we're going to give it use any value will talk about list of values and queries in another video and then we're going to make it text and then we'll grab a since an ID makes it in click okay okay so there's our parameter but now we want to make the query query one which is a great name use it so we'll go back to query one right click on this guy and go to advanced editor and watch this once you have to pay attention to what I'm about to do I'm gonna go ahead and get rid of that value and type ampersand student ID if that's the name of my grammar perfect it is make sure you don't have any errors down at the bottom click done and now you see that nothing really happens but it actually executes the store procedure what I'm going to do is let's be a little adventurous I'm going to go ahead and grab another student value and it come here paste it you see you can see a little warning sign like that a little one in sign next to the query one it's because you're running the native query and I talked to my compadre Adam Saxon is like you can't avoid it you can't avoid things like the artist and I great but wait I'm going to show you how to solve this just hang on all right click edit permissions you'll get the native query box click run and then what you'll see is after the query successfully execute you see the same number as your parameter in the student ok perfect so if we close and apply this okay get our changes apply and what we're going to do is if I go edit queries and you see right there editor image I'm going to change that value click OK and we'll get our little prompt again because of our native query run and if we run it and just jump over and take a peek at the data and you'll see now it's the new student ID that I pass parameter so I was like all right there you go that's it you got it he's like no no no no not going to work it's like what do you mean he's like they want to pass multiple students in and create a report showing data for multiple students like oh man ok ok ok you can do that no problem so what I did was I created a spreadsheet form it's a really complicated spreadsheet to the single column spreadsheet 7204 7 to 1 student ID right have that guy there make sure it's safe minimize it and then get that data right I just fold to call cute videos who grab student and you'll see it pop open go ahead and get right there and make sure you click Edit ok so we want to go back into the query editor because we're about to have a little fun right now and now you can see sheet 1 let's rename this to student ID and because I specified my parameters text I'm going to go ahead and match these two up right so I'm going to change it from a whole number just to text so I don't have any data conversion problems just makes it easier I'm going to call this our students ok so now we have our students table we have our query how do I pass the values from that Excel spreadsheet into the stored procedure run into query one well watch this so right click on query 1 and choose create function create function we're going to call this get student details and click OK so now I have my function and I'm going to disable what I'm going to do right here say it's a below then I just don't want that to show up in my my field list I like to keep Phil is kind of Tidy for my end-users okay and so now we have our students and we have our function and our function right here except the single parameter now watch watch the magic watch the magic that's about to happen click student go to add column and there's this icon labeled invoke custom function you guys created their custom function what you want it let's invoke this thing right so I'm going to invoke my custom function and it's called don't worry about the column name just don't worry about that so I get to the details and we're going to pass the value from that single field which is student ID we can named it student ID that stored in that spreadsheet it's pretty cool right click OK alright again got to run a query and what you see is a single row for the student ID that stored in that spreadsheet you don't believe me I'll open the spreadsheet up there is a student ID look in the spreadsheet there's a student ID and then there's this table in the top right corner there's two arrows pointing that way all right click those two arrows I'm going to say what you want to do we don't need to bring in student a K which star student ID we already have that one you don't need to check the box for use original column names prefixed because what is what it will do is it'll prefix the column name and then at the column name again we don't want to do that right click OK and now can see all my data what what are you kidding me so I want to show you something so I'm going to go and add another one all I'm going to do is go to my spreadsheet add another one alright save this what I'm going to do here is go ahead and go home and I added a new student to the spreadsheet and now what I'm going to do is click refresh preview so I click refresh preview says yes because I added a new one it's got to run the query again and watch this and now you see two students and both of the students that's in that spreadsheet Patrick you're a genius I'm just pretty smart right ok this is cool but I keep getting that edit queries native query run query edit permissions all that stuff I want to avoid let me show you how to avoid it okay just like a cooking show remember like a cooking show fam I'm going to switch over to one that I already created okay so what you would do before I hop over there let me back up a little bit what you do is close and apply ok so if I click close and apply you see I have my students table and I start building my report all right so let's see what did I do wrong so I did I forgot to check that box for prefix but that's all right let's go back to edit queries and let's do this here get rid of that my bad ok now it's a little better there we go and click close the fly make mistakes we're all humans right there we go now our column names are a little better ok but like a cooking show now I want to switch over to a report that already created based on this this report that I created and it's using my function and you can see the results of my function I call the studentdetail that you can see I have some stuff a nice little report I created what I did was I installed the Gateway on my machine on this laptop I saw the personal gateway because I was doing some testing you can install the enterprise gateway or whatever since this is for an end user they'll probably install the on-premises I mean the personal gateway for their own you know purposes and then I published this up to power bi once I had it published up to power bi what I did was so I went to manage gateways because it's my personal gateway ok don't worry about the one with all the warning times and I set up both of my data sources so my Excel workbook that contains the values that I want to pass and then I set it up to the data that I'm pulling with I set it up to the sequel server that contains the stored procedure right where I'm going to execute the store procedure so I set those two data sources up and then what you can do is take a look at my report let's just take a look at my report so you guys see the sense of what's there it's got one value and it's got a couple of things in it ok and so now spreadsheet I have I mean let's just add one more five more right so I'm going to delete these can I don't care about those anymore pesos in there click Save and then what I'm going to go do is because I set up everything my schedule refresh is available also my on demand refresh so I'm going to go to that report I called it model the data set is called model I'm going to go ahead and say refresh now and then we wait and now BAM what you can see is that the values in my spreadsheet take a peak right that one that one that one that one that one that one that one right now my report matches it imagine this so I give my end users the ability to have data in a spreadsheet or data in whatever flat file source that they want to dynamically pass to a power bi report running in the power bi service and it will automatically filter their data just we filter it will take away it'll it's phenomenal got a better way to do it please let me know what you think about this video did you enjoy that you liked it there I talk too fast whatever it is you know let me know post it in the comments below if this is your first time visiting the channel be sure to subscribe and if you like my video please give it two thumbs up alright thank you guys and from Adam and Patrick a guy in a cube thanks for watching and we'll see you guys next time

Channel: Guy in a Cube

Views: 289,829

Keywords: Business Intelligence, Business Analytics, Analytics, GuyInACube, Guy in a Cube, Patrick LeBlanc, Power BI, PowerBI, bi, Microsoft, parameters, dynamic data, dynamic parameters, Stored Procedures, SQL Server, Microsoft SQL Server, Excel, Power Query, M Syntax, M Function, Power Query function, Power BI Desktop, gateway, refresh, On-Premises Data Gateway, Personal Gateway

Id: iiNDq2VrZPY

Length: 12min 20sec (740 seconds)

Published: Wed May 24 2017