Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
athornton
Frequent Visitor

Pagination - UK COVID-19

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

2 ACCEPTED SOLUTIONS
lbendlin
Super User
Super User

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"

 

View solution in original post

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"

 

View solution in original post

9 REPLIES 9
mb0307
Responsive Resident
Responsive Resident

Hi,

 @lbendlin 

 
 

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

 

mb0307
Responsive Resident
Responsive Resident

@athornton,

 

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-20201,1427303661

 =2239

Do you have any suggestion?

 

Thanks

MB

 

lbendlin
Super User
Super User

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"

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors