In this tutorial, you’ll be learning how to share a file with a person that’s not part of your company using a query parameter in Power BI. You may watch the full video of this tutorial at the bottom of this blog.
The data used for this tutorial is a basic stock file containing clothes data.
For this example, let’s say you’re working with a person named Bill.
Bill is someone you’re not familiar with. He could be an outsider or someone from your company but is part of a different department.
He asks you for help regarding the issues he’s experiencing with a certain file in the database.
This problem can only be solved using a power query.
Unable To Access Files
You can go to the power query by clicking the Edit Queries option.
However, the problem is you don’t have access to the folder Bill is using.
An error message appears which prevents you from proceeding.
When Bill imported the data in the location, he used a text file. This could be a CBS or Excel file, among others. The file is located in Bill’s desktop which you don’t have access to.
But for this example, you conveniently have this data residing in your desktop so you can easily change its file location. This will then allow you to view the data.
However, the next issue would be when you send this file back to Bill. He’s going to run into the same problem you had when you first tried to open the file.
Ideally, the simplest solution would be to place this in a shared data source on a network drive or in the cloud.
But for instances when this option isn’t possible, you need to create a simple parameter that will feed into the file’s source. So, instead of a hard-coded value, it’ll be a drop-down or an option where the user can type something to change the folder or file location easily.
Using A Query Parameter
Here’s how to do it.
Go to Manage Parameters and select New Parameter.
You can name the new parameter as you wish. In this tutorial, it’s called FileLocation.
The Current Value is the entire file path which is found under C:\Users.
After you’ve placed all the necessary input, click Okay.
Now, go back to the Stock Performance Data Query, and click the Advanced Editor option.
To make sure you won’t experience these problems in the future, you need to get rid of any hard-coded values in the query editor. So, instead of the file location, you need to type in the parameter in Power BI.
Hard-coded values make things inflexible.
Instead of a hard-coded value, it’s now going to put in the parameter value.
Changing File Locations
In the Manage Parameters section, you now have a list of values.
With this, you can now easily change between file locations. All you need to do is place all the locations in the list.
When you send this file back to Bill, he can easily change the location to his desktop so that he can easily access the file and vice versa.
You can make any transformation in the file locations to suit the needs of the receiver.
***** Related Links *****
Power BI Query Parameters: Optimizing Tables
Unpivot And Pivot Basics In Power BI – Query Editor Review
My Practice Tips When Using The Power BI Advanced Editor
Conclusion
The technique featured in this tutorial is an easier alternative to make file-sharing or location changes flexible. This is also most helpful when you have multiple files.
It’s a simple technique that shows you how to create a new parameter in Power BI to make things more flexible for everyone using the data.
All the best,
Nick
[youtube https://www.youtube.com/watch?v=v7gnqODMmCw?rel=0&w=784&h=441]
