Parameters and Functions in Power BI / Power Query–Combine Files Experience

Combine Files

I highly recommend that you read the first and second part in this series prior to reading this one, but you can jump right in if you understand the main concepts of parameters, arguments and functions in the M language.

Understanding the Combine Files Experience: Practical Example

Consolidation

The scenario that we’re going to tackle is going to be THE most common way that people use Functions without even knowing what is going on.

We’ll go over the new Combine Files experience that was introduced in Power BI Desktop at the start of 2017 and how everything changed from there.

We’ll try to combine 3 files that we have inside of a folder. Each file is a simple csv with monthly data and we’re trying to append or consolidate everything into one big tall table.

image

You can follow along by downloading the sample files used from the button below:

Download Sample Files

Let’s jump right into it!

Connect to the Folder

Make sure that you unzip the downloaded file before continuing and that you remember where you saved that folder / files.

The first step is to go ahead and connect to that folder that we just unzipped.

So go ahead and select the “From Folder” option from Power BI Desktop or Power Query inside of Excel:

From Folder

this will prompt a new window where you’ll need to input the folder path:

Folder path

After hitting OK, a new window will appear that shall give you a preview of the files inside of that folder. Instead of clicking on Combine, click on Edit or Transform if you’re inside of Power BI Desktop:

image

This will then take us to the Power Query Editor where we’ll actually start doing the magic:

Power Query Editor

This is usually the time when people click on the Combine Files button, but in this case we’ll be doing it completely manual so you can have a full understanding of what actually happens behind the scenes when you click that button and how everything is accomplished using nothing but functions and Parameters.
Combine Files

(don’t click that button! keep reading the post!)

Grab a Sample File

The data inside each of our files looks like this:

SNAGHTML2722b4b2

it does require a set of transformations, but instead of defining the transformations on a per file basis, we can create what it’s called a Sample File and then create a function from that Sample file which can later be invoked against each of the files inside of our Folder.

It might sound like a complicated task, but it’s actually quite easy.

What we’ll end up doing is following one of the Navigation techniques that I explained in this post.

All we need to do right now is simply right click on one of the files (The text that reads binary in green or yellow) that we have in our folder and use that as our Sample File.

Add as New Query

Don’t forget to select the option that reads ‘Add as New Query’. The result of that operation will be a new query with just a Binary:

image

I highly recommend that you rename that query to Sample File Binary.

Create the Parameter

image

So far we have 2 queries. One is a table of the files inside of our folder and the other one is just a binary from one of the files in the folder.

The next step is to create a Parameter. We’ll do that by going to the Home Tab, clicking on Manage Parameters and adding a new Parameter as shown below:

Manage Parameters

Create a Function

With the parameter in place, the next step is to create the Custom or User Defined function.

We’ll start by simply creating a reference over the Sample File Binary:

image

and on the newly created query, replace the formula in the formula bar from = #”Sample File Binary”  to be =#”Sample File Parameter”

Once you hit enter, then Power Query will try to act automatically and the result will look like this:

image

I’m going to rename this query from Sample File Binary (2) to Transform Sample File.

Now all we need to do is right click on our query and select the option that reads Create Function…:

image

this will display another window where you can define what your new function should be named:

Create Function

I chose to go with Transform File Function and once you hit OK you’ll get the following:

image

We’re not done yet. Now we need to create our function, but we don’t have to write a single line of code for it.

You can go back to the Transform Sample File query and start doing the transformations in there.

We need to do only 3 Transformations, but before do any of them, I highly encourage you to delete the Changed Type step. Hardcoding the name of the columns might create some errors in the future and that’s the main reason why we get rid of any Changed Type steps at this stage.

Replacing Values

Notice how in the first column, named Month, we have a bunch of blank values.

In order to replace those with the current name of the month, we need to replace the null values with blank by right clicking on the column and selecting the option to Replace Values and the entering the arguments as shown below:

image

and then you can go ahead and do a Fill Down on that column:

image

Unpivot

We see that the markets are across the columns, but we need them all to be inside a single “Market” or “Region” column, so we need to unpivot those columns. To do that, we simply select the 2 columns that we don’t want to unpivot (Month and Product Name), right click on them and do the Unpivot Other Columns Operation:

image

Rename Columns

After doing all of the previous steps, we end up with a table with 4 columns, but we need to rename the column Attribute to Region and the Values to Amount:

image

You might not see it, but everything that you just did has been translated into a piece of M code and canned into our Transform File Function.

Organize everything in Folders / Groups

Before we move forward, I just want to tidy things up, so I’m gonna go ahead and place everything within Folders / Groups:

image

This is a completely optional step, but it adds a lot of value when you want to go back to your query and see how everything ties together. Creating a group is as simple as simple right clicking on the Queries pane and select the option to create a Group or move a query from one group to the other.

Apply the function to the rest of the files

OK! Everything is good to go. All we need to do now is simply apply that function to all of the files inside our Monthly Sales Data query, how do we do that?

Simply go to that query, click on the Add Column menu and select the option that reads Invoke Custom Function:

Invoke Custom Function

This will create a new column and I really just want to keep that new column and the name of the file, so I’ll delete the rest of the columns from my query and then I’ll go ahead and click on the Expand button for the newly created column and expand all of the columns inside that table column:

image

and, after setting the data types for each column, I was able to test and make sure that all of the data was indeed in this table:

image

Now you have a better understanding of what happens when you click the Combine Files button and how powerful Functions are.

Extensibility

The good thing about this approach is that it is quite user friendly. You can add new parameters to your function and the code will adjust accordingly.

You can add new steps, remove the previous ones or change them from the Transform Sample File query and the function will adjust automatically without you ever needing to write a single line of code.

For example, I could potentially add a new Parameter called TopRows:

image

Go to my Transform Sample File query and add a new step to Keep Top Rows (using that newly created Parameter as the argument):

image

and then when I go back to my Monthly Sales Data query it’ll have an error because the Function requires 2 parameters and we only passed 1.

This means that the Dialog will show us 2 parameters instead of one and here’s what I did with that:

image

which means that I’ll only get 1 row (the top row of the table) from each file.

Here’s what that looks like:

image

Why this and not the manual approach?

If you’re an advanced M user, then you could probably make this happen by writing the M code yourself, but the debugging experience for any other approach other than the one described in this post is simply not effective as it consumes 80% of your time going back and forth from seeing the code and running the actual code.

With the approach described in this post you can have the code in one query and a preview of how it works in another, which is, from my own experience, invaluable when you’re trying to understand a code that you didn’t create.

That’s why I recommend this approach even to the most seasoned M professionals out there. This is a FAR better experience when you’re trying to debug and understand ANY code from ANYONE.

Stay tuned for next week’s blog post where I’ll cover Parameter Tables

One Comment

  1. this was a lifesaver!!! I kept getting OLE DB errors because my files in the folder I’m querying have varying columns. all fixed by removing the change type in the transform! thanks 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.