Dataflows in PowerApps

Introduction: In this blog, we’ll cover the use of Dataflows to load data from various sources to Common Data Service(Microsoft Dataverse).

What is a Dataflow?

Dataflow is an Extract, Transform, and Load(ETL) process that takes data from various sources, uses Power Query to transform the data, and loads this data to CDS(Microsoft Dataverse) or Azure Data Lake Gen2.

What you can do with the loaded data?

You can build apps, flows, Power BI reports, and dashboards or connect directly to the dataflow’s Common Data Model folder in your organization’s lake using Azure data services like Azure Data Factory, Azure Databricks, or any other service that supports the Common Data Model folder standard.

Use Case:  In my example, I will be loading the data of the Azure SQL database to CDS(Microsoft Dataverse)

Steps to be followed:

1. Login to PowerApps (https://make.powerapps.com/)
2. Go to Data –> Dataflows and click on “+Start from blank”

3. Enter Name and Click on Create.

4. Select the Data source from where you want the data.

• In my example, the data source is Azure SQL database

• Enter the details to connect with Azure SQL database.

• Select the table and click on Transform data

5. Transform your data using Power Query.

6. Click on Next when you are done.
7. Now under Load settings, you’ll have the following options:

• Load to new table: It will create a new table(entity) in CDS(Microsoft Dataverse) and load your data under that table(entity)
• Load to existing table: If you already have a table in CDS, and you want to map your source data to that table, then select this option.

8. I am creating a new table and loading the data under that table.

• Select Load to create a new table under Load settings.
• You can change the Table name and Table display name
• You can see the Source and destination fields and the data type of that field.

9. Click Next when you are done.
10. Now set the Refresh setting.

• You can Refresh manually or you can set the refresh setting if you want the data to refresh automatically.

11. Click on create.
12. You can see your Dataflow.

13. Select the Dataflow –> More Commands(…) to change any of the settings related to the dataflow.

Viewing Data in CDS(Microsoft Dataverse)

1. Go to Data –> Tables
2. Select the table (Customer table is automatically created)

3. You can see all the columns of the table.

4. Under Data you can see all the data which is loaded from the Azure SQL database.

NOTE:

1. If you want to load your data to any of the existing table:

• Select the Load to the existing table under Load settings.
• Select the Destination table from the drop-down in which you want to load the data.

• Under field mappings, map the source and destination fields and click on Next

*When you map a source column to a standard column, the following occurs:

1. The source column takes on the standard column name (the column is renamed if the names are different).
2. The source column gets the standard column data type.

To keep the Common Data Model standard table, all standard columns that are not mapped get Null values.

All source columns that are not mapped remain as is to ensure that the result of the mapping is a standard table with custom columns.

*Reference: https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/create-and-use-dataflows