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 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"

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 

     

12 Replies

  • 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?