Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Get data from a web

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....
Need Help
  • CNENFRNL's avatar
    CNENFRNL
    5 years ago

    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

CNENFRNL's avatar
CNENFRNL
Community Champion
5 years ago

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