Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I am trying to combine multiple pages from the UK coronavirus data link here for more information. I have both a function and table setup to try and extract the data from the API query. I am using the table to define which page each request should be refering to and the function to send the URL to the table for drilling down into the data.
Code for function:
(params as text) =>
let
source = Web.Contents("https://api.coronavirus.data.gov.uk/v1/data?" & "filters=areaType=utla&structure={""areaType"":""areaType"",""areaName"":""areaName"",""areaCode"":""areaCode"",""date"":""date"",""newCasesBySpecimenDate"":""newCasesBySpecimenDate"",""cumCasesBySpecimenDate"":""cumCasesBySpecimenDate""}&format=json&page=" & params)
in
source
Code for table:
let
Source = Json.Document(Web.Contents("https://api.coronavirus.data.gov.uk/v1/data?" & "filters=areaType=utla&structure={""areaType"":""areaType"",""areaName"":""areaName"",""areaCode"":""areaCode"",""date"":""date"",""newCasesBySpecimenDate"":""newCasesBySpecimenDate"",""cumCasesBySpecimenDate"":""cumCasesBySpecimenDate""}&format=json")),
pagination = Source[pagination],
totpages = pagination[last],
newvalue = Replacer.ReplaceText(totpages, "/v1/data?filters=areaType=utla&structure=%7B%22areaType%22:%22areaType%22,%22areaName%22:%22areaName%22,%22areaCode%22:%22areaCode%22,%22date%22:%22date%22,%22newCasesBySpecimenDate%22:%22newCasesBySpecimenDate%22,%22cumCasesBySpecimenDate%22:%22cumCasesBySpecimenDate%22%7D&format=json&page=", ""),
newvalue2 = Number.FromText(newvalue),
List = {1..newvalue2},
#"Converted to Table" = Table.FromList(List, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "List"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"List", type text}}),
#"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "Function", each Function([List])),
Function1 = #"Invoked Custom Function"{0}[Function], //this is where I think the issue is as this will only show the first page
#"Imported JSON" = Json.Document(Function1,65001),
data = #"Imported JSON"[data],
#"Converted to Table1" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"areaType", "areaName", "areaCode", "date", "newCasesBySpecimenDate", "cumCasesBySpecimenDate"}, {"Column1.areaType", "Column1.areaName", "Column1.areaCode", "Column1.date", "Column1.newCasesBySpecimenDate", "Column1.cumCasesBySpecimenDate"})
in
#"Expanded Column1"
The issue I am having is that only the first record (page 1) is showing in the output that I require and I want to extract all into one table!
Any help would be amazing!
Anthony
Solved! Go to Solution.
I think you're slighty overcomplicating this. Here's how I would approach it.
Function GetPage:
(Page) => let
URL = "https://api.coronavirus.data.gov.uk",
Query= "/v1/data?filters=areaType=utla&format=json&page=" & Text.From(Page) & "&structure={""areaType"":""areaType"",""areaName"":""areaName"",""areaCode"":""areaCode"",""date"":""date"",""newCasesBySpecimenDate"":""newCasesBySpecimenDate"",""cumCasesBySpecimenDate"":""cumCasesBySpecimenDate""}",
Source = Json.Document(Web.Contents(URL & Query))
in
Source
Main query:
let
Source = GetPage(1),
Total = Text.AfterDelimiter(Source[pagination][last],"=",4),
ToFetch = Table.FromList({1..Number.From(Total)}, Splitter.SplitByNothing(), {"Page"},null, ExtraValues.Error),
#"Invoked Custom Function" = Table.AddColumn(ToFetch, "Data", each GetPage([Page])[data]),
#"Expanded Data" = Table.ExpandListColumn(#"Invoked Custom Function", "Data"),
#"Expanded Data1" = Table.ExpandRecordColumn(#"Expanded Data", "Data", {"areaType", "areaName", "areaCode", "date", "newCasesBySpecimenDate", "cumCasesBySpecimenDate"}, {"areaType", "areaName", "areaCode", "date", "newCasesBySpecimenDate", "cumCasesBySpecimenDate"})
in
#"Expanded Data1"
This should do the trick
let
Source = GetPage(1),
Total = Text.AfterDelimiter(Source[pagination][last],"=",4),
ToFetch = Table.FromList({1..Number.From(Total)}, Splitter.SplitByNothing(), {"Page"},null, ExtraValues.Error),
#"Invoked Custom Function" = Table.AddColumn(ToFetch, "Data", each if [Page]=1 then Source[data] else GetPage([Page])[data]),
#"Expanded Data" = Table.ExpandListColumn(#"Invoked Custom Function", "Data"),
#"Expanded Data1" = Table.ExpandRecordColumn(#"Expanded Data", "Data", {"areaType", "areaName", "areaCode", "date", "newCasesBySpecimenDate", "cumCasesBySpecimenDate"}, {"areaType", "areaName", "areaCode", "date", "newCasesBySpecimenDate", "cumCasesBySpecimenDate"})
in
#"Expanded Data1"
Hi,
I am making a report on Covid data and using API (https://api.coronavirus.data.gov.uk/v1/data) to pull the data. The issue which I am facing that data with areatype =ulta is not showing the correct value but with areatype=nation the numbers are correct
As per the website https://coronavirus.data.gov.uk/developers-guide
for areatype = ulta i am using newCasesBySpecimenDate
and for areatype =nation newCasesByPublishDate
Can you please suggest what can be the issue.
Thanks
Hi, I am working on Covid19 Uk data, I have got the data from the API, numbers are not correct if I put areaType =ltla or ulta but for the nation is correct. I am using newCasesBySpecimenDate on 19/10/20 its showing 2212
data from https://coronavirus.data.gov.uk/cases
cases by Specimendate
19-10-2020 | 1,142 | 730 | 366 | 1 |
=2239
Do you have any suggestion?
Thanks
MB
I think you're slighty overcomplicating this. Here's how I would approach it.
Function GetPage:
(Page) => let
URL = "https://api.coronavirus.data.gov.uk",
Query= "/v1/data?filters=areaType=utla&format=json&page=" & Text.From(Page) & "&structure={""areaType"":""areaType"",""areaName"":""areaName"",""areaCode"":""areaCode"",""date"":""date"",""newCasesBySpecimenDate"":""newCasesBySpecimenDate"",""cumCasesBySpecimenDate"":""cumCasesBySpecimenDate""}",
Source = Json.Document(Web.Contents(URL & Query))
in
Source
Main query:
let
Source = GetPage(1),
Total = Text.AfterDelimiter(Source[pagination][last],"=",4),
ToFetch = Table.FromList({1..Number.From(Total)}, Splitter.SplitByNothing(), {"Page"},null, ExtraValues.Error),
#"Invoked Custom Function" = Table.AddColumn(ToFetch, "Data", each GetPage([Page])[data]),
#"Expanded Data" = Table.ExpandListColumn(#"Invoked Custom Function", "Data"),
#"Expanded Data1" = Table.ExpandRecordColumn(#"Expanded Data", "Data", {"areaType", "areaName", "areaCode", "date", "newCasesBySpecimenDate", "cumCasesBySpecimenDate"}, {"areaType", "areaName", "areaCode", "date", "newCasesBySpecimenDate", "cumCasesBySpecimenDate"})
in
#"Expanded Data1"
This seems to be doing the trick! I have tested by creating a custom column and removing duplciate values and all seems unique 🙂 thank you!
Sorry I wasn't clear enough. I fixed the double fetch of page 1, the resulting table was not affected.
Perfect! I am so gratful for this as I cna use the code for other issues I may have! Really appreciate it!!!!
Note that this solution fetches page 1 twice. It could be adjusted to only fetch pages 2 to x and re-use the page 1 source if that is a concern.
Is there any chance you could point me in the right direction? I guess I just need to pull page one then combine with the query?
This should do the trick
let
Source = GetPage(1),
Total = Text.AfterDelimiter(Source[pagination][last],"=",4),
ToFetch = Table.FromList({1..Number.From(Total)}, Splitter.SplitByNothing(), {"Page"},null, ExtraValues.Error),
#"Invoked Custom Function" = Table.AddColumn(ToFetch, "Data", each if [Page]=1 then Source[data] else GetPage([Page])[data]),
#"Expanded Data" = Table.ExpandListColumn(#"Invoked Custom Function", "Data"),
#"Expanded Data1" = Table.ExpandRecordColumn(#"Expanded Data", "Data", {"areaType", "areaName", "areaCode", "date", "newCasesBySpecimenDate", "cumCasesBySpecimenDate"}, {"areaType", "areaName", "areaCode", "date", "newCasesBySpecimenDate", "cumCasesBySpecimenDate"})
in
#"Expanded Data1"