Dyanmic Query per Row O

[Ashley101]

I have an API call which pulls a dataset, this dataset contains an ID for the child dataset (audience).

e.g. /url/campaigns

returns: campaign_id, campaign_title, campaign_audience_id

Unfortunately the child dataset can only be pulled using the child ID in the API call. 

This means I need to do a new API call for the child dataset on each row of the parent.

e.g. /url/audience/{id}

Is there a way in PowerBI to do this and possibly combine the child datasets?

[Ashley101]

Hi Winniz,

Thanks for your reply. 

That looks almost there, but the ID value (and consequently, child dataset) changes per row of the parent.

In your example does the Source loop for each row of the parent supplying the ID?

My parent table has an unknown number of records so this makes manual addition of a child dataset per record unvible.

Please see the attached image, I hope it clears things up.

[v-kkf-msft]

Hi @Ashley101 ,

Do it need to reference the ID of the parent dataset in the API that calls the child dataset details?

[Ashley101]

Hey,

Unforunately it does, it's a third party API so I cant alter it.

[v-kkf-msft]

Hi @Ashley101 ,

You can first get all the dataset IDs and then add custom column to get the child dataset for each ID.

Like this:

..."https://api.powerbi.com/v1.0/myorg/groups/xxxxxxx/datasets/" & [id] & "/users"...

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.