Forum Discussion
Nested json in a paginated API call
I have this request for an api. It's json. Works fine but the result of this are four records. One for each page. The problem is it's a nested JSON that looks more or less like this: {
"12":...
Thanks tonmcg . I haven't really gotten that to work though. I`m not 100% if I`m getting the json object right. This is what I have know based on your example:
let
Url = "https://xx.com/magento-api/rest/customers?order=entity_id&dir=asc",
Limit = "100",
consumerKey = "xx",
Token = "xx",
SignatureMethod = "PLAINTEXT",
Signature = "xx",
TimeStamp = "1551909334",
Nonce = "2NuM9DBGZHb",
Pagination =
List.Generate(
() => [
Page = 1,
Counter=0,
WebCall = Function.InvokeAfter(Record.FieldValues(
Json.Document(Web.Contents(Url,
[Query=[limit="" & Limit & "",page="" & Text.From([Page]) & ""],
Headers=[Authorization="OAuth
oauth_consumer_key=" & consumerKey & ",
oauth_token=" & Token & ",
oauth_signature_method=" & SignatureMethod & ",
oauth_timestamp=" & TimeStamp & ",
oauth_nonce=" & Nonce & ",
oauth_version=""1.0"",
oauth_signature=" & Signature & "
"]
]
)){Counter}),#duration(0,0,0,1))
], // Start Value
each [Counter] < 5,
each [
Page = [Page]+1,
Counter = [Counter]+1,
WebCall = Function.InvokeAfter(Record.FieldValues(
Json.Document(Web.Contents(Url,
[Query=[limit="" & Limit & "",page="" & Text.From([Page]) & ""],
Headers=[Authorization="OAuth
oauth_consumer_key=" & consumerKey & ",
oauth_token=" & Token & ",
oauth_signature_method=" & SignatureMethod & ",
oauth_timestamp=" & TimeStamp & ",
oauth_nonce=" & Nonce & ",
oauth_version=""1.0"",
oauth_signature=" & Signature & "
"]
]
)){Counter}),#duration(0,0,0,1))
]
),
#"Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(Table, "Column1", {"WebCall"}, {"Column1.WebCall"}),
#"Column1 WebCall" = #"Expanded Column1"{0}[Column1.WebCall]
in
#"Column1 WebCall"
But this gives me the following error:
Expression.Error: There is an unknown identifier. Did you use the [field] shorthand for a _[field] outside of an 'each' expression?
Any idea what this could be?
Can you provide an example of the JSON that's returned from one of your calls? And at which query step do you get that error?
Hi tonmcg . Thanks again for your help. The Json response for this request normally looks like this:
{ "2": { "entity_id": "2", "website_id": "1", "email": "[email protected]", "group_id": "1", "created_at": "2012-03-22 14:15:54", "disable_auto_group_change": "1", "firstname": "john", "lastname": "Doe", "created_in": "Admin", "prefix": null, "suffix": null, "taxvat": null, "dob": "2001-01-03 00:00:00", "reward_update_notification": "1", "reward_warning_notification": "1", "gender": "1" }, "4": { "entity_id": "4", "website_id": "1", "email": "[email protected]", "group_id": "1", "created_at": "2013-03-28 18:59:41", "disable_auto_group_change": "0", "firstname": "Earl", "lastname": "Hickey", "created_in": "Admin", "prefix": null, "suffix": null, "taxvat": null, "dob": "2012-03-28 13:54:04", "reward_update_notification": "1", "reward_warning_notification": "1", "gender": "1" }The error happens at the Pagination step. When I click on show error it directs me there.
This is what I see: