Part 4 - Data Migration using Power Platform Dataflows

1. What is a Dataflow?

===> A Dataflow is like a cleaning machine for your data.

===> It helps you:

• Bring data from different places (like Excel, SharePoint, or any databases etc.)
• Clean it (remove bad data, fix names, remove empty rows).
• Store it in one place (like Power BI or Dataverse) so you can use it in apps or reports.

===> In simple way we can define Dataflow is a tool that connects to messy data, cleans it, and stores it in a clean format for use in apps and reports.

Dataflows Bring Data In — not push it out

Example:

==> Imagine you're running a hotel.

You have:

• Guest details in Excel
• Room details in SQL database
• Booking records in SharePoint

==> But all this data is scattered and messy.

A Dataflow helps you:

✅ Connect to all these files

✅ Clean the data (e.g., remove blanks, correct dates)

✅ Combine them into one clean table

✅ Save the clean data in one place (like Dataverse)

2. Why Use Dataflows?

===> Imagine you have data in an Excel file (like customer info), and you want to:

• Use that data in your Power Apps
• Show it in Power BI
• Or automate something in Power Automate

===> Instead of copying and pasting it manually every time, you use Dataflows to:

• Bring that data into your app or report
• Keep it updated (automatically)

👉Dataflows save you time and effort.

3. When Should You Use Dataflows?

==> Use a dataflow when:

• You have data in another system (Excel, SQL, SharePoint, etc.)
• You want to import that data into Power Apps / Dataverse or Any other System.
• You need the data to refresh automatically (daily, weekly, etc.)
• You want to clean or change the format of the data before using it

👉Dataflows Bring Data In — not push it out

4. Where Can You Use Dataflows?

===> You can use dataflows in:

• Power Apps (to bring data into Dataverse)
• Power BI (for dashboards and reports)
• Power Automate (to run flows when data updates)

Important Note:

👉 You Can bring data into Microsoft tools (like Power BI or Dataverse).

👉 You Cannot send data out to external systems.

👉 Dataflows is One-Way Import-Only.

Real Time Scenario:

Importing and Transforming Sales Data from SQL Server into Dataverse for a Power Apps Sales Dashboard.

Overview:

You work in a company that stores sales records in a SQL Server database. The management wants a Power Apps dashboard to view and analyze this sales data (monthly sales, top-selling products, etc.)

Problem:

Here the problem is:

• The SQL data has extra columns we don’t need
• Product names are messy (like " Laptops " or "Desktops.")
• Dates are in a weird format
• You need to automate the process (data should update daily)

Our Goal is :

👉 Connect to SQL Server

👉 Clean/transform the data

👉 Load into Dataverse

👉 Refresh daily

👉 Use it in a Power Apps app or dashboard

Step 1: Open Power Apps and Go to Dataflows

1. Go to https://make.powerapps.com
2. Select your environment (top right)
3. In the left-hand menu, click Data > Dataflows
4. Click + New dataflow
5. Give it a name like “Sales Data Import”
6. Click Create

Step 2: Connect to SQL Server

1. In the data sources list, choose SQL Server
2. Enter your: Server name (e.g., salesdata.company.com) ; Database name (e.g., SalesDB)
3. Choose authentication type (e.g., Windows, SQL login, etc.)
4. Enter username and password
5. Click Next

Step 3: Select the Table You Want

1. You’ll see all tables from the database.
2. Choose the table: example dbo.SalesRecords

It might have columns like below:

SaleID ProductName Quantity SaleDate StoreCode UpdatedBy Notes

We only need:

• ProductName
• Quantity
• SaleDate

Click Transform Data

Step 4: Clean and Transform Using Power Query

Now you are inside Power Query Editor. Let’s clean the data:

1. Remove unwanted columns:

Right-click on SaleID, StoreCode, UpdatedBy, Notes

Click Remove Columns

2. Trim and clean text:

Click on the ProductName column

Go to Transform → Format → Trim (removes spaces)

Then click Format → Capitalize Each Word

3. Convert SaleDate format:

Select the SaleDate column

Change type to Date/Time

4. Filter out empty rows:

Click dropdown on ProductName

Uncheck (blank) rows

Once done, click Next

Step 5: Load Data Into Dataverse

1. Choose Create new table
2. Name the table Sales Data
3. Match the columns:

ProductName → Single Line of Text

Quantity → Whole Number

SaleDate → Date and Time

Click Next

Step 6: Schedule Automatic Refresh

1. You will now see Refresh Settings
2. Enable refresh and choose:

Frequency: Daily

Time: 2:00 AM

Choose credentials to connect again to SQL

Click Create

Step 7: Use the Data in Power Apps or Power BI

Now you can:

• Build a Power Apps Model-Driven App using the Sales Data table
• Create dashboards, forms, views

OR

• Use Power BI to create visuals from this table directly

What Happens Every Day Now?

At 2:00 AM daily, the dataflow will:

1. Connect to SQL Server
2. Pull the latest sales data
3. Clean and transform it automatically
4. Load into the Dataverse table

👉No manual work needed

Benefits of This Dataflow:

1. Auto-refresh ===> No need to re-upload Excel/SQL data
2. Cleaned data ===> No messy text or weird date formats
3. Accurate reports ===> Power Apps or Power BI always show fresh data
4. Central storage ===> All your app users can access the latest sales data in Dataverse

Difference between Data Wizard vs CMT vs Data flows

✅ Done, we have successfully Transformed data using Dataflows, Follow the same steps and try from your end.

Have a Nice Day!