Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Problem extracting data from JSON

Hey there,

 

I've been trying to paginate an API from World Bank, but it does not work. Basically, I list how many pages are to be listed in the final table (which by default loads only 50 rows), but as the custom function is invoked, the following error message pops up:

DataFormat.Error: We found an unexpected character in the JSON input.
Details:
    Value=<
    Position=0

 

Here is the link to the file.

 

Does anyone know what I'm doing wrong and how to make it work? 

Need Help
Show and Tell
Tips and Tricks
  • ThxAlot's avatar
    ThxAlot
    2 years ago

    Simple enough

    let
    Source = Xml.Tables(Web.Contents("http://api.worldbank.org/v2/country"))[#"Attribute:pages"]{0},
    Pages = Table.FromList({"1"..Source}, Splitter.SplitByNothing(), {"Page"}),
    #"API Call" = Table.AddColumn(Pages, "API Call", each Xml.Tables(Web.Contents("http://api.worldbank.org/v2/country?page="&[Page])))
in
    #"API Call"

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Thanks for your response, amitchandak . You were of great help as usual.

  • ThxAlot's avatar
    ThxAlot
    Super User
    2 years ago

    Simple enough

    let
    Source = Xml.Tables(Web.Contents("http://api.worldbank.org/v2/country"))[#"Attribute:pages"]{0},
    Pages = Table.FromList({"1"..Source}, Splitter.SplitByNothing(), {"Page"}),
    #"API Call" = Table.AddColumn(Pages, "API Call", each Xml.Tables(Web.Contents("http://api.worldbank.org/v2/country?page="&[Page])))
in
    #"API Call"
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Thanks a lot for your response, thxalot kkkk

       

      It really worked out.