I was recently catching up on Blogs as I always find this a wealth of information and it often leads to me learning something new. Which I can then leverage in my current work environment and this is one of those cases.

This is to show how using Power BI and the built-in Parameters it can be capable of handling changing folder locations, as well as fewer errors with Flat Files, CSV’s of Excel  Files. And this does happen fairly often.

I have been working a lot with CSV and Excel sources, and from time to time, when refreshing the data I got the dreaded Formula.Firewall error as shown below.

This then lead me to the blog post by Ken Puls (Power Query Errors: Please Rebuild This Data Combination), where he goes on to show how to work around this. But in his version Ken uses Excel and a table to pass parameters.

Example in order to follow

For this blog post it is best to use a simple example to follow with.

  • In this example I am going to import a CSV File that contains Weather Information from Brisbane
  • I will then create a parameter and link it back to the Source CSV File.
  • Finally I will then show how to create a new query which will reference the Source.

Loading your Source Data

The first thing that we will need to do is load your source data in the typical way.

As with the above example I am loading the CSV File with the Weather information.

Once loaded you will see the following:

Next what I like to do, is to rename this query from BOM-BNE to Source – BOM-BNE.

The reason for this is so that I can differentiate later what is my source file and what is the file that I now want to modify.

Creating the Parameter

In the steps below is where I will now create my parameter and modify the existing Source file to use the parameter.

  • First is to create the Parameter
    • I click on the Manage Parameters Icon in the ribbon and select Manage Parameters
    • Now I create my Parameter as shown below
        • As you can see I have given it a name of “Folder Name – BOM“, this is if I have multiple Folder Parameters I know which one is associated to which Parameter.
        • And then I gave it the location of where my CSV File is.
        • Then I clicked Ok.

Modifying the Source

In this section I am going to modify the Source.

This is to ensure that my source is exactly that, just the source with no other steps.

  • Next is to modify the existing source.
    • I click on my Query “Source – BOM – BNE” and then in the ribbon click on Advanced Editor
    • This now brings up the Advanced Editor, and before I edit anything this is what the code looks like.
    • Now I will replace
      the C:\PBI with the parameter I created earlier “Folder Name – BOM“
      • So the existing code has the following:

                  Source = Csv.Document(File.Contents(“C:\PBI\BOM-BNE.csv”)

      • I then modify it with the Parameter:
                 Source = Csv.Document(File.Contents(#”Folder Name – BOM” & “\BOM-BNE.csv”)
        • Now what I have done above is to put in the Parameter Name, which in Power Query requires it to have the following syntax shown in Light Blue
          • #”Parameter Name”
        • Then in order to append this to our existing File Name I had to continue this with adding the & “ shown in Purple
      • Then I clicked Ok.
    • Your data should now refresh the preview and it will basically look the same.

Disabling the Load of the Source

Next is a handy trick that I learnt from Reza Rad (Performance Tip for Power BI; Enable Load Sucks Memory Up) in terms of disabling the Load of tables that are not going to be used in the Power BI Model.

NOTE: What this does is because you do not load the data into your Power BI Model, it does not consume precious memory.

  • So in order to disable the loading I right clicked on the query “Source – BOM-BNE” and unticked Enable Load
  • It will prompt you with the following window
    • Click
      Continue
  • So once I completed this it was greyed out.

Referencing your Source

Now in the next step is where I now can easily reference my Source data.

And this is where as per Ken Puls blog, it not only avoids the potential error, but it also separates my source data from my working on the data.

  • To do this you simply right click your query.
    • As with our example I right clicked “Source – BOM-BNE” and selected Reference
  • Next I renamed it to “BOM-BNE“
  • And then I started working on my Source File to get it into a Shape that I required.
  • Now if you had to look at the Advanced Editor we can see where it references our Source

And what I do generally to make it easier for Navigation in my query Editor is to create groups for my different data aspects as shown below.

And this is a very quick report using the data

You can download a copy of the file here: Using Parameters for Flat File – CSV – Excel Source Data.pbix