Skip to main content
cancel
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

Reply
Andshepch
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

Andshepch_2-1682085238090.png

 

 

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

Andshepch_0-1682084994340.png

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

2 REPLIES 2
AlienSx
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. 

BA_Pete
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

 

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



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

Proud to be a Datanaut!




Helpful resources

Announcements
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