Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Select today if not yesterday data source base on availability

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 inser...
Need Help
  • AlB's avatar
    AlB
    6 years ago

    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 

     

AlB's avatar
AlB
Community Champion
6 years ago

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's avatar
    AlB
    Community Champion
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        AlB
        Community Champion
        6 years ago

        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