27 Feb 2020 Power BI Dataflows

Last year dataflows were introduced to Power BI, offering new possibilities in both Power BI Service and Power BI Desktop which we’ll be looking at in this article.

1. What are dataflows?

A dataflow is a new feature recently released by Microsoft in Power BI; basically, it is a collection of entities, very similar to tables in datasets. Dataflows can only be created in Power BI Service and they can be managed using Power Query. With dataflows, the online version of Power Query has been added to Power BI Service.

2. How to create dataflows?

2.1. Steps to create a new dataflow

Inside a workspace in Power BI Service, you can directly create a new dataflow.

Figure 1: Create new dataflow inside a workspace

The process to create a new dataflow is like the Get Data process in Power BI Desktop; the Power Query window opens, and lots of different options of data sources are shown to import your data from.

Figure 2: Power Query shows data sources available

From this point, the process is exactly the same as in Power BI Desktop: the data can be transformed using an advanced editor or by clicking on the different options from Power Query.

Figure 3: Transform data using Power Query

Some new features have been included in Power Query to transform data in dataflows: Map to standard and AI Insights.

2.2. Common Data Models

The Map to standard option allows the user to transform the columns of its query output to attributes of a standard Common Data Model entity defined by Microsoft; there are multiple entities like account, address, product, social activity and much more.

Common Data Model allows the data to be unified in a well-known form with semantic meaning, and it simplifies the integration of data between different apps which use Common Data Model standard entities, enabling these applications to easily read and understand the data.

Figure 4: Map to standard Common Data Model entity window

2.3. AI Insights

The AI Insights are a group of functions added to Power Query, both the online and desktop versions. These functions are only available with a Premium Power BI account and are used to detect the language of a text string, extract key phrases from the user’s data, calculate a sentiment score based on negative and positive phrases, plus image tagging too.

3. Dataflows vs datasets

3.1. Main differences between dataflows and datasets

As mentioned before, dataflows can only be created in Power BI Service and they look very like datasets, but there are some different properties: the table below shows the main differences between dataflows and datasets, which will be explained in the following sections.

3.2. Dataflows in Power BI Service

Datasets in Power BI Service can only be loaded, without the option to manage them, while dataflows can be transformed using Power Query. The main benefit of using Power Query online is that the transformation process runs in the cloud without using PC memory.

Another difference between datasets and dataflows is that datasets can be used in reports in Power BI Service, whilst dataflows cannot.

To create published datasets from dataflows, first publish them to Power BI from the desktop application.

3.3. Use dataflows in Power BI Desktop

To create reports using dataflows it is necessary to import them to Power BI Desktop. As datasets, they can be used as a data source.

Figure 5: Import Power BI datasets and dataflows in Power BI Desktop

When using dataflows as a data source in reports it is possible to use one or more dataflows, and when importing the tables, the user can choose to import only the ones they want to use. Contrary to dataflows, when using datasets as a data source only one dataset can be chosen, so the Get Data option will be disabled. When importing datasets it is not possible to choose the tables to use, so all the datasets will be imported.

Figure 6: Datasets don’t offer the option to choose specific tables

Figure 7: Dataflows allow choosing different tables from multiple dataflows

When the chosen data source is Power BI datasets, the queries can’t be edited, and the data can’t be transformed anymore. Importing data from Power BI dataflows allows the user to make changes and edit queries.

Figure 8: Power BI datasets can’t be modified

Another property of dataflows is the lack of relationship creation between tables. Different tables which share values should be autodetected to be related to each other, but when these tables come from dataflows Power BI can’t detect these relationships. This isn’t a big problem because users can manually create relationships by simply dragging a line between two tables.

Conclusion

Dataflows are a very useful tool for people who work with a lot of data because they can easily create tables and edit them using Power BI Service. There are some scenarios where dataflows are preferable to datasets, as we have explained in this article.

If you would like to learn more about dataflows and how they can help you and your company, please get in touch with us here at ClearPeaks!