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

Hi  camargos88,

 

You mean right here ? DId i get it right?

 

And that code will mean, it will take data aoutomatically as the latest one whenever i click on refresh right? So basically i wont need to type dates in Invoke Function anymore?

 

Modified fuction as per you prev message.

let
Source = #"Apple Mobility Trend"(Date.From(DateTime.LocalNow())),

 

Thanks in advance,

Mehdi

Mehay's avatar
Mehay
Helper I
6 years ago

and also this part in URL changes too:  "2009HotfixDev28/" now for 10 Jun data it shows to be 14 instead of 28.

 

Thanks, 

Mehdi

  • camargos88's avatar
    camargos88
    Community Champion
    6 years ago

    Mehay ,

     

    We can try another approach for this, scrap this page and get the link directly:

     

     

    let
    Source = Web.BrowserContents("https://www.apple.com/covid19/mobility"),
    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

     

     

     

    • Mehay's avatar
      Mehay
      Helper I
      6 years ago

      Dear camargos88,

       

      Appreciate for you help, first of all.

       

      Yes, your function basically worked fine, i just copied all codes given by you.

      The thing is if I dont change 28 in URL to 14, it would still stick to data untill 9 Jun.

      So only when i changed it to 14, it gave me needed data untill 10 Jun.

       

      I checked with old URL, and it's probably still saved within Apple database with the same old link but wont be avialable publically in the website. 

      The website would provide only lateast data.

      So I think that would be another problem with this formula.

       

      Just my thought, is it possible to have codes with that stick strictly to d-2, so 2 days before, e.i. today 12 Jun, so to get 10 Jun), and then for that middle number in the URL it will count from 0-100, and therefore, the system will try to figure out wich number should match with 10 Jun data.

       

      Thanks again.

      Mehdi

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi Mehdi,

         

        Struggling with the same code could you forward it to me please.

    • Mehay's avatar
      Mehay
      Helper I
      6 years ago

      the 2nd approach basically works well, it got exactly what I need.

       

      Just a quick question, will my all filters remain same even after new data uploads in the apple system?

      • camargos88's avatar
        camargos88
        Community Champion
        6 years ago

        Mehay ,

         

        Yes, it's like a macro in Excel.

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