Final Friday Fix: August 2022 Challenge

On the final Friday of each month, we are going to set an Excel / Power BI challenge for you to puzzle over so that you can get your “Excel fix”.  Challenge your office colleagues to see who can solve the puzzle quickest.  There are no prizes at this stage: you are playing for bragging rights only!

The Challenge

This month, we have a Power Query challenge.  There are three [3] transactions files of different entities exported from an accounting system.  Our goal is to combine them all together into a master table.

However, we have some “issues”:

• they do not have the same number of columns; and
• some column names are a little bit different depending on which country the entities are located in.

You can download the challenge files here.

Therefore, this month’s challenge is to extract the files from a given folder, standardise the column names and append them together.  The result should look like the table generated at the bottom of the picture below:

As always, there are some conditions:

• it should be possible for more source files to be added to the folder in the future
• each file can have a different number of columns
• the entity name should be extracted to an addition column
• only some columns are needed, and are listed in the ColumnList table (below)
• GST and Tax are presumed to be the same column
• some columns need to be dynamically renamed such as Date and ‘Invoice Number’.  New names are specified in the column To of the table (below).

Sounds easy?  Then why not have a go?  We’ll publish one solution in Monday’s blog.  Have a great weekend in the meantime!