How to pass MongoDb collection data into POST web request on Azure Data Factory Pipeline

Question

Greetings,

I have a task to perform dataset refresh on datasets published on PBI online. For that, I trying to use an Azure Data Factory Pipeline to refresh the data through REST API as listed in the documentation here: https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/refresh-dataset-in-group

Here's in more detail:

First, I have a dataset that gets data from a mongoDb collection. The preview data looks like this:

Second, I have a pipeline which creates a new dataset into a target powerbi online workspace (https://learn.microsoft.com/en-us/rest/api/power-bi/push-datasets/datasets-post-dataset-in-group#code-try-0)

I can create this dataset with dummy data as long as pass as JSON parameters in the Body. However, I want to create this dataset with the data from the first image, organized in columns tables such as it allows the creation of reports. How can I do that in my pipeline? What's the syntax I need to use in the Body?

I want to create this dataset in the correct way such as the data refresh can be easily done later.

Thanks for the attention.

Answer 1

Hi Alan Pitthan,

Thank you for posting this query in Microsoft Q&A Platform.

If I understand correctly, you want to compose your json body for API request with data coming from previous activity. Please correct me if I am wrong.

You can do this by reading property values from your previous activity output json and writing dynamic expressions accordingly. For you want to loop over the items(json object) of your previous activity array, then you need to use ForEach activity to loop. And inside loop may be use Set variable activity, to write dynamic expressions acccordingly to compose json body. Finally use that variable as json body in the web activity.

Please check below video to understand, how to read properties of previous activity output json into dynamic expressions.

How to read JSON output of one Activity in to another Activity in Azure Data Factory

Below are useful videos to understand activities.

ForEach Activity in Azure Data Factory

Set Variable Activity in Azure Data Factory

Hope this helps. Please let me know if any further queries.

---

Please consider hitting Accept Answer button. Accepted answers help community as well.