Combining workbooks (with a function)

Overview

This method that can be used when you also want to re-shape data in your source files.

The approach is to:

  1. Load one of the workbooks into Power Query, and complete steps to transform the data the way you want
  2. Convert the query into a custom function
  3. Create a new query that loads all the names of the files to be combined, and which calls the custom function on each one

Steps

1. Load and process one of the workbooks

To set the scene, here is 1 of 3 identical workbooks. Each workbook has a slightly different name.

  • Start by creating a new blank workbook and loading one of the source files.
  • Select the correct sheet, and select the transform button in the window that next appears.
  • Complete the transformations you need.

In this example, the data is being “unpivoted” on the month columns to get a better table structure.

2. Convert the query into a custom function

This query is currently a one-off query that only works on one specific file.

  • Next, convert it into a function so that it can be used to process any file (with the same initial structure)
  • Rename the query to something that indicates that the query is a function, something that is easy to remember - e.g. fnCombine.

Power Query is case sensitive, so you will need to replicate all capitalisation when you call the function later on.

  • Switch to the Advanced Editor.

The syntax to create a function is straightforward.

  • Add a line of code before the let statement that accepts parameters into the new function as shown below.
  • This can be interpreted as saying “this function accepts 2 parameters (myPath and myFile) and then passes the values from these parameters to the query steps in the function”.
  • Replace, also the hard coded pathfilename in the original query with the parameter values. You will need to replace everything - from and including - the double inverted quotes, with the 2 new parameters.
  • There should be an ampersand (concatenate) between the 2 parameters and no double inverted quotes.

The code should look something like follows:

M-Code 

   (myPath, myFile) =>
        let
            Source = Excel.Workbook(File.Contents(myPath&myFile), null, true),
            Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
            #"Promoted Headers" = Table.PromoteHeaders(Sheet1_Sheet, [PromoteAllScalars=true]),
            #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Product", Int64.Type}, {"Jan", Int64.Type}, {"Feb", Int64.Type}, {"Mar", Int64.Type}, {"Apr", Int64.Type}, {"May", Int64.Type}, {"Jun", Int64.Type}, {"Jul", Int64.Type}, {"Aug", Int64.Type}, {"Sep", Int64.Type}, {"Oct", Int64.Type}, {"Nov", Int64.Type}, {"Dec", Int64.Type}}),
            #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Product"}, "Attribute", "Value")
        in
    #"Unpivoted Other Columns"
  • Click Done, then File, Close and Load.

When you do this, you will see the query in the Query Pane on the right shows that the query is a Function (shown by the fx symbol next to the query name, as shown below).

3. Test the function to see how it works

It is useful to test the function now to see how it works.

  • Right click on the function name in the Query Pane, and select “invoke”. The function then asks for 2 parameters.
  • Go to Windows Explorer, then do a cut and paste of the folder path and also the file name. Note that you must have a final slash at the end of the file path name (this will not come across with a standard cut and paste from Windows Explorer).

The function should successfully process the file “Multiple File Data 2.xlsx” even though the query was originally written for the file called “Multiple File Data 1.xlsx”

  • Click File, Discard and Close – i.e. do not save this query.

4. Create a new query that combines all the files

The final step is to create a new query that will create a list of all the file paths and file names you want to be combined. It will then pass the pathfilenames to the function, one at a time, for processing.

  • First create a new query that provides a list of all the file paths and file names.
  • Click From File ➔ then From Folder ➔ then navigate to the folder that contains all the files ➔ click OK.

The query will provide you will quite a few columns of data, but the only ones needed are a) Folder Path and b) File Name.

  • Select these 2 columns and then click “remove other columns”.

You should now have all the information you need to pass to the function written earlier.

  • Add a new custom column and call the function from this column.
  • The formula for the custom column is shown below. Type the name of the function and pass in the 2 parameters to the function.
  • There is a comma between the 2 column names and not an ampersand.

After you click OK, the Query will show a new column, with each row returning the results of the fnCombine function.

  • Remove the ‘Name’ and ‘Folder Path’ columns as they are now no longer needed
  • When you click on the expand button (shown above), you will be given a choice of which columns from the new table are required. Select all columns and deselect the last option “Use original column name as prefix”.

After clicking OK, the query should look like the following, with all the data appended together.

  • Finally, click ‘Close & Load’ to load the results to Excel!