Forum Discussion

aukev's avatar
aukev
Helper III
7 years ago

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":...
Need Help
tonmcg's avatar
tonmcg
Resolver II
7 years ago

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"

 

aukev's avatar
aukev
Helper III
7 years ago

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?