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.

 

[elanc01]

Sorry for the confusion. I thought that was a starting point example. Attached is an image of a subset of my folder. I highlighted the files I need the filter to capture (most recent two dates/weeks). This example happens to be current and prior week, but that may not always be the case as I stated previously.

Post any code you have and then I can try to take it from there.

Sorry for the picture - I can’t log into Mr Excel from work - forums/boards are blocked.

 

[sandy666]

pic is not readable

M is as example

let
    Source = Folder.Files("path_to_folder"),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date created", type date}}),
    Sort = Table.Sort(#"Changed Type",{{"Date created", Order.Ascending}}),
    Filter = Table.SelectRows(Sort, each ([Extension] = ".xlsx")),
    Week = Table.AddColumn(Filter, "Week of Year", each Date.WeekOfYear([Date created]), Int64.Type),
    Group = Table.Group(Week, {"Week of Year"}, {{"Max", each List.Max([Date created]), type datetime}, {"Count", each _, type table}}),
    DateMaxN = Table.AddColumn(Group, "DateMaxN", each List.MaxN([Count][Date created],2)),
    ExpandD = Table.ExpandListColumn(DateMaxN, "DateMaxN"),
    ListName = Table.AddColumn(ExpandD, "Name", each List.LastN([Count][Name],2)),
    ExpandN = Table.ExpandListColumn(ListName, "Name"),
    TSC = Table.SelectColumns(ExpandN,{"Name", "DateMaxN", "Week of Year"})
in
    TSC

 

[elanc01]

It took me a while of experimenting but I finally got it....

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 latest2 = List.Min(List.MaxN(List.Distinct(#"Changed Type"[Date created]),2)) in each [Date created] >= latest2)
in
    #"Filtered Rows1"

I used List.Distinct to get all unique dates, which is the input to List.MaxN to get the two most recent dates, which is input to List.Min to get the second to last date. Then filter to all rows with a date >= to that date. This yields all the files from the most recent two dates. I'll probably tweak this to to use week numbers in the event the weekly files are ever delivered on separate days, but same concept. I'm not sure if there's an easier way to get the second to last of a set of values, but this worked.

 

[sandy666]

Glad it worked for you