Select today if not yesterday data source base on availability

[Anonymous]

Hi,

I wanted to schedule my PBI report using a data source with today's date and if today's report is not available yet, I want PBI to choose yesterday's report. Can anyone help? Where do I insert if then else? Or should I use try otherwise? Below is just an example to start with

let

if
Source = Excel.Workbook(Web.Contents("https://printersales.com:", [RelativePath="timeReportDaily_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true) = null
then
Source = Excel.Workbook(Web.Contents("https://printersales.com:", [RelativePath="timeReportDaily_" & Date.ToText(Date.AddDays(Date.From(DateTime.LocalNow()), -1), "YYYYMMDD") & ".xlsx"]), null, true),
else
Source = Excel.Workbook(Web.Contents("https://printersales.com:", [RelativePath="timeReportDaily_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true),

in
#"Source"

[AlB]

Hi @Anonymous 

Try this, a small variation on what you already have:

let
Source =
if
 Excel.Workbook(Web.Contents("https://printersales.com:", [RelativePath="timeReportDaily_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true) = 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
Excel.Workbook(Web.Contents("https://printersales.com:", [RelativePath="timeReportDaily_" & Date.ToText(Date.From(DateTime.LocalNow()), "YYYYMMDD") & ".xlsx"]), null, true)

in
#"Source"

Please mark the question solved when done and consider giving kudos if posts are helpful.

 Cheers 

[AlB · Accepted solution]

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 

[Anonymous]

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 

[Anonymous]

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 

[Anonymous]

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.

[AlB]

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 

[Anonymous]

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!

[smpa01]

@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.

[Anonymous]

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