Combining information from multiple data sources with Power Platform Dataflows

Power Platform Dataflows can be used to load data either into the Common Data Service or into an Azure Data Lake Storage Gen 2 resource, both for utilizing the data in your Dynamics 365 apps or your custom Power Apps and for analytical scenarios. I have written about Dataflows earlier, even before the feature was called Dataflows. Last year Data Integration in Maker Portal was rebranded into Dataflows as part of the 2019 Release Wave 2. In this blog post we will look at how to combine information from different sources and write to the same entity in the Common Data Service.

The sources

In this example two different sources will be used, the first one is Web API and the second one is Excel. Please note that the sources might as well have been tables in two different databases. I have not tried that example, but I assume it works in a similar way. What I have tried though is to combine information from two different tables from an on-premises SQL server and write to one entity in the Common Data Service. More about that in a later blog post.

The first source is Web API, the global plants API Trefle will be used, we will use the following URL for our request:

https://trefle.io/api/plants?q=magnolia&token=YOUR-TOKEN

For the token parameter you specify a token which you can get by creating an account for free at trefle.io. The parameter q means that we are searching for all plants with in this case “Magnolia” in the Common name or Scientific name, i.e. different Magnolia plants.

In the Excel file I have added some of the Magnolia plants with IDs from the Trefle API and a Wikipedia link. The plan is to set up a Dataflow which loads plants (Mangolia plants) from the API into the Common Data Service and includes the information from the Excel file within the same Dataflow. Just to give an example of how to combine information from different sources in a Power Platform Dataflow.

The entity

When setting up a Power Platform Dataflow, you can choose between writing to an existing entity or to let the Dataflow create an entity for you. Here I recommend to create the entity before you set up the Dataflow, simply because otherwise you cannot set your own prefix for the entity. Vote for my idea – for us to be able to let the Dataflow create an entity with a prefix of our choice.

This is how my entity looks like. A simple one with just a few fields. The records will be filled with data from the Web API combined with the Wikipedia links from Excel. Please note that the Trefle API contains a lot of more information about each plant but this is what we will use in this example.

A Gardening Solution wich one entity – Plant

The Dataflow

Here is a summary of the configuration:

Create a new Dataflow
Choose a data source
Transform the data if needed
Choose Home – Get Data
Choose another data source
Transform the data if needed
Merge Queries
Choose join kind
Select the join keys
Verify number of rows – Click OK
Expand the new column
Go to the next page and map source to destination
Run and you are done

Here we go!

Go to the Maker portal, Data, Dataflows. Choose New Dataflow.

Choose Web API and type in the URL. As already mentioned, we will query the Trefle API and search for Magnolias. This is what it looks like.

We will get JSON back from the Trefle API.

How to get something we can map to our entity? Read on and you’ll see!

Now we need to use the button “To Table” and then expand it, in order to convert this list into a table and to get something that looks like we can actually map it to an entity in the Common Data Service.

When we have our table, we click on the button next to the column header to expand and then we get to choose which column we want to include.

Now we have our columns for this first query and we are ready to add another data source. Under Home – Get data you will find the data sources and this time we choose Excel.

We then get to browse OneDrive and from there we can upload a document or choose a document which we have already put there.

Next we get to choose Excel sheet, I had only one and in that one there are two columns – one for the Trefle ID (in order to match the records from the Trefle API) and one which contains a Wikipedia link.

After we have pressed OK we now have two different queries to work with.

Now we have two data sources as two different queries

The first thing we will do with our query with data from Excel is to choose to use the first row as header.

Use first row as header – in order to set the column headers

Next we will merge the two queries.

Where to find the “Merge queries” alternative

Now here we will get to choose what kind of join we want to achieve. In my case I want all records from the Trefle API and the matching records from the Excel file so I will make sure ‘Left outer join’ is chosen when I click Merge. I will now get to see how many matches there are.

These are the different kinds of join to choose from.

Different kinds of Join when merging queries

When you combine different sources you will get prompted with a question – do you really want to do this:

You need to allow combining data from multiple data sources

When you have said yes I allow that, you will get prompted again.

Just click continue and all is good. Now we will see one column from the Excel file, which contains the whole sheet and we can expand it and choose what Excel columns we want to include.

Now let us expand it.

Expanding to get the Excel data in different columns

Now we can move on to the next page and start with the mapping. Remember from earlier in this blog post that we created an entity, Plant, which will hold all information. So first we will set Wiki to Do not load. Then we go to Query and here is where we will map to the entity Plant.

Choosing not to load data from the Wiki query

This is what is looks like when we are done with our mapping.

Just click Next and the Dataflow will run. When it has been run we can take a look in Maker Portal under the entity Plant.

Data has been loaded into our entity Plant. Please note – there are more than these records in there.

Now let us go and create a Model-Driven App so that we can work with our data.

Set a name and click OK. Then go to the Sitemap, rename the headings and add the entity Plant.

Save and Publish. Before you Play the app, go back to your entity Plant and make some adjustment according to your choice for the views and forms. Then play your app.

In the Trefle API I can find Magnolia Stellata.

I can also find Magnolia Kobus. However I cannot find the combination of those two – the Hybrid Magnolia × loebneri ‘Leonard Messel’, which is a favorite of mine. I better explore the API further. Anyway – I hope you have enjoyed reading this blog post!