Loading the latest version of a file

Overview

Sometimes, you may only want to see the latest version of a file. For example, if you have a transactional file that grows in length each time, or a dimension/lookup table (such as Customers) that can change slowly over time.

This article explains how to load the latest version of such files.

Load from the Folder, not File

The solution to this problem is to load the file from a folder and not directly from the file itself. Thus, you would:

1. Need to ensure each file includes a unique name e.g. ‘myFile-YYYYMMDD.xlsx’
2. Place the file into a standard folder
3. Sort the folder in descending order based on creation date
4. Select the first file from the list of files

The above approach should just load the latest version of the file, while keeping an archive history of all files in the same folder.

In this example, we have a file that each day keeps growing. For example, on the 18th October, the file looks like this (containing 1 day of data):

On the 19th of October, the file looks like this (containing 2 days of data):

Steps

1. Load the folder into Power Query

• To start, load the folder into Power Query by clicking on Get Data ➔ From File ➔ From Folder

You should get a window that looks like this:

• Click on the Transform Data button

2. Extract the data

• Next, sort the ‘Date created’ column, into descending order

• Click the fx icon, and enter the following code:

   = Excel.Workbook(#"Sorted Rows"{0}[Content])

• Rename the step to ‘SelectFirstRow’ (as shown below)

• Click the text called ‘Table’, in the ‘Data’ column (shown above)
• Then promote the first row to headers

The data should load as follows, getting to our desired end-result!