Power Automate's deep integration

across multiple data sources

 49 min
 Module
 7 Units
Feedback
Beginner
Business User
App Maker
Functional Consultant
Microsoft Power Platform
Power Automate

Power Automate is an online workflow service that automates actions across the most
common apps and services. In this module, you build flows across multiple data sources.

Learning objectives
This module explains how to:
 Build a flow that uses Dataverse.
 Build a flow that uses SQL.
 Integrate Power Apps, Power Automate, and SharePoint.
 Monitor flows.

Introduction
Completed100 XP
 3 minutes

Let's face it, sometimes we simply can't pick and choose our data sources. We have to
use what is in use and available at that point in time.

This is where Power Automate's large ecosystem of Software as a Service (SaaS)

connectors comes in handy. A connector, that is simply a wrapper around an API
(Application Programming Interface), allows the underlying service to talk to Microsoft
Power Automate. It provides a way for users to connect their data and use a set of
prebuilt actions and triggers to build their flows.

In this unit, we'll take a look at some of the popular data sources such as Dataverse, SQL
and SharePoint.

So, let's get started!

Exercise - Build a flow that runs when

an event in Dynamics 365 occurs
Completed100 XP
  14 minutes

You can create flows that start when an event occurs in Microsoft Dataverse or some
other service. These flows then perform an action in that service.

In Power Automate, you can set up automated workflows between your favorite apps and
services to sync files, get notifications, collect data, and more.

In this unit, we look to build two flows:

 The first flow creates a task in Microsoft Dataverse when a new Account is created.
 The second flow copies a list item to the Planner when a task is created in Microsoft
Dataverse.

Here are some other examples of flows that you can create by using Microsoft
Dataverse:

 Create a list item in Microsoft SharePoint when an object is created in Microsoft

Dataverse.
 Create Microsoft Dataverse account records from a Microsoft Excel table.

Prerequisites
 Access to Power Automate.
 An environment with a Microsoft Dataverse database. (Applicable only for work or
school accounts).
 A basic understanding of Power Automate and experience with creating a flow is
recommended.
 A pre-existing plan in Microsoft Planner. Create a plan in Planner.
Important

To invoke a flow trigger, the Microsoft Dataverse customer engagement table that's used
with the flow must have change tracking turned on. For more about how to turn on
change tracking, see Enable change tracking to control data synchronization.

Example one: Create a task from a new account

This example shows how to create a new row in the Dataverse Tasks table whenever an
account is added to the Dataverse Accounts table. Both
the Accounts and Tasks tables are part of the Common Data Model and are available in
all Microsoft Dataverse environments.

1. Sign in to Power Automate using your organizational account then select the
environment for the flow.

2. In the left pane, select My flows.

3. Select New flow, and then select Automated cloud flow.

4. Type in a name for the flow under Flow name. For this example, type Create
a task when an account is created for the flow name.

5. In the list of flow triggers, enter Microsoft Dataverse in the search all triggers
field and then select Microsoft Dataverse - When a row is added,
modified, or deleted.

6. Select Create. If prompted to sign in to Dataverse, do so.

7. From the canvas pane, select the trigger action When a row is added,
modified, or deleted to open it in the action configuration pane.

8. Select Added for Change type, select Accounts for Table name.

9. For the Scope, select Organization

Tip

The Dataverse trigger requires a Scope to be [Link] scope determines

whether the flow runs when a user within a business unit creates a new
record, or if any user in the organization creates a new record.

Next, we add another action using Copilot.

10. In the Copilot pane on the right, type in When a new row is added to a
Dataverse table, add a new row to another Dataverse table and press ENTER.

11. Copilot expands the flow and adds the action Add a new row.

Note

Copilot may give you an "Invalid parameters" warning in your flow trigger as it
updates the flow. If that happens, return to your flow trigger and update the
required parameters before continuing.

12. Select the action Add a new row to configure the properties. The action
configuration pane on the left pops out.

13. In the Table Name field, find/select the Tasks table.

14. More fields appear under the table selection. Select the Subject field and
then the dynamic content icon (it's in the shape of a lightning bolt) to
choose fields from the previous steps.

For this unit, select Account name.

 15. Select Save from the right side of the command bar to save the flow.

Now when a new row is added to the Accounts table, Power Automate creates a new
row in the Tasks table and the Subject of the task is the name of the new account.

Example: Create a planner task from a Microsoft

Dataverse task
This example shows how to create a task in Microsoft Planner whenever a task is created
in Microsoft Dataverse. Planner is a service that you can use to create to-do lists, add
reminders, and track errands. To complete the example, you need to create a plan in
Planner.

1. Return to [Link] using your organizational account then

select the environment for the flow.

2. In the left pane, select My flows.

3. Select + New flow, and then select Automated cloud flow.

4. Type in a name for the flow under Flow name. For this example, type Create
a Planner task when a Dataverse task is created for the flow name.

5. Type in Dataverse in the search all triggers field and then select Microsoft
Dataverse - When a row is added, modified, or deleted.

6. Select Create.

7. The new flow opens in Power Automate Copilot designer.

8. Select the trigger When a row is added, modified, or deleted to open the
action configuration pane and configure the properties.

9. In the Change type field, select Added.

10. In the Table Name field, find/select the Tasks table.

11. In the Scope field, select Organization.

Note

If the trigger shows Invalid connection, select Change connection from

the action configuration pane on the left. You are either prompted to sign in or
select your existing connection.
 Next we add a planner task.

12. In the Copilot pane on the right, type in When a new row is added to a
Dataverse table, add a new planner task and press ENTER.

13. Copilot expands the flow and adds the action Create a task.

Note

It's possible Copilot deleted the Table name from the trigger. Verify the Tasks
table is still selected, then continue.

14. Select the Create a task action to open the properties in the action
configuration pane on the left.

15. For Group ID, select the dropdown and select the Group that has your
Planner plan. In our example, I select Sales and Marketing.

16. For Plan ID, select the plan from the dropdown. In our example, we're
selecting Product Launch Event.

17. In the Title field, enter some text and then add the Subject from the
dynamic content pane. In our example, for the Title field, we enter Begin
onboarding process for:

18. Select the lighting bolt to bring up the dynamic content options. Since we're
looking for the subject, I start typing Subject. Once the subject pops up in the
dynamic content box, select it.

19. Select Advanced parameters then select Bucket Id. You can select out of
the advanced parameters popup to close it. The Bucket ID field now shows
below the Advanced parameters.

20. For Bucket ID, select the bucket to create the task in from the dropdown
(optional). In our example, we can select To do, but if you made a planner
from scratch, look for the option Up next. If there are other parameters you'd
like to add data for, continue entering the dynamic content into your planner
task items from Dataverse. An example is the field Due Date Time where
you can select the Due Date from Dataverse.
 See create a task parameters for information about the other fields you can
add to your task.

21. Select Save from the upper right menu to save the flow.

Now, when new tasks are added to the Tasks table, a new task is created in the
"Product Launch Event" in Planner.

Limitations of trigger-based logic

Triggers like When an item is created, When a item is updated, and When an
item is deleted start your flow within a few minutes after the event occurs. In rare
cases, it might take up to two hours for your flow to be triggered.

When the trigger occurs, the flow receives a notification, but the flow runs on the data
that exists when the action runs. For example, if new item creation triggers your flow,
and you update the item twice before the flow runs, your flow runs only once with the
latest data.

Specify advanced options

When you add a step to a flow, you can select Advanced parameters (or Show
advanced options in the old designer) to add a filter or order-by query that controls
how the data is filtered.

For example, you can use a filter query to retrieve only active Accounts ordered by email
address when retrieving a list of rows. In the example below we have Filter
Rows and Sort By selected from Advanced parameters. To retrieve only active
accounts, we're using status eq 0. To order the list by email address, we use
the Email column name. For more about filter and order by queries, see MSDN:
$filter and MSDN: $orderby.

Best practices for advanced options

When you add a value to a column, you must match the column type. You must match it
regardless of whether you enter a value or select a value in the dynamic content pane.

Expand table

Column type How to use Where to find Name Data

type

Text columns Text columns require a single line of text or Solutions > Default Category Text
Column type How to use Where to find Name Data
type

dynamic content that's a text-type column. Solution > Task > Columns
Examples include the Category and Sub-
Category columns.

Integer columns Some columns require an integer or dynamic Solutions > Default Percent Whole
content that's an integer-type column. Solution > Task > Columns complete Number
Examples include the Percent
Complete and Duration columns.

Date columns Some columns require a date entered Solutions > Default Created on Date and
in mm/dd/yyyy format or dynamic content Solution > Task > Columns Time
that's a date-type column. Examples include
the Created On, Start Date, Actual Start, Last
on Hold Time, Actual End, and Due
Date columns.

Columns that Some columns that reference another table Solutions > Default Accountid Primary
require both a record require both the record ID and the Solution > Account > Columns Key
record ID and a lookup type.
lookup type

Exercise - Build a flow that uses SQL

Completed100 XP
 12 minutes

This unit shows how to create a flow that monitors a source for new or changed items,
and then copies those changes to a destination. You might create a flow of this type if
your users enter data in one location, but your team needs that data in a different
location or format.

In this unit, you copy data from a list in Microsoft Lists, the source, to a Microsoft Azure
SQL Database table, the destination.

Keep in mind that you can copy data over hundreds of services that Power Automate
supports.

Important

Changes that you make in the destination aren't copied back to the source, because two-
way synchronization isn't supported. If you try to set up two-way synchronization, you'll
create an infinite loop where changes are sent endlessly between the source and
destination.

Prerequisites
 Access to a data source and a destination. This unit doesn't include the steps
to create the source and destination.
  Access to Power Automate.
 A basic understanding of how your data is stored.
 Familiarity with the basics of creating flows. For this unit, it's assumed that
you know how to perform these actions.

Tip

Column names in the source and destination don't need to match (unless the column's
data type is choice), but you must provide data for all required columns when you insert
or update an item. Power Automate identifies the required fields for you.

Quick overview of the steps

If you're comfortable with Power Automate, use these quick steps to copy data from one
data source to another.

1. Identify the source to monitor and the destination to copy changed data to.
Confirm that you have access to both the source and the destination.
2. Identify at least one column that uniquely identifies items in the source and
destination. In the example that follows, we use the Title column, but you can
use any columns.
3. Set up a trigger that monitors the source for changes.
4. Search the destination to check whether the changed item exists.
5. Use a condition like this:

 If the new or changed item doesn't exist in the destination, create

it.

 If the new or changed item exists in the destination, update it.

6. Trigger your flow, and then confirm that new or changed items are being
copied from the source to the destination.

Note

If you haven't previously created a connection to SharePoint or a SQL Database, follow

the instructions when you're prompted to sign in.

Here are the detailed steps to create the flow.

Monitor the source for changes

First, Copilot helps us build the structure of the flow.

1. Launch Power Automate and sign in using your organizational account.

2. In the middle of the home page type, When an item is created or modified
in SharePoint add a get rows SQL action. Add a condition and if the
item exists, update the item in SQL, else create a new item in
SQL. Then press Generate.
 3. Copilot strives to build a flow with actions based on the description and
provides an opportunity to review the actions that were generated. In this
example, you can see:

a. The description box where we entered our prompt. You can continue
to refine the actions in the flow here.

b. Copilot added a When an item is created or modified

trigger (SharePoint).

c. Copilot added a Get Rows (SQL) action, a Condition, an Update

Row (SQL) action in the condition's True branch and an Insert
Row (SQL) action in the condition's False branch.

d. The Next button takes you to the flow designer.

4. Once you're satisfied with the flow, select Next to configure the actions.
5. If you didn't previously create a connection to the SQL Database or
SharePoint, Copilot instructs you to create a connection. Otherwise, you see
green check marks next to each connection.

6. Once the connections are established, select Create Flow.

7. Select the When an item is created or modified trigger to open the action
properties panel on the left.

8. Enter the site address and then select the name of the list in Microsoft Lists
that your flow monitors for new or updated items.

Search the destination for an existing item

Next, we configure the Get rows (SQL) action to check if an item already exists in the
SQL database.

1. Select the Get Rows action to open the action properties panel on the left.
2. Set the Server name, Database name, and Table name for the SQL table
you wish to monitor.
 3. Select the Advanced Parameters and select Filter Query.

4. In the Filter Query box, enter CustomerID eq. Then select

the CustomerID token in the dynamic content list.

Note

This step assumes the Microsoft List and the SQL table will match on
the CustomerID column.

Check whether to create a new item or update an

existing item
Next, we configure the condition to check if an item exists. If the item exists, update the
item, else create a new row.

1. Select the condition to open the action properties panel on the left.
Select Insert Expression.

2. Search for length and select length(collection).

3. Without leaving the open expressions pane, select the Dynamic content tab.
4. Under the Get rows category, select body/value, and then select Add.

Tip

Confirm that you've selected value from the Get rows category. Don't
select value in the When an item is created or modified category.

5. In the field in the center, select is equal to.

6. In the field on the right, enter 1 (one).

The Condition card should now look like this image.

 Tip

The addition of the length() function lets the flow check the value list. Length
returns the number of items in the list. If there is 1 item in the value list, then
we know the item exists in SQL and needs to be updated.

When your flow gets items from the destination, there are two possible outcomes.

Expand table
Outcome Next step
The item exists. Update the item.
The item doesn't exist. Create a new item.

Create the item in the destination

The condition is checking if the Microsoft List item exists in the SQL destination. But we
need to tell Power Automate what to do once the condition is checked. If the Microsoft
List item doesn't exist in SQL, then create a new row, else update an existing item.

1. Select the Insert Row (SQL) action on the False side of the condition to open
the action properties panel on the left.
2. Set the Server name, Database name, and Table name for the table to
insert the new item into (same as the information you entered above).

Note

The Insert row card expands and shows all fields in the table. Fields that are
marked with an asterisk (*) are required and must be filled in for the row to be
valid.

3. Select each field that you want to fill in and enter the data.

You can manually enter the data, select one or more tokens in the dynamic
content pane, or enter any combination of text and tokens into the fields.

Note

The Insert row and Update row cards show the names of the columns in the SQL
Database table that's being used in the flow. Therefore, the cards that are shown in the
images in this procedure might differ from the cards that you see.

Update the item in the destination

Next, we configure the action to update the SQL item.

1. Select the Update Row (SQL) action on the True side of the condition to
open the action properties panel on the left.

2. Set the Server name, Database name, and Table name.

3. For the Row Id field, we have to get the primary key from the SQL database.
In this example, it's the CustomerID field from output of the Get Rows (SQL)
action.

Typically when we use a Get Rows action and then want to access the
properties of the output in subsequent actions, Power Automate automatically
creates a For Each loop to handle each item in the list. However, in this
particular case, we know Power Automate always returns one record if a
CustomerID is found because CustomerID is the primary key in our SQL table.
This means we can access the CustomerID property of the first item in the list
using the following expression:

outputs('Get_Rows')?['body/value'][0]?['customerid']

4. Select the Row Id parameter and then type / and select Insert Expression.
Place the code above in the Expression formula bar.
5. Select the Advanced parameters and select which fields you want to
update.
6. Save and test your flow!

Now, whenever an item in your list, the source, changes, your flow is triggered. It either
inserts a new item or updates an existing item in SQL Database, the destination.

Note

Your flow isn't triggered when an item is deleted from the source. If this scenario is
important to you, consider adding a separate column that indicates when an item is no
longer needed.

Exercise - Integrate Power Apps, Power

Automate, and SharePoint
Completed100 XP
 8 minutes

The data that fuels business processes is often buried in separate systems that are
difficult to connect to and navigate. This is one reason why business processes continue
to be complex, and why people rarely stop worrying about them.
The Microsoft Power Platform with Power Automate, Power Apps, and Power BI along with
tools like Microsoft SharePoint make it all easier. Together, these apps and services
provide advantages like these:

 Data can easily be accessed.

 Critical business decisions can be made quicker and more intelligently.
 People can worry less about what their data is doing and concentrate more on
moving their business forward.

This unit gives an overview of:

 Integration of Power Automate with Power Apps.

 Integration of Power Automate and Power Apps with SharePoint for easy
sharing of data in lists.

Add a flow in Power Apps

Adding a flow to a Power Apps application is straightforward.

1. Go to [Link] and sign in by using your organizational

account.
2. Open your app for editing.
3. On the left navigation menu, select the Power Automate icon.
4. From the Power Automate flyout, select Create new flow.
A Power Automate window opens and by default templates will be filtered to
only show Power Apps Templates. You could also select the + Create from
Blank option to build your flow from scratch.
For more about how to create flows, see Create a flow from a template in Power
Automate.

Add a Power Apps application from Power Automate

You can also go in the other direction. You can start in Power Automate and then select a
template to add an app from Power Apps.

1. Launch Power Automate and sign in using your organizational account.

2. In the left pane, select Templates.
3. Select one of the many Power Apps templates.

To see all the Power Apps templates that are available, you can search
for Power Apps.

Once the template is selected and opened, you can start building your flow.

For more about how to create apps by using Power Apps, see Create a canvas app from
a template in Power Apps.

Integration of SharePoint with Power Automate

Customers regularly exchange data between Microsoft Lists and other systems to
support business processes. These scenarios become more powerful through the deep
integration of Power Automate with Microsoft Lists.
Power Automate lets you automate the exchange of workflows and data between
SharePoint and various Microsoft and third-party services. You can create and start flows
directly from a list in Microsoft Lists, and store and change that data in SharePoint.

1. From a list in Microsoft Lists, select Integrate on the top toolbar, and then
hover your cursor over Power Automate.
2. Select Create a flow.

3. In the Create a flow pane, select the template to use.

Power Automate is started, and you can finish creating the flow.

Integration of SharePoint with Power Apps

Power Apps lets you connect to, create, and share business apps on any device in
minutes. You can build efficient mobile forms and apps directly from Microsoft Lists,
without writing a line of code.

Power Apps and Power Automate share a common connector framework. The framework
lets you weave in dozens of data sources that are located on premises or in the cloud.
These data sources include:

 Microsoft Exchange
 Microsoft SQL Server
 Microsoft Dataverse
 Salesforce
 Google
 MailChimp
 Twitter

1. From a list in Microsoft Lists, select Integrate on the top toolbar, and then
hover your cursor over Power Apps.
2. Select See all apps.

3. You can create your application here.

Exercise - Monitor flows

Completed100 XP
 5 minutes
You can view a summary of the number of times that each flow succeeded or failed
today, yesterday, and on previous days. You can also explore details about each run,
such as:

 When it ran
 How long each step took
 If a step failed, why it failed

Prerequisites
 Install the Power Automate mobile app for Google Android or Apple iOS on a
supported device. The screenshots in this unit were taken on the Apple iPhone
version of the app, but the mobile app for Android and Windows Phone is
similar.
 If you complete all of the Units in this Learning Path, you have many flow runs
available for review. If you don't already have a flow, create one now on
the Power Automate website before proceeding with this unit. For easier
testing, use a flow that you can trigger yourself instead of waiting for an
external event.

Tip

For testing, you can set up the flow with your personal email address. Then, when the
flow is ready for real use, you can set it up with a different address (for example, your
manager's).

Power Automate Mobile Flow Tab

1. If your flow hasn't run before, trigger a run to generate data.

It might take some time for the data to appear in the app.

2. From your phone, open the Power Automate Mobile app and sign in. Power
Automate automatically opens the Flow Tab and displays a list of your flows.
3. Select the Bell icon in the top right hand corner to see your notifications.
Flows that fail and other notifications are listed here.
Show details of a run
1. From the Flow tab, select the flow that you want to show the details about.
2. The flow details page opens, where you can see information about the flow
and the flow's run history. Select All runs.
3. Select the flow run you want to see the full history of.

4. The Run details page opens and for each action in the flow, a
green Checkmark symbol indicates the step was successful and a
red X symbol indicates that there was an issue with the step. If it succeeded,
the amount of time that it took (in seconds) also appears.
Check your knowledge
200 XP
 6 minutes
Answer the following questions to see what you've learned.
1.

What are connectors used for in Power Automate?

Connectors are used to start a flow.

Connectors let you build a flow that accepts user input.

Connectors let you connect to a service like Salesforce, Microsoft 365, Twitter.
2.

What is a key advantage of using Power Automate in business process

automation?

Power Automate primarily enhances graphic design capabilities.

It offers complex coding environments for software developers.

Power Automate simplifies the automation of repetitive tasks and integrates various
applications and services.

It is used for hardware integration and physical device management.

3.

When creating flows across data sources do the column names need to match?

Yes.

Yes, but only for numerical column types.

No.

Yes, but only for choice type column types.

Check your answers

Summary
Completed100 XP
 1 minute

Congratulations! You've expanded your skills for creating flows that let you do more
while working less.

In the previous modules for Power Automate, you learned how to build simple flows.
You've now increased your knowledge by learning about complex data sources, flow
scheduling, integration, and complex business processes.
Continue your journey
Microsoft Learn provides several learning paths, based on your role and interests.

Here are some ways to use Power Automate to get more done with less work when you
use Dynamics 365 and the Power Platform. Because these technologies work together,
it's easy to measure your business, act on the results, and automate your workflows.

 In this Power Apps learning path, you'll learn how to build apps that use flows
to track or update business processes on any device.
 In this Power BI learning path, you'll learn how to turn your unrelated sources
of data into coherent, visually immersive, and interactive insights.
 In this Customer Service learning path, you'll learn how to capture, track, and
follow up on sales leads. You'll also learn how to connect to your customer
relationship management (CRM) platform.

What's even more exciting is that you can do all this without writing a line of code!