Forum Discussion
Select today if not yesterday data source base on availability
or if you want to make it a bit more legible:
let temp_ = Excel.Workbook(Web.Contents("https://printersales.com:", [RelativePath="timeReportDaily_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true), Source = if temp_ = null then Excel.Workbook(Web.Contents("https://printersales.com:", [RelativePath="timeReportDaily_" & Date.ToText(Date.AddDays(Date.From(DateTime.LocalNow()), -1), "YYYYMMDD") & ".xlsx"]), null, true) else temp_ in #"Source"Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
Source = seek_latestFunc(-1) will looking at the report of yesterday's date. Source = seek_latestFunc(0) starts by looking at today's. In both cases the recursion continues looking at the previous day until the result of invokibg Excel.Workbook(Web.Contents(.... is not null
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
seek_latestFunc(0) was not working is what I meant, so i tried to troubleshoot by changing the number
I think the looping back is not working, once it detects today no report, it gives an error straightaway
DataSource.Error: The downloaded data is HTML, which isn't the expected type. The URL may be wrong or you might not have provided the right credentials to the server.
Then comparing it to null as you were doing initially is not sufficient. You need to change the code to capture the error and update the condition check
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
I tried to swap the condition below but still getting same error
output = if temp_ <> null then temp_ else @seek_latestFunc(offset-1)
Any help is appreciated!
Anonymous please refer to the attached PBIX, you need to adapt this to your scenario.
I don't have the pefect URL. So I used
https://www.boxofficemojo.com/year/2020/?ref_=bo_yl_table_1
where the bold portion is the dynamic value. Let's suppose you want to use this URL to access the data and you always want to capture the data that is for the latest year catalogued by boxofficemojo.
A. you can either create a year table {1990..2100} (date table in your case) and a custom function to get the data by writing a custom function which will return an error message when fail to access anything on the URL for example, anything>2021.
B. or you can access the latest year (date in your case) from the website through a query and pass on that value to the custom function to get data by that date only.
What you are currently doing is trying to pass on the DateTime.LocalNow() which is determined by you and see if html stucture contains that info. If you do excatly the reverse, somehow create a query to get the latest date from the html and pass on that value to B., then you can avoid a lot of guessing game.
I have demonstrated both A and B in the attached PBIX. B is better performance wise.
