Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Advocate II
Advocate II

Power Query - next file if expected file missing

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



Super User
Super User

Hi, @Andshepch 

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. 

Super User
Super User

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


    each [#"Custom - Copy"] >= [Date]



Second filter - Get the earliest date from this reduced selection


    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:


    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"]))





Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!

Helpful resources

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors