Power Query: Flexible Appending – Part 1

Welcome to our Power Query blog.  This week, I am combining data from a folder and using a translation table to create column names.

This week, I have three [3] Excel workbooks, each with some accounting data.  The first file looks like this:

The second file looks like this:

The final file looks like this:

There are clearly some differences between the files.  The column headings vary by country and the first file has an extra column.

My goal is to get this data into the same table.  For the purposes of this example, I am not required to convert the figures to the same currency.  However, I do need to allow for more files appearing from other countries.  The files are held in one folder.  I have a translation table Column List in a separate Excel workbook (shown below) to help me determine the column names in the final table.  No other columns are required apart from the ‘entity’ name (e.g. ‘Entity 1’).

I start in the workbook containing table ColumnList (the table shown above).  Whilst in a cell in the table, I go to the ‘Data’ tab, and choose ‘From Table/Range’ in the ‘Get & Transform Data’ section.  I take the default settings and indicate that my table has headers.  This gives me the query ColumnList:

To make my solution flexible, I create a new parameter called FolderPath.  The ‘Current Value’ of the parameter is the folder path containing the files that I am going to append. 

I create a new query from the ‘New Source’ option in the ‘New Query’ section of the Home tab.  I select File, then Folder.

I navigate to the folder containing the files and click OK:

I choose to ‘Transform Data’.  The path on the Formula bar then needs to be replaced with the parameter I created.

Next, I click the ‘Combine Files’ button next to the Content column to start combining files.

I select the Transactions sheet on the pop-up dialog and click OK.  Power Query will automatically generate some new queries.  Due to the complexity of the source files, I need to cleanse data from the Transform Sample File query before continuing with the main Source query.  I start by removing the ‘Promoted Headers’ step that has been automatically generated, as we will need to remove rows before this step. 

I will be extracting the entity name from the header.  For now, I insert a blank step which I call ‘Base’ by clicking the fx symbol next to the Formula bar.  I will return to this step.

I clean the sample file by removing the top four [4] rows using ‘Remove Rows’ then ‘Remove Top Rows’ from the ‘Reduce Rows’ section of the Home tab.  I then promote the headers, using ‘Use First Row as Headers’ in the Transform section of the Home tab.  I also remove the autogenerated ‘Changed Type’ step by clicking on the ‘X’ next to the step.

I can remove the row containing the totals by filtering out null values from the Account Code column.

Next time, I will standardise the column names.

Come back next time for more ways to use Power Query!