Append and Merge to combine data from multiple data source in Power Platform dataflows

Let us continue with our previous post where we loaded the data from on-prem SQL DB to CDS using dataflows.

https://nishantrana.me/2020/07/07/load-data-from-sql-on-premise-to-cds-common-data-service-using-power-platform-dataflows-in-power-apps/

Now suppose we have another table (or any other data source) having the contact details, which we would like to append/merge along with our previous data source.

For simplicity, here we have created a copy of the existing table and added a new record with different values.

Edit the existing data flow created earlier.

Click on Get data and select the SQL Server database.

*The other data sources that are available.

Specify the connection details to connect to the database.

Select the new table that we had created.

Next click on Append queries, select Append queries as new, to create a new query.

Specify the primary table and table to append.

The new Query by default named Append will have the data from both the tables combined i.e. appended, which can then be used to load data to the existing contact entity or load to a new entity.

Specify the mapping in the data flow that completes the configuration of the dataflow.

That was about Append queries, now let us take another simple example to see Merge queries in action.

Again let us create one more table that has additional detail (e.g. Mobile Number) for the contact records. Just for simplicity, we are creating a table else it could be any of the supported data sources.

Let us edit the dataflow created earlier, and select Get data to get the data from the SQL Server database.

Here we have selected the new table Contact Details.

This time select Merge queries.

Specify the left table and right table for merge and the join type.

Select the join keys column.

Here we have opted for Inner Join.

The result includes the 1 matching row.

Expand the MyContactDetails column to populate the MobileNumber data.

Now with the data transformed, click on Next and define the mapping using the new merged query.

After a successful refresh, we can see the mobile phone field updated.

Let us update the value again for the mobile number field and run the refresh.

We can see the value updated for the CDS contact entity.

If the tables are already related using foreign key relationship for e.g. Contact and Contact Details, the Get data will load the related table, without any need of merge queries.

PkFk

Refer below articles for more details on Append and Merge in Power Query.

https://radacad.com/append-vs-merge-in-power-bi-and-power-query

https://carinaclaesson.com/2020/03/19/combining-information-from-multiple-data-sources-with-power-platform-dataflows/

Hope it helps..

Load data from SQL On-Premise to CDS (Common Data Service) using Power Platform dataflows

Let us continue our previous post where we created a connection to the below On-Premise SQL Server Database using an On-premises data gateway.

https://nishantrana.me/2020/07/06/configuring-on-premises-data-gateway-to-connect-tosql-server-on-premise-data-source-power-platform/

Here we will use the Power Platform
dataflows to load contact entity in CDS using that same on-prem table.

Sign in to Power Apps

https://make.powerapps.com/home

Navigate to Data – Dataflows and create a new dataflow

Dataflow can store the entities either in Common Data Service or Azure Data Lake for analytical purposes.

Leave the Analytics entities only checkbox unchecked as we will be saving the entities in the Common Data Service.

Next, let us specify the data source. Here we have selected SQL Server Database as the source.

Specify connection details

On specifying the Server and Database details, it will auto-populate the rest of the connection details if already defined.

Select the table and click on Transform data

Transform Data give us the option of further refining the data to match the destination.

Click on Next and specify the mapping.

We can either load data in a new entity

Or load to an existing entity.

Here we have selected contact entity and clicked on Automap option which has mapped FirstName and LastName fields for us. Manually map the email field. Here we have not mapped the ContactID field for now.

Here for Key fields in Field Mapping, it will list down alternate key, if defined for the entity.

Specify the refresh settings. We have kept is as refresh manually.

With refresh completed, let us check the contact entity.

Go to Data – Entities and Data tab, we can see the records added.

Now let us add one more record in the source database and refresh the dataflow manually.

As we had not specified key while defining the data flow, we have all the records created again including the new one.

Now let us define an alternate key for the contact entity.

And edit the data flow to map the ContactID field of the source database to the newly created alternate key.

Define the mapping for the alternate key field.

Also, let us delete the old records that were synced. After refresh is done, we can see the records created

What about the update?

Let us update the record in source and run the refresh

As expected, the record is updated and no duplicate records are created in CDS.

What about delete?

Now let us delete the record in the source and run the refresh

The records are not deleted in the Common Data Service.

What about updates in CDS?

As this is a one-directional flow to load the data from source to destination, any change in CDS will be overridden and will not be synced back to the source SQL Server database on the next refresh run.

Let us update records in CRM, and run the refresh.

As expected, the record in CDS is updated back with source DB.

What about the history and log of the refresh?

Show refresh history option gives us all the details of the refresh run and the failure and success of the records.

More on the data flow capabilities

https://docs.microsoft.com/en-us/data-integration/dataflows/dataflows-integration-overview#dataflow-capabilities-in-power-platform-services

Also, check out the interesting article below

https://carinaclaesson.com/2020/05/11/alm-for-power-platform-dataflows-the-story-begins/

Hope it helps..

Configuring On-Premises data gateway to connect to SQL Server on-premise data source – Power Platform

Let us take a simple example to understand the steps to be performed for configuring an On-premises data gateway.

We have below database in our on-premise SQL Server which we connect to using On-Premise data gateway.

Below are the steps we need to perform to configure it –

Login to Power Apps and navigate to Data – Gateways

https://make.powerapps.com/

Download the On-Premises Data Gateway

Follow the installation wizard to install the on-premises data gateway

https://docs.microsoft.com/en-us/data-integration/gateway/service-gateway-install#minimum-requirements

Sign in with the Office 365 Organization account

Select Register a new gateway on this computer

Specify the name and the recovery key and click on configure

This completes the setup of the gateway.

Within Power Apps, we can see the gateway record created.

Now let us create a connection to our On-Premise SQL Server Database that uses the gateway configured

Navigate to Data – Connections and click on the new connection.

Select the SQL Server connection type, and specify the connection details of the on-prem database.

Select the gateway configured and click on Save, which will test the connection.

On a successful connection, we can see the connection created with status Connected.

Now as we have the connection established with our On-Premise SQL Server database through the gateway, we can use it for in various cloud services like Power BI, Power Apps, Power Automate, Logic Apps, Dataflow, etc.

Get more details here

https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-onprem

Another option to connect to SQL On-Prem database is through Azure Hybrid Connections (this would for Azure App Service)

https://nishantrana.me/2018/02/19/using-azure-hybrid-connections-to-connect-to-sql-on-prem-database-from-azure-webjob/

https://www.slideshare.net/biztalk360/the-hitchhikers-guide-to-hybrid-connectivity-81149063

Hope it helps..