Using Variables for File Locations

Move You Data The Easy Way
Move You Data The Easy Way

This week I encountered an issue when working with multiple queries in my data model.  Here is the source files in case you want to follow along.

Here’s what happened.  I had a PBIX file that had four queries in it, one file for the summer the Olympic metal count for the following years, 2000, 2004, 2008, and 2012.

 

Olympic Metal Count
Olympic Metal Count

After a bit of working I figured that my desktop screen was going to get to cluttered if I continued to collect Olympic metal data.  Thus, I moved my excel files which were my source data into a folder called Olympic Medals.

File Move
File Move

By doing this I broke all the links for all four files.  This was discovered when I tried to refresh my queries and noticed that all the queries failed. Power BI gave me a nice little message notifying me that there was a data source error.

DataSource.Error: Could not fine the file:
Missing File Error
Missing File Error

To fix this I had to open the query editor and change each file’s location to the new folder that I just made.  Seeing that this is not an efficient use of my time, I decided to spend more time to figure out a way to make a variable that would be my file location for all my queries.

Lets begin by making a new blank query by clicking on the bottom half of the New Source button on the Home ribbon.  Then click the item labeled Blank Query.

Start Blank Query
Start Blank Query

With the new query open type in the file location where you will obtain all your working files.  For me my file location was on my desktop, thus the file location is listed below.  Rename the new query to Folder.

Folder Query
Folder Query

Note: Since we are working on building a file structure for Power BI to load the excel files you will want to be extra careful to add a “\” back slash at the end of the file location.

Next on the query for Medals 2000, we click the Source under the applied steps window on the right.  This will expose the code in the formula bar at the top of the window.

Select the Source Applied Step
Select the Source Applied Step

Note: If you don’t see the formula bar as I have illustrated in the image above, you can turn this feature on by click the View ribbon and checking the box next to the words Formula Bar.  This will expose the formula bar so you can edit the source step.

This is where the magic happens.  We can now insert our new blank query into this step.  Our current file contents looks like the following:

= Excel.Workbook(  File.Contents(  "C:\Users\Mike\Desktop\Olympic Medals\2000 Medals.xlsx") ,  null ,  true )

Now remove the first part of the file location and make the equation match the following:

= Excel.Workbook(  File.Contents(   Folder  &   "2000 Medals.xlsx") ,   null , true )

Not only does this shorten our equation, it now uses the folder location we identified earlier and then we can pick up the file name 2000 Medals.xlsx.  This makes is very easy to add additional queries with the same steps.  Also, if you move your files to a new folder location, you only have to change the Folder query to reflect the new file location.  To test this make a new folder on your desktop called New Folder.  Move all the Olympic medal files to the new folder.  Now in Power BI Desktop press the Refresh on the Home ribbon.  This should result in the Data.Source.Error that we saw earlier.  To fix this click the Edit Queries on the Home ribbon, select the Folder query and change the file directory to the new folder that you made on your desktop.  It should look similar to the following:

New Folder Image
New Folder Image

Once you’ve modified the Folder query, click Close & Apply on the Home ribbon and all your queries will now reload.  Success!!

New Queries Loaded
New Queries Loaded

 

Hope this tutorial helps and solves some of the problems when moving data files and storing information for Power BI desktop.  Please Share if you like the tutorials. Thanks.

7 Comments

  1. I enjoy your posts Mike and I’d like to be alerted when you post new stuff. I cannot however find the anywhere to subsribe to updates/mailing list/RSS feed or the like?
    Cheers

    • Thanks so much for the feedback. I have now added an RSS feed which can be found on the left side of the screen underneath the categories section. I have also included a copy of the RSS feed here: http://powerbi.tips/feed/ Let me know if that works for you.

  2. Hi Mike,

    This is a really good and helpful post! 🙂 thanks a lot for reducing the hassle of changing the path of the dataset 1 by 1.
    However I still want to know how we can determine the path of a chosen file or make it a relative path since power BI is only accepting absolute one.

    This is for me to reduce the hassle of changing the “Folder” path whenever I move my folder from 1 computer to another. Hope you can create another post for this.

    • James,
      Thanks so much on the positive feedback. You are right, it can be a pain to move one file from one location to another. Here is a simple tick you can do to fix this issue. Introduce parameters, use a parameter in the beginning of a folder load and you can move the Power BI file around with and configure a parameter to open up the file one whatever the computer it is hosted on. I actually just did a project today that incorporated this exact feature. I will write a post about this soon. Thanks for the great idea.

  3. This is brilliant, was having a nightmare trying to create a relative file reference until I found this!

  4. Awesome solution, works like a charm where i have essentially the same report file, but for multiple cities. Every time I change one, I save-as for the others, then had to update a dozen file paths. Thanks!

Comments are closed.