Get files from folder - filter to files to most recent create date, but the most recent two dates.

[elanc01]

Hello,

I'm somewhat new to the Power Query/Power Pivot world. I have a situation where I'm creating a report and need to import from a folder, for a set of four files, the most recent version of each file and the previous version. Any time there is a new version of the files they will all have the same created date. However, the most recent files may not be in the current week or month the report will be run and the period of time since the previous version of the files is not always on a consistent basis (i.e. should be weekly, but not always so). Therefore, I wanted to create code that could dynamically determine the two most recent created dates. I was hoping to somehow leverage List.LastN to filter down to the last two created dates. I converted the created dates to "date" type to get rid of the creation time complications. I started with a date filter of "is latest" hoping to be able to modify that code, but I just can't figure it out. How would I go about getting all the files for the two most recent created dates?

Below is the code from the advanced editor. I'd like to modify or replace the #"Filtered Rows1" line... Unfortunately it wasn't as easy as just replacing List.Max with a List.LastN(#"Changed Type"[Date created],2)

let
    Source = Folder.Files("L:\Corrective Actions\New Item Review\New Item Lists"),
    #"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Folder Path], "Source Reports") and Text.Contains([Folder Path], Text.From(Date.Year(DateTime.LocalNow())))),
    #"Changed Type" = Table.TransformColumnTypes(#"Filtered Rows",{{"Date Created", type date}}),
    #"Filtered Rows1" = Table.SelectRows(#"Changed Type", let Latest = List.Max(#"Changed Type"[Date created]) in each [Date created] = latest)
in
    #"Filtered Rows1"

Thanks for any help or insight.

 

[sandy666]

maybe try from date modified (time)

 

[sandy666]

eg.
LN = Table.LastN(Table.Sort(Source,{{"Date modified", Order.Ascending}}), 2)

 

[elanc01]

Thanks. The files are in a shared network folder at work so date created would be a little less risky for my purpose - in case someone else does a save which changes date modified . Regardless of which date field is used, modeling from what you did above, if I get rid of the time component and just use date, would Table.LastN,2 only result in two rows? In my scenario there are multiple files for each date. I need two sets of files, all from the most recent date and whatever is the prior date. In the example above my desired result would be all the files because there are only two dates. If for example, on 16/07/2020 another set of files is added, then my desired result would be all the files from 16/07/2020 and 15/07/2020. The files from 14/07/2020 should exclude because they are not from the two most recent dates.

 

[sandy666]

what if there is 20 000 files with the same date created without time? (it's possible)
you cannot identify files from morning vs evening but values there can be different

 

[elanc01]

The folder is a storage folder for a set of "weekly" reports. They are run from four different admin systems so can't be merged at creation... I do other things with the files aside from merge - comparison over previous week, etc. I was previously doing filter for "is in current week or is in prior week". However, there is sometimes inconsistency in the frequency of the reports (i.e. a week is skipped) in which case I would have to do my process manually because the "is prior week" doesn't work in that case. I'm trying to make the selection more dynamic.

 

[elanc01]

So volume is never more than eight files in total.

 

[sandy666]

something like this?

 

[elanc01]

Yes, that is it, except each week all the files would be the same created date. Based on this example I would need to filter to weeks 26 & 27. Whatever value is leveraged (date or week number) I need to get the the most recent two dates (or two highest values in a week number context). My ultimate goal is to get my weekly process work even if files weren't produced in one week or if I don't happen to run my report in the same week the files are released - that is where the "in current week or in prior week" filter I was using can break down. So trying to get to something more dynamic.

 

[sandy666]

sorry, but i don't have proper example. you need to test it yourself
i can share M if you want