Forum Discussion

ToffeeMarkWork's avatar
ToffeeMarkWork
Frequent Visitor
3 years ago
Solved

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]...
Need Help
  • ToffeeMarkWork's avatar
    ToffeeMarkWork
    3 years ago

    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.

BA_Pete's avatar
BA_Pete
Super User
3 years ago

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

  • ToffeeMarkWork's avatar
    ToffeeMarkWork
    Frequent Visitor
    3 years ago

    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.