Forum Discussion

Luis_RBDO's avatar
Luis_RBDO
Frequent Visitor
2 years ago

Connect to file with dynamic link embbed in a webpage

Hello everyone,   I have a complex challenge to solve.   I need to extract a list of companies, available in a file linked in public agency webpage. Instead of manually downloading the file from...
Need Help
Tips and Tricks
lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago

Make sure that the base URL returns valid HTML, or skip the connection test.

Luis_RBDO's avatar
Luis_RBDO
Frequent Visitor
2 years ago

Thank you for your help lbendlin but unfortunately it seems that this is something that it's not possible to do.

 

The base URL returns the webpage HTML source code, I'm not sure if this qualifies as valid but I have no issue scraping the file link on PBI desktop. It's not possible to check the box "Skip Connection Test" because the section "Data source credentials" is greyed out.


After further research, it seems that queries that use the "Web.BrowserContents" connector cannot be used for scheduled refresh. On the other hand, the connector "Web.Contents" works for other sources that I've implemented but it does not work in this case because it doesn't return the HTML webpage source code.

This issue as no solution.

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    2 years ago

    Your original post did not mention Web.BrowserContents.  Can you not see the required data when using Web.Contents?

    • Luis_RBDO's avatar
      Luis_RBDO
      Frequent Visitor
      2 years ago

      I mentioned it in the third paragraph of the original post.

      But it's easier to explain with real examples:

       

      The link to the pdf file is provided in the the body of website: https://www.ani.pt/pt/financiamento/incentivos-fiscais/selo-id-reconhecimento-de-idoneidade-na-pr%C3%A1tica-de-atividades-de-id/

       

       

       

      First, I scrap the website to obtain the file link with the following function, using the connector "Web.BrowserContents":

       

       

      let
    File_link = () => let
    #"HTML Code" = Web.BrowserContents("https://www.ani.pt/pt/financiamento/incentivos-fiscais/selo-id-reconhecimento-de-idoneidade-na-pr%C3%A1tica-de-atividades-de-id/"),
    #"Split Text" = Text.Split(#"HTML Code", "href="""),
    #"Converted to Table" = Table.FromList(#"Split Text", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Filtered = Table.SelectRows(#"Converted to Table", each Text.Contains([Column1], "lista-entidades-reconhecidas")),
    #"Extracted Text Before Delimiter" = Table.TransformColumns(Filtered, {{"Column1", each Text.BeforeDelimiter(_, """"), type text}}),
    #"Clean Address" = Table.TransformColumns(#"Extracted Text Before Delimiter", {{"Column1", each Text.TrimStart(_,"/"), type text}}),
    Source = #"Clean Address"{0}[Column1]
    in
        Source
in
    File_link

       

       

      Returning the dynamic component of the file link as a string: 

       

      Then, on a second query, I use the connector "Web.Contents" and supply it the static part of the link, "https://www.ani.pt/", and invoke the function in the relative path argument, to add the dynamic part:

      Source = Pdf.Tables(Web.Contents("https://www.ani.pt/",[RelativePath = #"Fetch File Address Query"]), [Implementation="1.3"]),

       

      I didn't manage to find a way to access the file link from the website body directly using "Web.Contents". But, if I copy/paste the file link and supply it as a static address it works and allows to schedule refresh. This solution is not dynamic and we will need to manually change the link every time there is a newer file published on the website, but since it's only once a year, it's perfectly acceptable.