API call for each row - 400 Bad Request

[bimmylee]

I have a list of parent ID's generated from an intial API call:

Now I want to call a second API for each row, using each of these parent ID's as inputs, and put it all into one big table. So I made a custom column:

= Web.Contents("https://urlexample.com/webapi/v01.0/eobjects/XMLData/CampaignOpenClickDetails/" & Number.ToText([CampaignID]) & "?pageon=1&pagesize=10", [Headers=[#"User-Key"="key", #"Company"="company",#"UserName"="username"]])

 Now I have successfully made the custom column and I get the Binary content link

but when I click on the Binary link to try and expand it, I get this error:

DataSource.Error: WebContents failed to get contents from 'https://urlexample.com/webapi/v01.0/eobjects/XMLData/%0ACampaignOpenClickDetails/126289?pageon=1&pagesize=10' (400) Bad Request
Details:
DataSourceKind=Web
DataSourcePath=https://urlexample.com/webapi/v01.0/eobjects/XMLData/%0ACampaignOpenClickDetails/126289
Url=https://urlexample.com/webapi/v01.0/eobjects/XMLData/%0ACampaignOpenClickDetails/126289?pageon=1&pagesize=10

I know the headers (masked in this example) are correct because I use the exact same headers for the intial API call. Also, I'm able to successfully make this exact API call in Postman, so I know I have network access and such. What am I missing?

[freginier]

Do you try to call one API manualy ? without your list

______________________________________________________

If you found this post helpful, please give Kudos C

[bimmylee]

Yes I am able to do that successfully. I can call the second API to get detail records for campaign ID 366617 separately, for example. No errors, works perfectly. It's only when I try the same kind of call per each row.

[freginier · Accepted solution]

To collect detail data from API you need to create 

- Query One with all records (filter only on one for your test)

- Query Two with detail for one record 

- Parameter to send ID to second query 

- Create a function Query Two + Parameter 

- Use custom function in Query one to get detail for all ID

Did you do all these steps ? 

______________________________________________________

If you found this post helpful, please give Kudos C

[bimmylee]

This ordering of steps is what I needed. I have it working now. Thank you!

[bimmylee]

Hi @freginier, one followup question. The parameter requires me to enter a single "current value", but this causes my visuals on the reporting side to be filtered by that single value which is undesirable. How to I prevent this?

[freginier]

don't worry about this value it's only for first run. When you will create a function based on your parameter the value with change automaticaly