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. 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

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

5 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    5 years ago

    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"

     

     

  • 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

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        CNENFRNL
        Community Champion
        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