Excel is for Parameters

In my last two posts, I have shown how to build a datamodel in Excel from an Excel workbook that had two levels of headings, and then I showed how to improve the code  using functions.

In the second I received one excellent comment on making the age bands more dynamic, and driven from another Excel table. This allows easy changing of the bands, and also makes it simpler to add further bands, it is usually easier to do anything in Excel than in code.

This comment also prompted me to address a further small issue I had with the code, namely that the workbook path was hardcoded into the M code. We all know that hardcoding is anathema to ‘real’ developers, and it was a bit of a nuisance my colleagues and I had to keep changing the M code as the path was different on different machines (time for a rant, it is a real pain with Power Query when you have multiple queries in a workbook that you have to exit back to Excel from the current query to then open another one, Microsoft should really make this a more efficient process).

To parameterise this path is simple. I added a two row table called FilePath with the following details:

FilePath

I created a simple function to build the path and name

let
    Source = Excel.CurrentWorkbook(){[Name="FilePath"]}[Content],
    InputPath = Source{0}[FilePath],
    InputFile = Source{1}[FilePath],
    Result=InputPath&InputFile
in
    Result
in
    FileName

Another simple change to my original main procedure invokes this function. I changed

 
    Source = Excel.Workbook(File.Contents("C:\Users\Bob\Documents\My Blogs\Unpivot Nested Headings\NZ Birth Rates.xlsx")),

to this 

     Source = Excel.Workbook(File.Contents(fnFileName())),

I think this is a much more acceptable approach, anyone using the workbook doesn’t have to go into Power Query to use their file, they just change an Excel table value.

A further enhancement we can introduce is if the source data workbook is in the same folder as the query workbook, using an old Excel trick to get the filename, the CELL function. The function
=CELL(“filename”,A1)
returns the full file path name of the workbook. In my case it would be
C:\Users\Bob\Documents\My Projects\_5 Development\ComCamp\[BirthsByRegionWithMap v2.xlsx]Source
We only want the path, which is the part to the left of the [ character. This is easily achieved using LEFT and FIND, namely
=LEFT(CELL(“filename”,A1),FIND(“[“,CELL(“filename”,A1))-1). 

Filepath formula

Advertisements
This entry was posted in Power Query and tagged , , , , . Bookmark the permalink.

6 Responses to Excel is for Parameters

  1. wessexbob says:

    I see that Chris Webb has posted a follow up to his Conditional Logic in M that is along the same principles as this post, he adds his case values to an Excel table http://cwebbbi.wordpress.com/2014/03/14/user-defined-conditional-logic-in-m/. The approach is similar to the comment in my last post by Bertrand https://wessexbi.wordpress.com/2014/03/10/a-case-for-functions/, but Chris solves it differently, Bertrand iterates the table to check the bands, Chris loads the Excel table into his M query. Those poor cats.

  2. zbarresse says:

    Very slick Bob! Nicely done.

  3. Pingback: PowerQuery Invoke Variables on Refresh

  4. Pingback: Bob The (Proto) Builder | Bob's BI Ramblings

  5. Hey Bob – your wish is MSFTs command – April PQ update allows for switching between workbook queries from within PQ window! I suppose you owe an apology on this one:-)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s