Power Query: Files for Today
17 April 2019
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:
= Text.BetweenDelimiters([Name], "_", "_", 1, 0)
(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:
= #date(Number.FromText(Text.Range([Text Between Delimiters],4,4)),Number.FromText( Text.Range([Text Between Delimiters],2,2)),Number.FromText(Text.Range([Text Between Delimiters],0,2)))
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:
= Table.SelectRows(#"Added Custom", each Date.IsInPreviousNDays([Date], 1))
I am going to change this to use Date.IsInCurrentDay (which I think should be a standard filter option for dates!):
= Table.SelectRows(#"Added Custom", each Date.IsInCurrentDay([Date]))
(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!