Sharing Power Query Solutions

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:

image

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:

SNAGHTMLb689aae

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.

11 thoughts on “Sharing Power Query Solutions

  1. @Ken - PQ Allows you to pull data from a file directly stored on OneDrive (either excel or access)

    Try the below
    let
    Source = Excel.Workbook(Web.Contents("https://onedrive.live.com/download?resid=AF0F671FC64E8122!4291"),true,true),
    mData = Source{[Item="DATA",Kind="Sheet"]}[Data]
    in
    mData

    It would pull data from a File called DB.xlsx that has a sheet called DATA

    Also make sure the Privacy options is set to allow Ignore Privacy Levels

  2. Cool stuff, Sam. I'm going to have to try that with a real database as well.

    With regards to ignoring privacy levels, that shouldn't be necessary. Are you trying to combine data with different privacy levels?

  3. No - you don't have to turn that Privacy level off - I just tend to this to make the file more portable.

    Even though this solution is not "Officially" supported - its works just fine.

    But I am told that official support for One drive is on its way in "Future" releases - there just some differences between the PQ team and the Windows Live -oops - Sky Drive - oops - One Drive Team to be sorted out 🙂

  4. Pingback: Mr Excel Power Excel Seminar in Toronto | My Spreadsheet Lab

  5. Pingback: Share and Refresh Power Query with Local Links - PowerPivotPro PowerPivotPro

  6. There is another very cool way that has worked well for me. I saw it on somebodys blog but forgot the source unfortunately. It goes by the same logic but uses the CELL("filename", [reference]) command.

    Important: Since it queries the filename it only works if your workbook is saved, so just opening a new workbook and not saving it will not give you a result!

    The formula is: =CELL("filename",A1)
    This will give you the complete path. You can use this formula in a table to see where the file is stored. This is especially useful for dropbox etc. because you will always know the path and can easily access subfolders etc.

  7. Hi Kaiko,

    Indeed, I've posted that technique on this blog. The issue here is that the dashboard wasn't saved in the DropBox folder, so picking up the username using the CELL function won't work. This needed to be targeted to a file path that contains the Windows user name.

  8. Hi Ken -- is there a simple version of this concept you can point me to? All I want to do is set up a powerquery on the same workbook and then be able to save it in different locations without it breaking. I've read a number of posts on all this, but they have more complications than I'm trying to deal with -- and I have no idea what I'm doing, so I'm having trouble even just dumbing them down to my needs. I read through your other parameters post, and that seems to get me 90% there, but I don't know what to do to finish it up when it comes to making use of those parameters in my simple queries. I keep getting errors that I don't understand... Thank you.

  9. Hi Tim,

    Best suggestion is to post in our Power Query help forum at http://www.excelguru.ca/forums. We'll help you get through the finer points and get you the last 10% of the way. (Sorry, but the parameter post IS the easiest - and only - method to pull it off today.)

  10. Hi, Ken! I implemented a similar solution. I find that this does not work so well when the source is a folder from where we want to combine all the files. In that case, Power Query complains about a privacy issue. The process will only work if I change the privacy settings for the file in the Power Query editor.
    The workaround is explained at around minute 10:45 on this video:
    https://www.youtube.com/watch?v=vZuMCEkL2eM&t=1138s

    Do you have a better solution for this issue?
    Thank you.

  11. Hi Celia,

    Unfortunately I don't. I wish I did. (I'll be honest, I often turn privacy off anyway, as it has an impact on the refresh speed.)

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.