Dataflow is the initial Data Preparation that takes place in Power BI for the report, to begin with. Power BI follows an ETL-Extract, Transform and Load process to perform the function. Power BI now brings the flexibility of ETL to be self-service through simple interface/navigation. Dataflows creation is performed inside the Power Query functionality.

dataflow-creation-usage-power-bi-self-service-etl

Image 1: Structure in Power BI Data and Reporting

 

Data Flows can be easily be created by performing the below steps:

1. Navigate to your workspace and select on Dataflow
2. Go to the +Createon the top right to bring in a dropdown
3. Select the option –> Dataflow

dataflow-creation-usage-power-bi-self-service-etl

Image 2: Data Flows Creation

 

The below mentioned 3 options will be visible inside the Dataflow creation:

1. Entities
2. Linked Entities
3. Common Data Model

 

1. Entities

An entity is a set of fields that are used to store data, much like a table within a database.

dataflow-creation-usage-power-bi-self-service-etl

Image 3: Choose an option

 

Select an appropriate entity to start the dataflow creation. The user could see a simpler and rich UI design screen, helping us to choose the data source connection we need. This is applicable for cloud, on-premise or even a simple excel sheet.

dataflow-creation-usage-power-bi-self-service-etl

Image 4: Data Sources in Power Query

 

Now, select the data source that you need to connect to your data.

dataflow-creation-usage-power-bi-self-service-etl

Image 5: Connectivity gateway for the data source

 

Choose the appropriate tables inside to fetch the data from.

dataflow-creation-usage-power-bi-self-service-etl

Image 6: Tables in Data Source

 

Once the tables are selected, we can then proceed to use the dataflow editor – ETL. This step is very similar to the initial power query we have for cleansing our data in Power BI desktop but hosts much more advanced functionalities to cleanse, refresh and schedule your data.
Once you’ve created a dataflow, you can define its ‘refresh frequency’ by setting a refresh schedule in the dataflow settings. You can also use the Advanced Editor for defining your queries in M language.

 

2. Linked Entities

Linked entities allow users to reuse data which exists in the lake, thereby allowing them to manage and organize projects with complex ETL processes, from one or multiple sources, while also letting analysts build on each other’s work.

 

3. Common Data Model

These are Microsoft Standardized Schemas for your data. Once we have finished our cleansing process we can start with the transformation mapping fields process and leverage the use of a common data model. To leverage the Common Data Model with your dataflow, click on the ‘Map to Standard’ transformation in the ‘Edit Queries’ dialog.

dataflow-creation-usage-power-bi-self-service-etl

Image 7: Mapping Fields

 

If any fields do not get mapped to the common data model fields they are pushed to be null. You can then proceed to save your dataflows. Finalize your dataflows and create ‘scheduled refresh’ for your data.

dataflow-creation-usage-power-bi-self-service-etl

Image 8: Refresh Scheduling in Power BI

 

Now you can consume the dataflows directly in Power BI Desktop and use them for your reporting and analysis.

 

Know more about Microsoft Power BI services offerings from Visual BI solutions here.

Subscribe to our Newsletter

5600 Tennyson Pkwy
Suite 120
Plano TX 75024.

+1 888-227-2794

+1 972-232-2233

+1 888-227-7192

solutions@visualbi.com