Power Query - next file if expected file missing

[Anonymous]

Hi all,

I have a folder in Sharepoint where a flat Excel file is (or should be) uploaded every day, with the date contained in the file name e.g. 2023-04-21.xlsx, so I can extract the date from the file name like this

What I am trying to do is get the file that matches the month end account date - which is defined in a table of dates that I have

This is straighforward to do with a merge based on the date, as long as all the files are in the source folder, but what I would like to do is : if the file for the exact date that I need e.g. 06/04/2023 is not there in the source folder, then get the next date in the source folder after it e.g. 10/04/2023.

I have not been successful so far, but I am sure it is possible, so any pointers woudl be greatly appreciated.

Thank you

Andrew

[AlienSx]

Hi, @Anonymous 

List.PositionOf(list_of_dates, your_date, Occurrence.First, (a, b) => a >= b)

gives you a position of the date you are lookind for in the list of dates providing that the list is sorted accordingly. If no date is found then -1 is returned. 

[BA_Pete]

Hi Andrew,

I can't give an exact answer without seeing exactly your tables/structure etc, but I think the general principle to filter your files table would be to do a two-stage process, something like this:

First filter - Remove any files that are before the month end date

Table.SelectRows(
    previousStepName,
    each [#"Custom - Copy"] >= [Date]
)

Second filter - Get the earliest date from this reduced selection

Table.SelectRows(
    previousStepName,
    each [#"Custom - Copy"] = List.Min(previousStepName[#"Custom - Copy"])
)

You could probably apply these to a single step, but for clarity's sake, this is the principle.

Example query, where I assume the period date is applied to a column in the table:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUNzDRNzIwMlbSUTIwg3NidYByRnjkjPHImeCRM8UjZ45HzgKHXCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Custom - Copy" = _t, Date = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Custom - Copy", type date}, {"Date", type date}}),

    filterGreaterThan = Table.SelectRows(chgTypes, each [#"Custom - Copy"] >= [Date]),
    filterMinAvailable = Table.SelectRows(filterGreaterThan, each [#"Custom - Copy"] = List.Min(filterGreaterThan[#"Custom - Copy"]))

in
    filterMinAvailable

Pete