Dataflow Creation and Usage in Power BI – The Self Service ETL

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.

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

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.

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.

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

Choose the appropriate tables inside to fetch the data from.

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.

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.