Forum Discussion
Nested json in a paginated API call
The thing to remember is that objects and arrays in JSON are equivalent to records and lists in Power Query M. The nested JSON you provided is an object containing two objects. In Power Query M terms, this is a record containing two records.
This in JSON:
{
"12": {
"entity_id": "12",
"name": "John"
},
"13": {
"entity_id": "13",
"name": "Tim"
}
}is equal to this in Power Query M:
[
#"12" = [#"entity_id" = "12", #"name" = "John"],
#"13" = [#"entity_id" = "13", #"name" = "Tim"]
]Making some assumptions about the structure of the data from the API, here's how I would access the entity_id and name from the list of nested records:
let
objects = { // list of records; in JSON, array of objects
[
#"10" = [#"entity_id" = "10", #"name" = "Mathilda"],
#"11" = [#"entity_id" = "11", #"name" = "Eliza"]
],
[
#"12" = [#"entity_id" = "12", #"name" = "John"],
#"13" = [#"entity_id" = "13", #"name" = "Tim"]
],
[
#"14" = [#"entity_id" = "14", #"name" = "Sara"],
#"15" = [#"entity_id" = "15", #"name" = "Joyce"]
]
},
initialPage = 1,
initialCounter = 0,
Pagination =
List.Generate(
()=>
[
Page = initialPage,
Counter = initialCounter,
WebCall = Record.FieldValues(objects{initialCounter})
], // initial
each [Counter] < 3,
each [
Page = [Page] + 1,
Counter = [Counter] + 1,
WebCall = Record.FieldValues(objects{Counter})
]
),
#"Converted to Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"WebCall", "Page", "Counter"}, {"WebCall", "Page", "Counter"}),
#"Expanded WebCall" = Table.ExpandListColumn(#"Expanded Column1", "WebCall"),
#"Expanded WebCall1" = Table.ExpandRecordColumn(#"Expanded WebCall", "WebCall", {"entity_id", "name"}, {"entity_id", "name"})
in
#"Expanded WebCall1"
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: