DataFlow in Power Platform

DataFlows

Dataflows are a self-service, cloud-based, data preparation technology. Dataflows enable customers to ingest, transform, and load data into Microsoft Dataverse environments.

When a dataflow is authored and saved, its definition is stored in the cloud. When a dataflow run is triggered, the data transformation and computation happen in the cloud, and the destination is always in the cloud

Powerful Transformation Engine in DataFlow

Power Query is the data transformation engine used in the dataflow. This engine is capable enough to support many advanced transformations. It also uses a straightforward, yet powerful, graphical user interface called Power Query Editor.

How DataFlow Works

Dataflows work by providing a cloud-based ETL (Extract, Transform, Load) solution that allows users to pull in data from various sources, clean and transform it using Power Query, and load it into destinations like Microsoft Dataverse, Power BI workspaces, or Azure Data Lake Storage.

1. Extract

This is the first step in the ETL process where data is pulled from various sources. The sources can include databases (like SQL Server), cloud services (such SharePoint, OneDrive), files (Excel, CSV), or web APIs.

2. Transform

After extracting data, the next step is to transform it. This involves cleansing, reshaping, and manipulating the data to meet the required format or structure. Data transformation is done using Power Query, which provides a no-code/low-code experience for handling data.

3. Load

Once the data is cleaned, transformed, and ready, the final step is to load it into the desired destination. In the context of Dataflows, you can load the data into

Dataverse: To store data that can be accessed and used by Power Apps, Dynamics 365, or other Dataverse-connected services.

Let’s Explore How Create Dataflows

Scenario 1: I have a list in SharePoint named student and want to migrate this SharePoint data into Dataverse

This is SharePoint list with data

?

Now Create Dataflow by follow the steps

1)Login to make.powerapps

2)Click on New dataflow

Give Name of dataflow

All Data Source will be listed out select? Sharepoint as data source

Give?Site URL where SharePoint list is present and provide login credentials

All SharePoint lists will be shown select respective list in this case (student list)

?

?

Now Power Query editor will be shown to cleans or transform the data

Here you can clean your data by removing columns, Add new custom columns , add new conditional columns , Merging tables and much more

Remove unwanted columns coming from source (SharePoint) by right click on remove column

You? can define data type of column

?

You can add custom column that not coming from source data and include that column in Dataverse

?

Simply define name of custom column and datatype of column

You can define different power query formula like to calculate length of character

Conditional Column can also be added like I added a conditional column that will check if CGPA is greater than 3 column value will be Passed else failed

Now after cleaning data next step is to load data in Dataverse table

Here we have options to load data in Dataverse table

1)Load to new table (selecting this option will automatically create table in Dataverse and load the data)

2)Load to Existing Table (selecting this option we first have to create a table in Dataverse define columns that we want and map columns)

3) Do not Load (selecting this option will not load data in dataverse table)

Delete rows that no longer exist in the query output : This option will load only that source data into Dataverse that is present inn power query editor If row is deleted in source in next refresh dataflow the data will not duplicate in Dataverse)

?

?

?

Refresh manually (this option allows to refresh dataflow and load updated source (SharePoint) data into Dataverse by manually click on refresh dataflow option

Refresh Automatically(allow to refresh dataflow by define intervals in minutes, hours ,days, week,? month and automatically sync source data with destination we do not need to manually refresh data

?

Now dataflow created and will be published ?

You can edit created dataflow , rename ,history etc…

?

After published and refresh dataflow data from SharePoint list will be loaded into Dataverse table successfully

?

Scenario 2: I have SQL database and have parent (project) and child (Deliverable) data and want to migrate parent child table data from SQL to Dataverse

Parent (Project) table data

?

?

Child table (Deliverable) data it have lookup of parent table project

In this Scenario data source is Azure SQL database

Give database server and login credentials

Select tables from database

Now we have to map columns of tables

Note: Define Key in Dataverse Parent (project) table otherwise lookup column in child table will not be shown

I created two tables Project (parent) and Deliverable (Child) table in Dataverse and define relationship (one to many) between parent child tables

Select Load to existing table and select table name (This will show columns of Dataverse table)

Simply map source (SQL) and Dataverse (Destination) Columns

The last column is lookup column of parent table simply select source( parent id )

?

?

After mapping columns and published dataflow SQL data will be loaded into Dataverse table

?

?

Scenario 3: I want to merge data of parent child into 3rd table which will show columns of both tables

To Merge Tables click on Merge query option

?

Select join type and select parent table primary column and child table foreign key column

?

Merge table will include all columns of parent and child table

?

?

?

?

?