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
Community Championor 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
Thank you, it works!!
I have another question. If yesterday's report is also not available, I want PBI to use whatever the latest report available. how to do that? Is it to create a loop keep on -1 from today's date?
- AlB
Hi Anonymous
You can try with a recursive function that will continue looking on previous days until what it reads is different from null:
let seek_latestFunc = (offset) => let temp_ = Excel.Workbook(Web.Contents("https://printersales.com:", [RelativePath="timeReportDaily_" & Date.ToText(Date.AddDays(Date.From(DateTime.LocalNow()), offset), "YYYYMMDD") & ".xlsx"]), null, true), output = if temp_ = null then seek_latestFunc(offset - 1) else temp_ in output, Source = seek_latestFunc(0) in #"Source"Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
I got an error, what could it be?
Expression.Error: The name 'seek_latestFunc' wasn't recognized. Make sure it's spelled correctly.
- AlB
Try adding an @ before the recursive call to the function:
let seek_latestFunc = (offset) => let temp_ = Excel.Workbook(Web.Contents("https://printersales.com:", [RelativePath="timeReportDaily_" & Date.ToText(Date.AddDays(Date.From(DateTime.LocalNow()), offset), "YYYYMMDD") & ".xlsx"]), null, true), output = if temp_ = null then @seek_latestFunc(offset - 1) else temp_ in output, Source = seek_latestFunc(0) in #"Source"Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
looks like @seek_latestFunc(offset-1) is not working, I deleted today's report to test if PBI will select yesterday's report but got an error below
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.
when i change Source = seek_latestFunc(-1) at the bottom, it works
Does @seek_latestFunc(offset-1) automatic get the latest report?
- AlB
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