Sailing out of castaway island

In your business applications, thinking of data integration Or even migration to Power Platform, Dataflows help in getting out of the castaway island in the stranded situations. Microsoft Dataflows features become generally available in the power platform maker portal in Oct 2019 and can be used both in the PowerApps portal or in PowerBI. Using the PowerQuery, dataflow provides a very rich set of connectors to interface with not only the databases, and files but also with Open standards like OData, ODBC and restful Web APIs. Yes, it is the same no-code PowerQuery that we have been using for years in PowerBI. In summary, it is the most advanced Extract Transform and Load (ETL) tool present today that provides self-service capabilities to you in the ever-growing volumes of data to convert into actionable insights.  

Power Platform Dataflows

To drive this home, I set up a dataflow as a sample to migrate the contacts data from a SharePoint environment to the Power Platform entities and it just took few minutes to configure the fully integrated Common Data Service environment. If you want to bring in any other data outside your business application or even your legacy system, like accounts, products, projects (Project Online) or any other data you would imagine, it can be easily done in the same way. 

Navigate to the power platform maker portal and click on new Dataflow in the left navigation pane. In the next screen, I select OData data source and you need the credentials for reading the data only. I could have used the SharePoint connector here, but I preferred OData because it is a good example that will fit many systems complying with OData standards. Yes, if you have the data in your on-premises environment, you can bring it to the surface using the data gateway

OData data source in PowerQuery

In the next step, you will be shown the entire content of the data source. Here, you can select columns you are interested in, filter rows based on your requirements or even can add calculated columns.

The next screen is a simple mapping of your data source to CDS Entity fields. If there is no mapping of your data on any existing entity, you can bring the data as a new entity. It will ask you to select a primary key column and the entire data source will be replicated in the CDS entity.  

Dataflows allow up to 48 auto data refreshes on a daily basis. In PowerBI, we have the limit of up to 8 auto-refreshes per day. This limit is quite generous and it means we can auto-refresh data every 30 mins. The other option is manual data refresh and this can be handy for one-time data import or if you are planning to do data refresh based on certain actions that can be automated too. 

and here comes the Data imported in CDS Entity surfacing on the model-driven app.

Power Platform Integration Dataflows is a vital tool

Power platform dataflows helps in bringing the data from several data sources. Once the data is loaded into the storage, we can use it in Apps, MS Flows and produce reports in PowerBI too.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: