Data Integration, Dataflows, Power Apps, Power Platform, PowerApps

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:

  1. Create a new Dataflow
  2. Choose a data source
  3. Transform the data if needed
  4. Choose Home – Get Data
  5. Choose another data source
  6. Transform the data if needed
  7. Merge Queries
  8. Choose join kind
  9. Select the join keys
  10. Verify number of rows – Click OK
  11. Expand the new column
  12. Go to the next page and map source to destination
  13. Run and you are done

Here we go!

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

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.

Web API as data source

 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.

Converting to a table

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.

Adding another data source

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

Getting data from OneDrive

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.  

Choosing an Excel sheet

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.

Merging queries

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.

Yet another warning

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.

Merged queries

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.

Mapping to our entity Plant

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.

Creating a new Model-Driven App

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

Modifying the Sitemap

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.

Gardening App

In the Trefle API I can find Magnolia Stellata.

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!

7 thoughts on “Combining information from multiple data sources with Power Platform Dataflows”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s