Always select and edit most recent file with standardized naming convention in folder

[Anonymous]

Hey guys

This is my first post on these forums so bear with me. I've started a new job four weeks ago as an analyst fresh out of university and basically my boss told me to go get good at Power BI. I've been trying to recreate and automate a report that was previously always made with excel (through copying and pasting new data) in our company. The situation is as follows:

1) Every day, a file with updated data gets dumped into a certain folder

2) This folder is dated and placed in an overarching folder (think filepath "Main Folder\2020\january\YY MM DD ALL.xlsx")

3) In this overarching folder (let's call it "Main Folder"), other data files get dumped that do not have anything to do with my report.

4) So here we go. I go into Power Query and I want to automatically select the most recent of my relevant files.

5) I sort descending on date created, however this sometimes brings up other files created in the hours after my wanted data file

6) So, I filter on "containing 'ALL'" as my wanted data file contains this word.

7) So now it is simply selecting the top most file and importing it to edit it further.

😎 However, the day after I want to automatically refresh my query by letting power query select the newest data file and apply the same transformations that I specified (think promoting headers, removing columns, filters useless rows) so that my visualisations update automatically also.

9) Now here's the problem. Even if I open my query and refresh it today, and there is a new file in the folder specified (the new YY MM DD ALL.xlsx for today so to say), power query will not grab that file but still use the file I manually specified today (i.e., nothing changes). Does anyone know a solution for grabbing the newest file every day? I thought about using a wildcard in the advanced editor to change YY MM DD ALL.xlsx to * * * ALL.xlsx but it seems this is not possible. I think the problem lies with the fact that the file name is hardcored into the advanced editor code in two lines (when importing) making it always select that file specifically. Hereunder in the spoiler you can find the code in my advanced editor window for this query. If you have any further questions or want me clarify or specify something, I remain at your disposal. Greetings

[danextian]

Hi @Anonymous ,

Open advanced editor and modify your code a bit to something like this:

let
    CurrentDate = Date.From(DateTime.FixedLocalNow()),
    MyPath = "<path with trailing backslash>",
    Filename = Text.End( Text.From ( Date.Year ( CurrentDate ) ), 2) 
             & " " & Text.PadStart( Text.From ( Date.Month ( CurrentDate ) ), 2, "0")
             & " " & Text.PadStart( Text.From ( Date.Day ( CurrentDate ) ), 2, "0")             
             & " ALL.xlsx",
    MyFile = MyPath & Filename,
    Source = Excel.Workbook(File.Contents(MyFile), null, true)
--your additional codes---

What this does is dynamically determine the filename based on the current system date in YY MM DD format and then append " ALL.xlsx".  

The code below utilizes the get data from folder function. It sorts the files in descending order by modified date and then grabs the top most file. Since the expected data is from Excel, thus Excel.Workbook(Content) is used. From here on, you may select which worksheet/named range/table (displayed as data type table) you want to import.

let
    Source = Folder.Files("<path to files>"),
    #"Sorted Rows" = Table.Buffer(Table.Sort(Source,{{"Date modified", Order.Descending}})),
    Content = #"Sorted Rows"{0}[Content],
    #"Imported Excel" = Excel.Workbook(Content)
in
    #"Imported Excel"

[v-gizhi-msft · Accepted solution]

Hi,

According to your description, please try to change your Excel file path with current day.

I create a table to test and the Query Editor shows:

And the file name is linked to the current day:

Here is my test pbix file:

pbix 

Hope this helps.

Best Regards,

Giotto Zhi