Forum Discussion

Mehay's avatar
Mehay
Helper I
6 years ago
Solved

Dynamic Date and element in Power BI URL

Hi everyone,    Plz help me on this.   1. I'm trying to format my URL in the way that it takes 2-3 days prior data I found out that this code (below) will work for today, but how do I change it ...
Need Help
Show and Tell
  • camargos88's avatar
    camargos88
    6 years ago

    Mehay ,

     

    Yes, it's like a macro in Excel.

    It keeps all the steps and apply them when the refresh happens.

  • camargos88's avatar
    camargos88
    6 years ago

    Hi Mehay ,

     

    We get this error if the page load is slower than the Power Query request, to fix it, use this code:

     

    let
    Source = Web.BrowserContents("https://www.apple.com/covid19/mobility", [WaitFor = [Timeout = #duration(0,0,0,3)]]),
    Extract = Html.Table(
                            Source, 
                            {
                                {"Link", "#download-card > div.download-button-container > a", each [Attributes][href]}
                            },
                             [RowSelector="#download-card > div.download-button-container"]
                        ){0}[Link],
    GetData = Csv.Document(Web.Contents(Extract),[Delimiter=",",Encoding=65001, QuoteStyle=QuoteStyle.None])
in
    GetData

     

    I just added a timer to delay the load of the page in Power Query.

Mehay's avatar
Mehay
Helper I
6 years ago

Just for better understnading.

 

Thats URL I'm trying to deal with, Apple Mobility Trand report:

 

https://covid19-static.cdn-apple.com/covid19-mobility-data/2009HotfixDev28/v3/en-us/applemobilitytrends-2020-06-09.csv

 

I highlighted, what changes with every new report published.

So how can I automize it, in order to get latest report to be uploaded into my Power BI?

 

Thanks in advance.

  • camargos88's avatar
    camargos88
    Community Champion
    6 years ago

    Hi Mehay ,

     

    Create this recursive function and run it with current date:

     

     

    (dt as date) as table =>
let
Url =
Web.Contents(
 "https://covid19-static.cdn-apple.com/covid19-mobility-data/2009HotfixDev28/v3/en-us/applemobilitytrends-"&Date.ToText(dt, "yyyy-MM-dd")&".csv"
,[ManualStatusHandling={404}]
),
Result =
if Value.Metadata(Url)[Response.Status] = 404 then
@Query1(Date.AddDays(dt, -1))
else Csv.Document(Url,[Delimiter=",",Encoding=65001, QuoteStyle=QuoteStyle.None])
in
Result

     

    Replace the Query1 reference with the function name.

     

     

    • Mehay's avatar
      Mehay
      Helper I
      6 years ago

      Hi camargos88 ,

       

      Indeed Invoked function is a cool one, i do personally like it.

      But in this case it won't make it automized the way i would make it in python let's say.

       

      Moreover., whenever you choose new date of daat to be uploaded, it would open as new Invoked Function every time.

      And that means I need to apply all my filters again and again, os is there a way so function wont create new one but will replace existing with all its filters?

       

      Sorry, im quite new in Power BI, and therefore, not that familiar with M language too.

       

      Thnaks.

      • camargos88's avatar
        camargos88
        Community Champion
        6 years ago

        Hi Mehay ,

         

        You just need to create a table with 1 valiue (current date using Date.From(DateTime.LocalNow()), use the function and expand the table.

         

        This recursive function will decrease the date and try to find a valid url.