Rest API call where id is fetched from previous API Call

[aukev]

Hi,

I have an api call that looks like this

let
    url = "https://xx.com/magento-api/rest/customers?order=entity_id&dir=dsc",
    Source = Json.Document(Web.Contents(url, [Headers=[Authorization="OAuth oauth_consumer_key=""xx"", oauth_token=""xx"", oauth_signature_method=""PLAINTEXT"", oauth_timestamp=""xx"", oauth_nonce=""2NuM9DBGZHb"", oauth_token_secret=""accessTokenSecret"", oauth_version=""1.0"", oauth_signature=""xx"""]])),
    #"Converted to Table" = Record.ToTable(Source),
    #"Expanded Value" = Table.ExpandRecordColumn(#"Converted to Table", "Value", {"entity_id", "email", "created_at", "disable_auto_group_change"}, {"Value.entity_id", "Value.email", "Value.created_at", "Value.disable_auto_group_change"})
  
in
    #"Expanded Value"

The result of this will be 10000s of rows.

Now if I want to add address and phone number data to this table. For this I`d need to run a new API call. Like this:

http://xx/magento-api/rest/customers/{entity_id} where entity id is the same as identity id in the code above.

What's the most optimal way to do this? I currently found a solution where I add a new column where I call the api again like this: customers/['entity_id']/addresses . This works but I`m not sure if this is the most optimal solution.

If more optimal performance wise then I would consider loading the address data in a new table.

[v-piga-msft · Accepted solution]

Hi @aukev ,

From your description, it seems that the url which you want to run a new API call is the same with the url in your first API call.

I'm afraid that you just refresh the data source and then you will get the new feild in your data source.

If you want to add the new column address, you could back to the step #"Expanded Value" and modify the formula like below.

#"Expanded Value" = 
Table.ExpandRecordColumn(#"Converted to Table", "Value", {"entity_id", "email", "created_at", "disable_auto_group_change","adress"}, 
{"Value.entity_id", "Value.email", "Value.created_at", "Value.disable_auto_group_change","Value.adress"})

In addition, you could extract the two list outside and then you could manage them in the two list.

Best Regards,

Cherry

[aukev]

That worked, thanks.

[v-piga-msft]

Hi aukev ,

You're welcome!

Best Regards,

Cherry