In this post we will explore how to make the Sample Binary file path dynamic when combining files using the new Combine Binaries experience in Excel and Power BI Desktop.

Sample Files

If you'd like some sample data files to play with, you can download them here.

Why do we even need to talk about this?

I've covered the new combine binaries experience in my last couple of posts on:

• New Combine Binaries Experience
• Combine Excel Files

But one thing I didn't dig deep into was the Sample Binary file path, and the fact that it actually gets a hard coded file path.  To replicate the issue, here's how I set up my quick test:

• Open the application of choice (I'm going to use Power BI Desktop here)
• Get Data (create a new query) From File --> From Folder
• Browse to the folder path and click Edit

In this case I've browsed to following file path, which only contains a single file (so far):  C:\Users\KenPuls\Desktop\CSVs.  And here's what it looks like in Power BI Desktop or Excel:

And now I click the Combine Binaries button at the top right of the Content column, resulting in this:

Now, as I discussed in the first post in this series, we know we can modify the "Transform Sample Binary From…" step to see those changes in the final output.  So what's the issue here?  I'm going to right click the Sample Binary and choose to view it in the Advanced Editor:

Note:  I did add a line break between the 3rd and 4th lines, so read that as one.

The key part to notice here is that the file path, despite being in the original CSVs query, is also hard coded into this query TWICE.  That makes it very difficult to port this from one location to another, as simply changing the file path in the CSVs query is not sufficient, it will still break upon refresh.  It's for this reason that we need to make the Sample Binary file path dynamic: so that we only have to change it in one place.

How to make the Sample Binary file path dynamic

To start with, I'm going to throw this solution away and start over completely.  And again, while I'm using Power BI desktop to illustrate the method to make the sample binary file path dynamic, this will work the same in Excel with only one exception. (Once you have the new combine binaries method in Excel, anyway.)

Step 1 - Launch the Power Query editor

To get started, I'm going to launch myself into the Power Query editor, ideally without creating a new query.  This is easy to do in Power BI Desktop, simply go to the Home Tab and click the top of the Edit Queries button.  You'll be launched into the editor without creating any new queries:

In Excel, if you've never opened the Power Query editor before, there is no way to get in there without creating a new query.  You'll need to create a New Query --> From Other Sources --> Blank Query.  Then you can expand the Queries pane on the left, right click Query1 and Delete it.  Silly, but that's pretty much the way to accomplish this.  (If you have created other queries, you just need to edit any one to get into the editor, as it won't create a new one for you.)

Step 2 - Create a Parameter for your file path

Before we get started, we need to create a single place to update our file path.  For this we'll use one of the Power BI/Excel parameters.  To do that:

• Go to Home --> Manage Parameters --> New Parameter
• Set up the Parameter as follows:
  • Name:  FolderPath
  • Required:  yes
  • Type:  Text
  • Current Value:  <your file path>  (mine is C:\Users\KenPuls\Desktop\CSVs)
• Click OK

This will result in a rather simple little parameter that looks like this:

Step 3 - Create a new query against the folder

Now that we have our parameter, we are ready to actually create the query we need against the folder.  So let's do that now.  (Oh, and if you're working with Excel, just stay in the Power Query editor - no need to go back to Excel first.)

• Power BI Desktop:  Home --> New Source --> More --> Folder
• Excel:  Home --> New Source (near the end of the ribbon) --> File --> Folder

When prompted for the folder path, instead of clicking Browse, click the ABC on the left and choose Parameter:

It will automatically populate with the FolderPath parameter and, upon clicking OK, will take you to the preview window where you can click OK (Power BI Dekstop) or Edit (Excel.)

Step 4 - Combine the Binary Files

Now we'll combine the binary files… all one of them.

• Rename the Query to "Transactions"
• Click the Combine Binaries icon on the top right of the Content column

Just a quick side note here… in the current build of Excel we don't see this, but in Power BI desktop, we are taken to this window where we can control how the data types are determined:

This is pretty cool, and I assume it will be coming to Excel in the future too.  If your data types are consistent most of the time, you generally won't have to worry about this.  If, on the other hand, you've got strange things that happen, (say that once every 10,000 transactions you get a fractional sales unit,) you may want to choose the "Entire Dataset" option to avoid truncated decimals.

For now, just click OK with the default to blow past this dialog.

Step 5 - Make the Sample Binary file path dynamic

And finally, here we are, it's time to make the magic happen and actually make the sample binary file path dynamic.  To do this we're going to make a couple of small edits to the Sample Binary's M code.

• Right Click the Sample Binary --> Advanced Editor

NOTE:  Be sure not to accidentally hit the Sample Binary Parameter1… we don't want that one!

Now, first thing to notice is that the very first line no longer points to a hard coded file path, instead it points to our parameter.  That's very cool as a single update to the parameter means that both this query and the original one to pull the files from the folder will be changed when our parameter is updated.  One place to fix them both.

Now, there are still issues here, but I just want to do a bit of cosmetic cleanup first.  The second and last lines still start with the name of the file.  This is just something inside the M code that you'll probably never read again, but it's still good practice to clean it up:

I've change both highlighted parts to read "SampleFile" to make the code a bit shorter.  And now I can focus on the real issue:

The highlighted portion above still holds both the hardcoded file path and file name.  What this means is that even though the folder path is dynamic, if I change the parameter and update the file path on a new computer, it will still be pointing to the older source.  That is far from ideal.

Interestingly, it's really simple to fix when you know how.  You simply grab everything from the [ to the ] and replace it with 0 so that it looks like this:

To show that it updates properly, I'm going to click Done, and throw a new file in the folder called "Feb 2008.csv".  Since this is lower in the alphabet, we'd expect it to show up before "Jan 2008.csv", and it does when I refresh the preview window:

The End Result

The biggest reason I want to make the sample binary file path dynamic is the scenario where I email the solution to someone else, and they have a different file path to the data files.  In this case they now only need to edit the project, update the FolderPath parameter, and everything will work again.

Why Not Edit in the Formula Bar?

In truth, you don't actually have to go into the Advanced Editor to update your code.  I made a cosmetic fix in there, as I actually do go back and read code later.  Since the default step name leaves a red herring in the code, I wanted to nail that down.  If you're never going to read the code though, it's cosmetic.

In effect, all that is really necessary is to replace the code from [ to ] with 0 as we did above:

The problem is that if you do this here, it automatically kicks off 3 new steps that have to be deleted:

Granted it's not the end of the world, but since I want to clean up the code anyway…

Final Thoughts

You're not alone if you think this should be unnecessary.  In my opinion, this dynamic nature should be standard, and I think it would be an easy fix for the team to implement.  Marcel even posted a suggestion to modify this feature here, which you should consider voting for.