Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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"
Solved! Go to 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.
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)
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 ...
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.
Thanks! Kicking myself I didn't notice the ..