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

image

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).

image

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.

image

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.

image

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).

image

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

image

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).

image

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

image

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:

image

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.

image

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

image

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.

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).

image

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).

image

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.

image

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.

image

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.

image

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

image

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.

image

 

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.

  Subscribe to PowerPivotPro!
X

Subscribe

Matt Allington

Matt Allington is a Microsoft MVP specalising in Power Pivot, Power BI and Power Query Consulting and Training based in Sydney Australia. Visit Matt's blog here. 

This Post Has 7 Comments

  1. Matt,

    The section “Here is a Very Important Insight” about Power Query as a functional language and the benefits that brings to writing custom code is a game changer.

    Looking forward to getting a copy of your book when it comes off the press! Amazon says December 1, 2015 is the release date for the printed version. Will there be an earlier release for the e-book?

  2. Matt, one quick comment on this: “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.” That’s not 100% true. If you’re building queries against a database, the existence of a non-UI generated line of code will break the ability for any subsequent commands to be folded by the database. My understanding is that it doesn’t matter if the line is used or not, the existence is enough. At that point the M will attempt to fold anything before, but the remaining processing falls back to Excel. So if you’re trying to pass parameters dynamically to the query and use the GetParameter function to provide those parameters… query folding is dead to you.

    Oh, and I’m glad you posted your version. Always good to see the minor nuances that we do differently. 🙂

  3. I created a report using a very simular concept. Our problem was EVERY time we refresh the report it is for a diffrent category and time frame so all the linked files and folders are in diffrent places.
    So using the same concept you just described we can take it one step further by clicking on the linked file locations rather than typing them in. You’ll have to save your file as a macro enabled workbook. In the VBA editor, on the worksheet event page that has your Parameter Table, paste in this code:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error GoTo oppps

    If Not Intersect(Range(“D2:D2”), Target) Is Nothing Then ‘data folder
    i = Target
    If Target.Offset(0, 1) “” Then
    Target = GetFolder(Target.Value)
    End If
    If Len(Target) = 0 Then Target = i
    End If

    If Not Intersect(Range(“D3:D6”), Target) Is Nothing Then ‘data file
    i = Target
    If Target.Offset(0, 1) “” Then
    Target = GetFile(Target.Value)
    End If
    If Len(Target) = 0 Then Target = i
    End If
    Exit Sub
    oppps:
    ‘MsgBox “Looks like there was an error!”
    End Sub

    Modify the above ranges Range(“D2:D2”) & Range(“D3:D6”) to match your “Value” or “File Location” column in your Parameter Table. (NOTE: The first range will bring up an Excel GetFolder dialog box when any cell in the range is clicked on and the second range will bring up an Excel GetFile dialog box. Comment out if you don’t need one or the other.) This should be all you need to modify.

    Then create a new module and paste in these custom functions:

    Function GetFolder(strPath As String) As String
    Dim fldr As FileDialog
    Dim sItem As String
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
    .Title = “Select a Folder”
    .AllowMultiSelect = False
    .InitialFileName = strPath
    If .Show = True Then
    sItem = .SelectedItems(1)
    Else
    Exit Function
    End If
    End With
    GetFolder = sItem
    NextCode:
    Set fldr = Nothing
    End Function
    Function GetFile(strPath As String) As String
    Dim fldr As FileDialog
    Dim sItem As String
    Set fldr = Application.FileDialog(msoFileDialogFilePicker)
    With fldr
    .Title = “Select a File”
    .AllowMultiSelect = False
    .InitialFileName = strPath
    If .Show = True Then
    sItem = .SelectedItems(1)
    Else
    Exit Function
    End If
    End With
    GetFile = sItem
    NextCode:
    Set fldr = Nothing
    End Function

    Happy Exceling!

  4. Thanks a lot for your contribution Matt! it helped me a lot!!!

    Anyone has experimented issues using this approach combined with Power BI, when try it to refresh from on premises databases?

  5. I’ve got a fairly simpler solution:

    You name a cell something such as “path” and put this formula (which gets the path of the current spreadsheet) in it:

    =LEFT(CELL(“filename”,A1),FIND(“[“,CELL(“filename”,A1))-1)

    Then, to transfer it to Power Query as a constant, you insert this line in the editor:

    path = Excel.CurrentWorkbook(){[Name=”Path”]}[Content]{0}[Column1],

    It loads the named range as a (single value) table and gets the first row ({0}) of Column1, i.e., the path of the current sheet.

    This way, you can load the file using a relative address:

    address = Text.Combine({path, “filename.xlsx”}, “”) //Note that “path” will be the path to the current spreadsheet. If the files are in the same folder, then their paths are “path” as well
    Source = Excel.Workbook(File.Contents(address), null, true),

    Easy as 123

  6. Very helpful, Matt. Provided thorough instruction on how to achieve exactly what I was after & couldn’t find anywhere else. Thank you so much!

Leave a Comment or Question