1-5b

[Will] If you wanted to send a Power BI Desktop file that you've built to one of your colleagues to allow them to refresh data, but potentially only pull data for, say, the country they're interested in, you can use something called query parameters. What these let you do is let you define the report, model, and everything else in your Power BI desktop file, but then specify a little prompt that appears when a user opens that file to type in a value or select from a drop down that you can then use in your queries or in your calculations as you build that report. They'll only see, for example, if it was a prompt to select a country, they would only see then the data for that country. That's one of the examples, but let's take a look at how it works in practice. So here I've already built a report in the model I'll use in Power BI desktop. I've got some simple information here showing sales by product category and across different countries. And if I go and look at the query editor, I'm just pulling in two tables— one with sales amounts, and one with product details. But, like I said, perhaps if this was a very large data set, or if it was connecting to a database, and you wanted to limit the amount of data that people were bringing back from that database into Power BI desktop, you could use one of these query parameters to do that. So in this case, we're going to filter it down by country. And to start, I'm going to come up to this icon in the ribbon, and I can choose to create a new parameter. I'm going to type in the name and a description. So this is a country code, and it's the two letter country code for your area. So I'm going to imagine setting this to sales managers who manage each of these different countries around my organization. And I can choose here what the data type is, so if I wanted a date time field, if I wanted to potentially filter it by date, I'd get a little date picker in the prompt. In this case, it's just a text field. I can choose whether somebody has to put in a value for this parameter. So potentially if I had a whole set of parameters, maybe some of them would be optional. And I can choose whether it's a free form text box, a list of things that we're going to specify, or actually the results of the query. I'm just going to type in a list, so Canada, US, and UK. I'm just going to select those three. I get to choose a default and what the current value is. By default it means what will be shown in that prompt when somebody opens it by default, and the current value which is whatever I have selected at the moment. So there's a whole range of ways you could use this. For example, filtering data down before it comes into Power BI desktop like I'm doing here. Maybe even changing the database that it was connected to. So if you had different databases within your organization, but you can run the same queries and produce the same reports against them, perhaps moving from a test to a production environment. You could drive that through one of these parameters. So now I've got that parameter set up here, and I see that in the query editor as another query. And I can use it just as any other data source within the steps here in my query. So I can actually use this to come and do a filter on this country code field. Rather than typing in the value that I want to filter by, I'm going to choose one of the parameters that I've already defined, in this case, that country code. I could use this in my MScript as well, so I've seen people kind of replacing values with a value typed into one of these parameters, or a whole bunch of different ways you could use this. So when I close and apply this, it will go and requery that data. But in this case, it's only going to pull in data for the US. So that shows you kind of how you define these parameters on how you then use them when you're building and defining your report in your model. But what about the piece when you send it to another user? If I save this desktop file and just email it to them, they'll already see the US data, it's already been there, they'd have to go and refresh it again. The other thing is, it might be a very large data set. So what I can do is actually define some sort of Power BI template. And basically what this does is strips out all of the data from Power BI desktop, but it retains the query definition, it retains all those transformations, it retains any calculations that have been defined on top of it. And, of course, the visuals in the reports as well. So let's call this one our sales by country. Save that here onto my desktop, and I'll just close that one down. So I've now got this Power BI countries template. And this is the thing that I can send to one of my colleagues, and you can see it's only 16K, it's just the definition here. And when somebody opens this up, the first thing they'll see is they'll get prompted to fill in those parameters. So in our case it's going to be the country code, but it could be, like I said, kind of which database you want to connect to, what date range do you want to pick, etc., etc. You can see those three values that were populated for me. So let's just choose Canada. And I want to hit load—the same queries will be run. This time it's filtering the data to just Canada. So we just see those values for the Canadian data. None of the US data has come into the Power BI desktop here. That query is kind of pushed back down to the underlying source as well. So those query parameters give you a way to prompt your users to select particular data, or enter particular values that you can use when you're defining the rest of your models in your reports.