Get data from a web

[Anonymous]

Hi,

Could anybody help me to get the number of pages please? I think my problem is the condition to stop "each Table.IsEmpty(Page)," The call to this page has 1131 pages, and 1132 gives an error. Thanks.

let
GetData =
(PagePath as number) =>
let
Source =
Web.Contents("https://gtr.ukri.org:443/gtr/api/funds?p="&PagePath&"&s=100"
)
in
Source,
Source =
List.Generate(
() =>
[
page = 0,
Page = GetData(page)
],
each Table.IsEmpty(Page),
each
[
page = [page] + 1,
Page = GetData(page)
]
)
in
Source

[CNENFRNL]

@Anonymous , interestingly, I tried the assigned URL by chance, it returns

Thus, page parameter should range from 1 to 1131 rather than from 0.

I think the function should be defined as follows in order to scrape valid tables

GetData = (PagePath as number) =>
    let
        Source = Xml.Tables(Web.Contents("https://gtr.ukri.org:443/gtr/api/funds?p=" & Text.From(PagePath) & "&s=100")){[Name="fund"]}[Table]
    in
        Source,

In addition, List.Generate() can be replaced by List.Accumulate().

let
    GetData = (PagePath as number) =>
        let
            Source = Xml.Tables(Web.Contents("https://gtr.ukri.org:443/gtr/api/funds?p=" & Text.From(PagePath) & "&s=100")){[Name="fund"]}[Table]
        in
            Source,

    Source = List.Accumulate({1..1131}, {}, (s,c) => s & {GetData(c)}),
    Combination = Table.Combine(Source)
in
    Combination

[Anonymous]

Hi,

Thanks for this. Is that possible to work with List Generate? With List.Acumulate I will have to update {1..1131} as the website updates constantly with new content. That's why I sued ListGenerate, so I get the list of pages. So, I've updated the code with your suggestion, but I think the problem comes with the condition to stop [Page] = Null. 

thanks

let
GetData =
(PagePath as number) =>
let
Source = Xml.Tables(
Web.Contents("https://gtr.ukri.org:443/gtr/api/funds?p="&Text.From(PagePath)&"&s=100"),
{[Name="Fund"]}[Table]
)
in
Source,
Source =
List.Generate(
() =>
[
page = 1,
Page = GetData(page)
],
each [Page] = null,
each
[
page = [page] + 1,
Page = GetData(page)
]
)
in
Source

[CNENFRNL · Accepted solution]

@Anonymous , you might want to try a complete procedure like this if you insist on List.Generate().

let
    GetData = (PagePath as number) =>
        Xml.Tables(Web.Contents("https://gtr.ukri.org:443/gtr/api/funds?p="&Text.From(PagePath)&"&s=100")){[Name="fund"]}[Table],

    Source = List.Generate(
        () => [page = 1, Page = GetData(page)],
        each not (try [Page])[HasError],
        each [page = [page] + 1, Page = GetData(page)],
        each [Page]
    ),
    Dataset = Table.Combine(Source)
in
    Dataset

the condition to contiue the loop is a bit tricky,

each not (try [Page])[HasError]

Here's a detailed reference to the syntax of try statement in M language,

https://bengribaudo.com/blog/2020/01/15/4883/power-query-m-primer-part-15-error-handling

[Anonymous]

Thanks. It worked!

However, I changed the website (bold) to grab data from other API (same website but different APIs depending on what info I want to grab). This also happens with other path. Both give "This table is empty". 

If this helps, this is the website https://gtr.ukri.org/resources/api.html, and these the endpoints http://gtr.ukri.org/gtr/api/examples 

Thanks.

let
GetData = (PagePath as number) =>
Xml.Tables(Web.Contents("https://gtr.ukri.org:443/gtr/api/projects?p="&Text.From(PagePath)&"&s=100")){[Name="fund"]}[Table],

Source = List.Generate(
() => [page = 1, Page = GetData(page)],
each not (try [Page])[HasError],
each [page = [page] + 1, Page = GetData(page)],
each [Page]
),
Dataset = Table.Combine(Source)
in
Dataset

[CNENFRNL]

Hi, @Anonymous 

As M language is extremely strick on data type, this expression

"https://gtr.ukri.org:443/gtr/api/funds?p="&PagePath&"&s=100"

throws error when a PagePath (as number) is passed to it.

You might want to try

https://gtr.ukri.org:443/gtr/api/funds?p=" & Text.From(PagePath) & "&s=100"