Hi Oscar,
Okay so in creating a mock-up, it actually took fewer steps… let me walk you through it.
As I understood it the goal was to get the most recent file from a folder. So I created a query using the Get data from Folder option and renamed this FolderContents.
Now it contains a datetime colum called Date modified, I’m going to leave that as it is because time can be important in the sort order. I’ll add a separate Date column with the M code depicted in the formula bar. In M type datetime and type date are not equal.
The FolderContents is our base query and I’m not going to Duplicate but Reference it by right clicking on the query name and selecting Reference. Rename it Today, sort the Date Modified column descending.
… and only keep the top1 row.
For the next query we need to do some ground work first. Create a new, from Blank Query
Enter this M code and rename it ListFileDates
Repeat the new from Blank Query step, enter this M code and rename it LastDate
the {0} at the end is important because it allows you to access the first Value in the list.
As an alternative to the LastDate M code above you could also reference today’s date, like so:
Date.From(DateTime.FixedLocalNow())
So the LastYear query… start by making another Reference to the FolderContents, sort the Date Modified descending and place a filter on the Date column (select equals and enter a date than adjust it according to the M code depicted below - again keep the top1 row.
Now I only created one Parameter for the last query.
And here’s how the field values are defined for this Parameter called SelectDateTypeText
So we can now create our last Reference to the FolderContents query
again sort the Date modified descending and place a filter on the Date column, adjust the M code like so
Think this is it - here’s my mock-up file. I hope this is helpful.
eDNA - Irregular Extract Files & Time Intelligence.pbix (146.3 KB)