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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/124/image1.png/e774d10cbbb9450fc45efbe51abdf434.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/124/image2.png/f32e5a15e2cf9c3e4d2d058458ce054d.jpg)
I am prompted to enter or browse for my folder.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/124/image3.png/f1140ff857fc3b6f5f97a6a24f4a6fc7.jpg)
Having selected my folder, I click ‘OK’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/124/image4.png/72aa864d2854c6fefb1083fba0ab5792.jpg)
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’).
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/124/image5.png/36776d1da4d05b45bb5a5d09375f407c.jpg)
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’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/124/image6.png/23912d3b1671861e02bebcd5183f1607.jpg)
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:
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/124/image7.png/6f49c288a0d88a66b427eaf4ece923d6.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/124/image8.png/b9ee28d90e6b5bc92ea4aeafdad51628.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/124/image9.png/0485ccbc83bdeec1d741bad442a1ea5f.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/124/image10.png/daf8c4f0259ce428269c0d3d4badd32b.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/124/image11.png/22c6daeb82d7d69ac88f878227e04b28.jpg)
I have the option ‘In the Previous’, but not ‘In the Current’, so I will start off with ‘In the Previous’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/124/image12.png/a1537847463e660a31158c8032525438.jpg)
I choose dates ‘in the previous 1 day’.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/124/image13.png/917da985be13220165c8d2823e95344f.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/124/image14.png/8c3be7af9f73d031acae69ed85a2e148.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/124/image15.png/e63c0a4c21afc9afb438aacc09a59317.jpg)
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.
![](http://sumproduct-4634.kxcdn.com/img/containers/main/blog-pictures/2019/power-query/124/image16.png/d082e3477129350b8a2a589156028e63.jpg)
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!