CSV URLs produce Errors in a Table (but not on their own)

[TomTomTom]

The csv urls this query produces show as errors. But when I query them one by one, they can be queried just fine. Any ideas how to fix? Ideally I'll build queries that loop over urls with all monthname-years in them, so I definitely don't want to get at the one by one.

let
Source = Web.BrowserContents("https://digital.nhs.uk/data-and-information/publications/statistical/patients-registered-at-a-gp-pra..."),
#"Split Text" = Text.Split(Source, "https://files"),
#"Converted to Table" = Table.FromList(#"Split Text", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Filtered Rows" = Table.SelectRows(#"Converted to Table", each Text.StartsWith([Column1], ".digital.nhs.uk")),
#"Extracted Text Before Delimiter" = Table.TransformColumns(#"Filtered Rows", {{"Column1", each Text.BeforeDelimiter(_, """"), type text}}),
#"Added Custom" = Table.AddColumn(#"Extracted Text Before Delimiter", "Custom", each "https://files."),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Custom", type text}}),
#"Merged Columns" = Table.CombineColumns(#"Changed Type1",{"Custom", "Column1"},Combiner.CombineTextByDelimiter("", QuoteStyle.None),"Merged"),
#"Filtered Rows1" = Table.SelectRows(#"Merged Columns", each Text.EndsWith([Merged], ".csv")),
#"Trimmed Text" = Table.TransformColumns(#"Filtered Rows1",{{"Merged", Text.Trim, type text}}),
#"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Merged", Text.Clean, type text}}),
#"Added Custom1" = Table.AddColumn(#"Cleaned Text", "CSV", each Csv.Document(Web.Contents("[Merged]")))
in
#"Added Custom1"

[lbendlin]

How do you "loop over urls with all monthname-years" ?  The URLs seem to be using a different ID system

https://files.digital.nhs.uk/C4/3A7038/gp-reg-pat-prac-all.csv (May 2020)
https://files.digital.nhs.uk/BA/EEB621/gp-reg-pat-prac-all.csv (June 2020)

[TomTomTom]

The loop will be over the urls that look like those in source.

That's not the source of the error that I am stumped on.

The error comes at the end of the query ...

[v-eachen-msft · Accepted solution]

Hi @TomTomTom ,

At first, I found your "Cleaned Text" returns incorrect URLs. You could use "." to replace "..".

You need to remove double quotes in the last step "Added Custom1". "Merged" is already a text column.

each Csv.Document(Web.Contents([Merged])))

Here is the result.

[TomTomTom]

Thanks! Kicking myself I didn't notice the ..