Share and Refresh Power Query & PBI Get Data with Local Links

By Matt Allington

The concept of parameter tables in Power Query will be familiar to some and new to others.  A parameter table is a way that you can “pass” values to Power Query (and also Power Pivot) and these values can then be used in your queries and/or the data model.  I became aware of the concept of parameter tables mainly from a couple of blogs I regularly read, namely those of Chris Webb and Ken Puls.

I was halfway writing this blog post when Ken Puls published this blog article on the same topic as my blog today.  After chatting to Ken he encouraged me to post my blog anyway as there are always slight nuances on how people do things, so here is my way to solve this problem.

Sharing Workbooks with Local References

I came across a problem when I needed to develop a Power Query workbook on my local PC, but then share the workbook with another user.  When this happens, all of the hard coded paths that refer to my local files all have to be rewritten when you send the workbook to someone else.  Realising I had a problem I decided to extract the paths from my code and place them into a Parameter table so that I could easily change the path when I shared the workbook.

The first thing I did was a quick Google to read up on the concept of Parameter Tables.  I quickly found a great article on Ken’s blog and followed his advice on how to set one up.  I rewrote my queries so they extracted the Path Parameter from my Parameter Table.  When I sent the workbook to my customer, I just replaced the paths in the Parameter Table with the path my customer had configured on his PC (he needed to tell me the path names in advance so I could manually add these to the parameter table).

But wow – very quickly that became a hassle too.  I needed to  change the path each time I sent an updated version of the workbook to my customer.  It then occurred to me that I could extend this Parameter Table concept to capture “who” the current user was, and use this extra information to store the parameters for all users in a single parameter table.  This will make more sense as you read on.

First I created my table in Excel

I have 3 columns in my Parameter table.

1. The name of the Parameter
2. Name of the User that the parameter applies to
3. The value of the parameter

Notice how the CurrentUser Parameter has my name (and no user) and all the other parameters have 2 rows in the table; one for each person that will use the workbook.  I will use this first parameter CurrentUser to tell Power Query who is the active user and then Power Query will be able to find the correct values for each of the parameters for that user.

Time to build my Parameter Function

This new Power Query function I am about to write is going to be used to extract the correct path “on demand” for the current user.  I will then use this new function inside all of my Power Query Workbooks that import my data tables from my PC so I can change the hard coded path(s) with the path(s) I have stored in my parameter table.  Then all I will need to do is manually change the value of the CurrentUser in the above parameter table, and all of the data paths in my Power Query code will change automatically  – read on to see how I did it.

Note, you don’t need to know how to write each line of code you will see below.  I am going to explain the process of how to get Power Query to help you write the code.  If you learn the process, you will learn a technique that you can reuse over and over without having to be an expert in the Power Query Formula Language.

First I clicked inside my parameter table (shown as #1 below) and then went to Power Query (#2), then clicked From Table (#3).

In Power Query, I then clicked on the filter for the User column (shown as #1 below) and selected one of the names in the list (#2).  It doesn’t matter which name – I just did this to force Power Query to generate a line of code that I will edit shortly.

I then applied a second filter this time to the Parameter Column.  Once again it doesn’t matter which item I selected as I am just trying to force Power Query to write some code which I am going to edit.

I then switched to the Advanced Editor.  The code is really very simple but it contains the structure I need to create my function.  Frankly I could not write this line of code myself from scratch without a lot of errors and searching online for help.  But this is the great thing about Power Query – you don’t need to know how to do it, just let the UI create the code for you.  I am sure you will agree that it is pretty easy to work out what is going on with the code (below) once it is created by the UI.

Time for some manual intervention to the Power Query Formula Language shown above.  If you recall the original unfiltered table (shown again below), when the value in the User column is null, the value column will show the current user name (the user name matt in the Value column below is just text manually entered in the parameter table).

So I copied the 3rd line of code (that starts with #”Filtered Rows” = Table.SelectRows”) and created a duplicate copy.  I then edited this duplicate line of code as shown below

Note what I did above to the new duplicate copy of my line of code:

1. I changed #”Filtered Rows” = with MyUser=
2. I changed the part of the code each ([User] = “matt”) to be each ([User] = null)
3. I deleted everything after the and portion of the filter on this new line of code as I didn’t need it (I kept the last bracket and comma of course).

When I close the Advanced Editor, there is now a new Applied Step (shown #1 below).  As you can see the MyUser step returns a single row table (shown #2 below).

And the next step Filtered Rows returns a different single row table (shown below).

Here is a Very Important Insight

Now stop and pause for a minute and think about this.  This is a very important fact about Power Query that is not obvious.  You can write a random line of code and place it anywhere within your other code lines, and it will not have any negative effect on the rest of the code (Edit: see comments/warning from Ken below).  The reason this works is that Power Query is actually a functional language, not a procedural language.  Each line of code is in the format

myResult = myFunction(some parameters),

Technically Power Query doesn’t need to start at the first line of code and work its way down the page in logical order.  What it actually does is execute each function “on demand” to return the values needed to complete the task at hand.  You can think of it as being a bit like the Excel function language.  If you put a function in a cell A1 in Excel, and that function points to another function in Cell A2, then the function in Cell A1 is “calling” the function in Cell A2.  Power Query is just like that – each new line is simply a new function that is typically calling the result of another function.  When Power Query creates a line of code, it typically uses the previous step as input to the new step.  But it doesn’t have to be in this logical order – you can add new steps and change the order as much as you want directly inside the Advanced Editor.

Back to the task at hand

I am back in the Advanced Editor again.  Note that Filtered Rows is hard coded for user “matt” and I want this to be a variable that I can pass to my function.  What I need to do is first read the Value from the row of data returned in step MyUser (which is currently “matt” but could be “John” or anything else).  To do this, I added one more line of code as show below:

This new line of code uses the function Record.Field to extract a value from the intersection of the first row MyUser{0} and the column called “Value” (note Power Query uses a zero base index system so MyUser{0} is the first row, MyUser{1} is the second row etc).  This is really the only hard step in the entire process because this line is not generated by the Power Query UI.   And I didn’t invent this step myself – I learnt how to do it by reading material created by Chris and Ken as mentioned earlier.

If I close the Advanced Editor again, you can see the new step I created (shown as #1 below) has returned a “value” matt (shown as #2) instead of a row in a table.  This is what I want so that I can pass this value to my function.

Back to the Advanced Editor, and change the hard coded reference to “matt” with the name of my line of code MyUserValue.

OK, now my Power Query will read the name from the CurrentUser row in my table to work out which user is the “active” user.

---

Interested in Learning How to Do this Kind of Thing?

Our New Online Power Query Course just launched this year. You’ll learn Power Query from the best in the business, two Excel MVPs Ken Puls and Miguel Escobar. We’ve included 7 Hours of Self-Paced Video over 31 Courses, with lots of example files, and a Digital Badge powered by Acclaim.

CLICK HERE to claim your spot.

---

The next thing to do is to turn this code into a function so that I can choose which [Parameter] I want to extract. Currently it is hard coded to the “MasterDataPath” parameter but I want to be able to specify “which” parameter to extract at run time.

Back to the Advanced Editor.  First I add the line of code that accepts the parameter (shown as #1) and then change the hard coded filter to use this new parameter (#2).

If I were to stop there, the function will return a row in a table (shown below).  I don’t want the row in the table but the intersection of the row and the Value column (just like when I extracted the CurrentUser name above).

So back to the Advanced Editor for the last time and add a line of code (shown #1) to return the value instead of the row in the table using the same line of code I used for extracting the current user value (adjusted for the task at hand of course). Note I also have to change the last line of code (shown as #2 below) to match the name of the new line of code I created in step #1 below.

The objective here is not to manually run this function – you can invoke it to test it if you like. If you invoke the function manually by passing a parameter (MasterDataPath in the example below), it returns the text value of that parameter for the current user name listed against the parameter CurrentUser.  If you do this to test it, simply don’t save the result.

Now back to the task – now that I have the function built, it is time to use this function so that I can change my Power Query Workbooks to get rid of the current hard coded paths.  Instead they will extract the correct path from my parameter table using the new function.

I have a regular Power Query Workbook to import some data from my computer like this.

When you jump to the Advanced Editor, you can see it is hard coded for my local file path

So all that is required is to replace this hard coded path with the new function that will call for the correct path from my parameter table.  To do this I executed the following steps (you can see the results from each step as the first 3 lines of code after the let statement in the image below).

1. I kept the original line of code and commented it out.  This makes it easier for me to reverse the steps if needed.
2. I added a new line of code to call the new function and return myPath
3. I edited a copy of the original line of code and replaced the hard coded path with the path returned from the function.

And that is it – done.  All you need to do now is

1.  Make sure you enter the correct path(s) for every user in the parameter table.

2. Manually change the name of the current user in the parameter table when you share the workbook.

It is possible to write some VBA code to extract the user name (using the ENVIRON VBA function) if you like.  Read about that at ExcelGuru or just search on the web.

You can download a copy of my workbooks here if you are interested in taking a closer look.

Matt Allington is a professional Self Service BI expert based in Sydney Australia.