Forum Discussion

Rebecca6634's avatar
Rebecca6634
Frequent Visitor
6 years ago

Importing a csv with dynamic url from a webpage

Hi

 

I'd like to set up a query that automatically extracts the most recent versions of these csv documents from the below webpages. The csvs change each week and I need the report to work with scheduled refresh.

 

I have previously set up a query to import a csv/xlxs using a dynamic date, but I wonder if there is a better way to do this? The NHS files in particular are tricky as other parts of the url string change randomly each week rather than just the date.

 

Can anyone advise if there is an easy way to achieve this? Thanks

 

https://www.ons.gov.uk/peoplepopulationandcommunity/healthandsocialcare/causesofdeath/datasets/deathregistrationsandoccurrencesbylocalauthorityandhealthboard

 

https://digital.nhs.uk/data-and-information/publications/statistical/mi-potential-covid-19-symptoms-reported-through-nhs-pathways-and-111-online/latest  (csv files are in the Resources section towards the bottom of the page)

Need Help

1 Reply

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

    Here's one way of approaching this

     

    let
    Source = Date.WeekOfYear(DateTime.FixedLocalNow()),
    Weeks = {Source-30..Source},
    #"Converted to Table" = Table.FromList(Weeks, Splitter.SplitByNothing(),  {"Week"},null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "URL", each try Excel.Workbook(Web.Contents("https://www.ons.gov.uk/file?uri=/peoplepopulationandcommunity/healthandsocialcare/causesofdeath/datasets/deathregistrationsandoccurrencesbylocalauthorityandhealthboard/2020/lahbtablesweek" & Text.From([Week]) & ".xlsx")) otherwise null)
in
    #"Added Custom"