Connect to file with dynamic link embbed in a webpage

Question

Hello everyone,&nbsp;I have a complex challenge to solve.&nbsp;I need to extract a list of companies, available in a file linked in public agency webpage.Instead of manually downloading the file from the website and importing to Power BI, I was looking for a way to automatically retrieve it from the webpage using power query.This was possible by right clicking the file link in the webpage, copying the address and connecting&nbsp;to it using the power query web connector.&nbsp;The problem is that, when this agency updates the file once a year, the file address changes. They add the update date to the link, example https://www.agency_webpage.pt/list-of-companies_22_02_2024.pdf. They also don't update it on the same day and month each year, so next year the link could be&nbsp;https://www.agency_webpage.pt/list-of-companies_18_01_2025.pdf, making it impossible&nbsp;to dynamically change the date on the file address using the web connector parameter [RelativePath], like:&nbsp;Source = Pdf.Tables(Web.Contents("https://www.agency_webpage.pt/list-of-companies_22_02_",
[RelativePath = Number.ToText(Date.Year(DateTime.Date(DateTime.LocalNow())))]),
[Implementation="1.3"])&nbsp;I created a query to scrap the file link from the webpage html code (fetching it from a href line code) using the Web.BrowserContents connector, that returns a string with the file link, #"Fetch File Address Query". Then I created a second query to extract the file, using the Web.Contents connector, with the agency website address as the first parameter and referenced the #"Fetch File Address Query"&nbsp;query in the parameter [RelativePath], like so:Source = Pdf.Tables(Web.Contents("https://www.agency_webpage.pt/",[RelativePath = #"Fetch File Address Query"]), [Implementation="1.3"]),&nbsp;This solution returned the error: "Formula.Firewall: Query references other queries, so it may not directly access a data source.".&nbsp;I've tried changing the&nbsp;#"Fetch File Address Query"&nbsp; to a&nbsp;function and this way it worked on the Desktop. I'm able to apply and manually refresh the report in Power BI desktop. But after publishing to Power BI Service, it's impossible to manually refresh or schedule refresh in the service because it returns an error "Scheduled refresh is disabled because at least one data source is missing credentials." and the option to manage the credentials is greyed out:&nbsp;After extensive research, I didn't find any other way to create a query that could connect to a file with a dynamic address, located in a webpage, that worked properly in power bi service.&nbsp;Has anyone had a similar situation and was able to find a solution?&nbsp;

luis_rbdo · Answer

I mentioned it in the third paragraph of the original post.But it's easier to explain with real examples:&nbsp;The link to the pdf file is provided in the the body of website:&nbsp;https://www.ani.pt/pt/financiamento/incentivos-fiscais/selo-id-reconhecimento-de-idoneidade-na-pr%C3%A1tica-de-atividades-de-id/&nbsp;&nbsp;&nbsp;First, I scrap the website to obtain the file link with the following function, using the connector "Web.BrowserContents":&nbsp;&nbsp;let
    File_link = () =&gt; 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&nbsp;&nbsp;Returning the dynamic component of the file link as a string:&nbsp;&nbsp;Then, on a second query, I use the connector "Web.Contents" and supply it the static part of the link, "https://www.ani.pt/",&nbsp;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"]),&nbsp;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&nbsp;acceptable.

lbendlin · Answer

That's a very common error pattern.&nbsp; your RelativePath must include the&nbsp;
list-of-companies_22_02_
bit, or whatever the new name is. If you want to supply the Relative Path dynamically then that must happen inside the same query partition.
&nbsp;
&nbsp;

luis_rbdo · Answer

I've created a function to fetch from the website and return as a string the bit that changes every year . When I call the function on the Relative Path, it works on Power BI Desktop but not on the service. The "Data source credentials" section greys out, making it impossible to configure.

lbendlin · Answer

You are trying to automate something that changes once a year?

luis_rbdo · Answer

It's not necessary in this case, it's a challenge to test what is possible to do with querying web sources to use in&nbsp; similar&nbsp;situations in the future.

Forum Discussion

Connect to file with dynamic link embbed in a webpage

8 Replies

Related content

Dynamic ODBC Connection.

Connectivity Issue : Power BI to dynamics 365 (Dataverse)

Copy Activity Dynamic Source Connection

Dynamic Connection To Sources

CANNOT connect to Dynamics 365 Business Central