Power Query: Files for Today

Welcome to our Power Query blog. Today, I am going to select files using the date embedded in the file name.

I have a group of files created by my almost-legendary imaginary salespeople. I only need to upload those files that match today’s date.

I am going to import from a folder, which I first visited way back in Power Query: One Folder, One Query. In Excel, I first select the ‘Get & Transform’ section of the ‘Data’ tab. Here, I can choose to create a new query, and I select the ‘From Folder’ option on the dropdown from the ‘From File’ option:

I am prompted to enter or browse for my folder.

Having selected my folder, I click ‘OK’.

I can see a number of files in this folder, some of which apply to today (guess when I wrote this – 26/03/2019). There are a number of ways to get just those files that correspond to today’s date, and I will explore one of them. To start, I choose to transform my data using the ‘Transform Data’ tab (formerly known as ‘Edit’).

I am only interested in the first two columns: Content and Name, so I select these two columns whilst holding down the CTRL key and right-click to ‘Remove Other Columns’.

There are a number of ways I could use to extract the date; I am going to use the ‘Column From Examples’ functionality on the ‘Add Column’ tab:

After the first example, Power Query has filled in the transformation:

(for more on using Text.BetweenDelimiters please refer to Power Query: Sub Texting.

My next step will be to transform this into a date.  To do this, first I will need to get it into a format that Power Query will recognise.  This time, I add a column using ‘Custom Column’ and provide the M code. 

The M I have used is:

This essentially gets the portions of text that represent year, month and day using Text.Range, converts each portion to a number using Number.FromText, and then uses #date to convert the year, month and day to date format. 

Now I need to create the filter.  I am going to use an existing filter option to get the format of the M code, and make a tweak.

I have the option ‘In the Previous’, but not ‘In the Current’, so I will start off with ‘In the Previous’.

I choose dates ‘in the previous 1 day’.

This gives me yesterday’s file, but I can change the M code.  The code I have now is:

I am going to change this to use Date.IsInCurrentDay (which I think should be a standard filter option for dates!):

(For more on Date.IsIn() functionality, please refer to Power Query: Currently Dating.

This gives me the files that have today’s date on them, and I can then expand the available data should I wish. I rename my current Date column to File Date to avoid confusion.

When I use the icon next to the Content title, I am prompted to specify how to combine the files. I will use the defaults.

The data from the files that had today’s date in the title have been combined, and I can transform them as I wish.

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