Importing a csv with dynamic url from a webpage

[Rebecca6634]

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/death...

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

[lbendlin]

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"