Forum Discussion
how to fully load data from limited HTTP API?
hi dkay84.
I read the link twice, tested both scripts in it, but they simply does not work.
A simple copy+paste shows error on the second line: missing Eof "GetUserDetail = (Path)=>".
what does that means?
I am new to M language and I cannot find the error on the script.
please, I still need help.
I tried to modify the script from another post, but then Power BI freezes for a second and then returns and error.
error: An error occured when deserializing the results. try to repeat the operation.
below is the full script that I would like someone to review and correct.
in bold, commands that I don´t understand, but I am using anyway because I just adapted from another post, and I think this is the way to get my solution.
let
Source = Json.Document(Web.Contents("https://url.com/token?limit=100&offset=0&date_range=201001010000:201912310000")),
Pagination = List.Skip(List.Generate( () => [offsett = "0"], // Start Value
each Source[offset] <> Source[next_offset], // Condition under which the next execution will happen
each [ WebCall = "https://url.com/token?limit=100&offset="&[offsett]&"date_range=201001010000:201912310000", // retrieve results per call
offsett = if WebCall[next_offset] >= WebCall[offset] then WebCall[next_offset] else WebCall[next_offset] ],// determine the offset for the next execution
each [Value]),1) // Select just the Record of the last step from your query
in
Pagination
I tried to wrap the webcall under Json.Document(Web.Contents()), but this also doesn´t work.
for information, the first line (Source) returns this, which is ok.
i need to use and compare the values from offset and next_offset.
the from_cache value is not good for this script, because it returns false or true even if I still have more data to read.
Anonymous wrote:I tried to modify the script from another post, but then Power BI freezes for a second and then returns and error.
error: An error occured when deserializing the results. try to repeat the operation.
Do you know what the format is of the results (XML, JSON, etc)? Is your token something that you can change so that you can share it and the API url with us? Or can you point to another API that is public that has a similar structure of returned results?
I believe the results are in JSON, because this is how Power BI automatically detects a simple load.
sorry, i cannot control nor manage the token, and therefore cannot share it here.
i dont know any other public API with a similar structure/list. i checked some another APIs from another posts´examples but none of them seems to use the same parameters or logic.
I managed to load 100% of the limited query, but the script if far from perfect.
I am still looking for a script that checks if offset < next_offset and then stops.the aproach I used so far is: I created a list of offset values, and the script loads all data parts.
if I set a low number for the offset possibilities, the script will miss the last values.
if I set a high number, the script will return a lot of empty rows after the full load is finished.the script below loads all rows until the end at row 4323.
but it continues to load more lines until the 4400 limit that I set (inicial source list goes from 0 to 4400).for example, at this row 4323, this is when the offset and next_offset are the same and the script must stop, but i could not work on a script using this logic.
let
Source = Json.Document(Web.Contents("https://url.com/token?limit=100&offset=0&date_range=201001010000:201912310000")),
Source1 = {0..45}, //list of total pages in my full dataset
#"Converted to Table" = Table.FromList(Source1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Added Custom" = Table.AddColumn(#"Converted to Table", "Personalizar", each [Column1]*100), //times 100 because my offset is at every 100 rows
#"Columns Renamed" = Table.RenameColumns(#"Added Custom",{{"Personalizar", "Column2"}}),
#"Added Custom" = Table.AddColumn(#"Columns Renamed", "Custom", each Json.Document(Web.Contents("https://url.com/token?limit=100&offset=" & Text.From([Column2]) & "&date_range=201001010000:201912310000")))
in
#"Added Custom"
I tried to improve the last code as it follows below.
in bold, thing that i changed.
now i got a different erro: The type of the current preview value is too complex to display
but before the error occurs, i can see at the bottom right corner of the power bi, an increasing number of rows that goes a bit above 300 rows and then it crashes.
let
Source = Json.Document(Web.Contents("https://url.com/token?limit=100&offset=0&date_range=201001010000:201912310000")),Pagination = List.Skip(List.Generate( () => [offsett = "0"], // Start Value
each Source[offset] <= Source[next_offset], // Condition under which the next execution will happen
each [ WebCall = "https://url.com/token?limit=100&offset=" & [offsett] & "&date_range=201001010000:201912310000", // retrieve results per call
offsett = WebCall[next_offset] ],// determine the offset for the next execution
each [posts]),1) // Select just the Record of the last step from your query
in
Pagination
