Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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
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.
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
Proud to be a Datanaut!