cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ToffeeMarkWork
Regular Visitor

Get Data from web page using dynamic url from query

Hi -

1. My get data from web query #"releases.latest?" will return a table with 1 row and multiple columns (this query would not need to change) - 

2. One of the columns [releases.signatureFileUrl] will always contain the latest data download link

 

This query will return the webpage for the latest download link #Latest Download" = List.First(#"releases?latest"[releases.signatureFileUrl])

 

But if I try and use that = Web.Contents(#"Latest Download") I get a firewall error:

references other queries or steps, so it may not directly access a data source. Please rebuild this data combination

 

Does anyone know how to use the dynamic value to get data from the web using this query?

 

Thanks

Mark

 

1 ACCEPTED SOLUTION

Thanks Pete, your solution didn't work immediately for me but I was able to work through the errors and appear to resolved my issue with the below code (API key redacted):

 

let
Source = Json.Document(Web.Contents("https://isd.digital.nhs.uk/trud/api/v1/keys/ deocode/items/341/releases?latest")),
#"Converted to Table" = Table.FromRecords({Source}),
#"Expanded releases" = Table.ExpandListColumn(#"Converted to Table", "releases"),
#"Expanded releases1" = Table.ExpandRecordColumn(#"Expanded releases", "releases", {"id", "name", "releaseDate", "archiveFileUrl", "archiveFileName", "archiveFileSizeBytes", "archiveFileSha256", "archiveFileLastModifiedTimestamp", "checksumFileUrl", "checksumFileName", "checksumFileSizeBytes", "checksumFileLastModifiedTimestamp", "signatureFileUrl", "signatureFileName", "signatureFileSizeBytes", "signatureFileLastModifiedTimestamp", "publicKeyFileUrl", "publicKeyFileName", "publicKeyFileSizeBytes", "publicKeyId"}, {"releases.id", "releases.name", "releases.releaseDate", "releases.archiveFileUrl", "releases.archiveFileName", "releases.archiveFileSizeBytes", "releases.archiveFileSha256", "releases.archiveFileLastModifiedTimestamp", "releases.checksumFileUrl", "releases.checksumFileName", "releases.checksumFileSizeBytes", "releases.checksumFileLastModifiedTimestamp", "releases.signatureFileUrl", "releases.signatureFileName", "releases.signatureFileSizeBytes", "releases.signatureFileLastModifiedTimestamp", "releases.publicKeyFileUrl", "releases.publicKeyFileName", "releases.publicKeyFileSizeBytes", "releases.publicKeyId"}),
#"releases archiveFileUrl" = #"Expanded releases1"[releases.archiveFileUrl],
Custom2 = Lines.ToText(#"releases archiveFileUrl"),
Custom1 = Web.Contents(Custom2)

in
Custom1

 

This should work ok, I then need to extract the result from a zip which I can do.  Thanks for your help.

View solution in original post

2 REPLIES 2
BA_Pete
Super User
Super User

Hi Mark,

 

You need to put your file URL into a relative path argument so, instead of this:

Web.Contents(yourUrlParameter)

 

You'll need to do something like this:

Source = Web.Contents("https://theDomainOfYourUrl.com",
    [RelativePath = "/theRestOfYourUrlParameter"]),

 

Difficult to give an exact answer without knowing what file type you're actually trying to grab, but this RelativePath principle is the way to avoid the FormulaFirewall in this scenario.

 

Pete



Now accepting Kudos! If my post helped you, why not give it a thumbs-up?

Proud to be a Datanaut!




Thanks Pete, your solution didn't work immediately for me but I was able to work through the errors and appear to resolved my issue with the below code (API key redacted):

 

let
Source = Json.Document(Web.Contents("https://isd.digital.nhs.uk/trud/api/v1/keys/ deocode/items/341/releases?latest")),
#"Converted to Table" = Table.FromRecords({Source}),
#"Expanded releases" = Table.ExpandListColumn(#"Converted to Table", "releases"),
#"Expanded releases1" = Table.ExpandRecordColumn(#"Expanded releases", "releases", {"id", "name", "releaseDate", "archiveFileUrl", "archiveFileName", "archiveFileSizeBytes", "archiveFileSha256", "archiveFileLastModifiedTimestamp", "checksumFileUrl", "checksumFileName", "checksumFileSizeBytes", "checksumFileLastModifiedTimestamp", "signatureFileUrl", "signatureFileName", "signatureFileSizeBytes", "signatureFileLastModifiedTimestamp", "publicKeyFileUrl", "publicKeyFileName", "publicKeyFileSizeBytes", "publicKeyId"}, {"releases.id", "releases.name", "releases.releaseDate", "releases.archiveFileUrl", "releases.archiveFileName", "releases.archiveFileSizeBytes", "releases.archiveFileSha256", "releases.archiveFileLastModifiedTimestamp", "releases.checksumFileUrl", "releases.checksumFileName", "releases.checksumFileSizeBytes", "releases.checksumFileLastModifiedTimestamp", "releases.signatureFileUrl", "releases.signatureFileName", "releases.signatureFileSizeBytes", "releases.signatureFileLastModifiedTimestamp", "releases.publicKeyFileUrl", "releases.publicKeyFileName", "releases.publicKeyFileSizeBytes", "releases.publicKeyId"}),
#"releases archiveFileUrl" = #"Expanded releases1"[releases.archiveFileUrl],
Custom2 = Lines.ToText(#"releases archiveFileUrl"),
Custom1 = Web.Contents(Custom2)

in
Custom1

 

This should work ok, I then need to extract the result from a zip which I can do.  Thanks for your help.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors