One of the attendees at today’s Power Query Workshop asked about a specific scenario today.  His company doesn’t have a solid IT infrastructure for sharing data, and he’s trying to figure out the best method for sharing Power Query solutions with a colleague so that he’s not the only one with the power to update.  Let’s take a look at this and explore a solution.

Background

The scenario is based around high level soccer (football) players.  The National Team coach wants a report every week on the status of the current National Team players. For instance he wants to know things like - have they been injured, how many training sessions they’ve done, their training data (GPS, Heart rates etc), and other wellness information.

Of course, the players play in different clubs, each of which has an analyst who records this information into their team database on a daily basis.  Our client then uses Power Query to aggregate the data from these different team databases with the national database, to push out their dashboard.

Current Solution Architecture

Because each team is independent, they manage their own player databases.  Our client set up a system where those teams upload their databases to a DropBox folder on a weekly basis so that he can get access to their databases.

He then installed DropBox on his local PC, allowing him to sync those databases inside the corporate firewall.  The reason this is important is that he now has “local” access to their databases, as well as access to his own corporate database.  Visually, the system looks like this:

The Issue

Now this is great, all works well except for one issue… with the exception of the corporate database, all the databases needed in order to update the solution are stored in our client’s personal DropBox folder, and he uses a hard coded path to grab the data.  Using DropBox, that path is something like this:

C:\Users\<username>\Dropbox\Solution\filename

Why is that an issue?  Because he wants to go on vacation, and the dashboard needs to be updated while he’s gone.  Short of sharing his password, how can he fix this?

Goal

We need to figure out a way to have the file path dynamically updated to the correct username.  But the problem is that there isn’t an Excel function that returns the Windows username used in the DropBox file path.  Power Query also can’t query the Windows username.

But guess what?  Several years ago I just happened to publish a VBA routine to work out the Windows username.  You can find that article right here.

Sharing Power Query Solutions

Solution Adjustment Overview

Here’s the overall steps we’ll need to do in order to adjust his solution:

• Invite his colleague to the DropBox folder
• Make sure his colleague installs the DropBox app on his work PC so that the data syncs
• Add the VBA function listed above to the dashboard
• Implement a Parameter table to dynamically change the path to the DropBox folder

We’ll take it for granted that he’s taken care of the first two steps

Adding the VBA Function

The first step we need to do is add the VBA code.  That’s fairly straight forward, we just need to follow these steps:

• Press Alt+F11 to open the Visual Basic Editor
• Locate the solution workbook in the Project Explore
• Right click the solution name –> Insert –> Module
• Paste in the code from the article as listed here.  (If the module you created has the line Option Explicit at the top already, then avoid copying that line from the article.)
• Close the visual basic editor

We can test that the function worked by entering the following in an Excel cell:

=GetName(“Windows”)

Add the Parameter table

Based on the steps listed in this blog post, we need to:

• Create a new table parameter table as follows:

The final column here isn’t required for the end solution, but shows how I set things up on my side to test this out:

• The VBA function returns my username
• The DropBox Root shows the root folder to my DropBox location, dynamically created with my username
• The DropBox folder is a test folder I set up for this purpose (but would be replaced with the name/path to the shared folder)
• The Filename is a simple file I set up for my test

Of course, we shouldn’t forget to copy in the fnGetParameter function as listed in the article either, as this is pretty important to make the solution work.

Adjust the queries to use dynamic paths

Now, I didn’t have the full set of logic from our client, so I cooked up a simple example to emulate this.  Basically I just connected to the text file in my test folder, so my M code looks like this:

let
Source = Csv.Document(File.Contents("C:\Users\Ken\Dropbox\Test\Test.txt"),null,"",null,1252)
in
Source

At this point, I simply started substituting in variables into the code using the fnGetParameter function:

let
dropboxroot = fnGetParameter("Dropbox Root"),
dropboxpath = fnGetParameter("Dropbox Folder"),
filename = fnGetParameter("Filename"),
fullfilepath = dropboxroot & dropboxpath & filename,

Source = Csv.Document(File.Contents(fullfilepath),null,"",null,1252)
in
Source

And at this point, the query is dynamic.  When I save my workbook it will refresh on his side, providing he’s saved the text.txt file in his DropBox\Test folder.

Adjusting this for Databases

Our client isn’t actually retrieving data from a text file, but that doesn’t really matter.  In order to make this work, for each of his queries, he needs to do three things:

First, he needs to copy the first four lines after the let statement above into his query.

Second, he needs to update the filename to refer to the name of the database he’s connecting to:

filename = “FootballClubOne.Accdb”,

Third, he needs to locate the hard coded file path in his query and replace it with the fullfilepath variable.

And that’s it.  At that point, the file path is dynamically set.  Providing his co-worker has set up DropBox, the solution should refresh for him as well.