Nested json in a paginated API call

[aukev]

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": {
        "entity_id": "12",
        "name": "John"
    },
    "13": {
        "entity_id": "13",
        "name": "Tim"
    }
}

 So when I extend the records I just get 100s of columns like 1,2,3,4, etc.. Instead of 'entity_id and "name".

let
    

        Pagination = List.Skip(List.Generate( () => [WebCall=[], Page = 1, Counter=0], // Start Value      
   	
        each [Counter]<5,
        each [ WebCall = Function.InvokeAfter(
            ()=>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 & "
                "]
            ])), 
            #duration(0,0,0,1)),          
            Page = [Page]+1,
            Counter = [Counter]+1  
	]
	) ,1),

        
    #"Table" = Table.FromList(Pagination, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(Table, "Column1", {"WebCall"}, {"Column1.WebCall"})
in
    #"Expanded Column1"

Any ideas on how I can get the nested json variables entity_id and name as column names? 

[tonmcg]

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]

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?

[tonmcg]

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?

[aukev]

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": "test@example.com",
    "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": "earl@example.com",
    "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: