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

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

Jun 12, 2019

SHARE

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 and Usage in Power BI – The 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 and Usage in Power BI – The 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 and Usage in Power BI – The 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 and Usage in Power BI – The 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 and Usage in Power BI – The Self Service ETL
Image 5: Connectivity gateway for the data source

Choose the appropriate tables inside to fetch the data from.

Dataflow Creation and Usage in Power BI – The 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 and Usage in Power BI – The 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 and Usage in Power BI – The 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.


Corporate HQ:
5920 Windhaven Pkwy, Plano, TX 75093

+1 888-227-2794

+1 972-232-2233

+1 888-227-7192

solutions@visualbi.com


Copyright © Visual BI Solutions Inc.

Subscribe To Our Newsletter

Subscribe To Our Newsletter

Join our mailing list to receive the latest news and updates from our team.

You have Successfully Subscribed!

Share This Blog!

Share this blog with your friends and colleagues!