As everyone knows, Power BI is one of the best tools in the data analytics market for creating and sharing reports using the Power BI service.
In this blog, we will explore all the easy ways to migrate queries to dataflows, which allows you to remove queries from the Power BI report and make them available to consume multiple times into multiple semantic models from the same source.Â
We will also explain the backend process of establishing gateway connections and its advantages, which help in reporting effectively through Power BI. But before that, we need to know about Dataflows.
What are Power BI dataflows?
To learn about dataflows, first, we need to know what transformation engine is used in Power BI. Power Query is a transformation engine in Power BI. Dataflows use Power Query to transform data online and store it independently.
Generally, the data in a dataflow is stored in Azure Data Lake Storage Gen2 and Dataverse.
Dataflows can only be created in Power BI Service (Cloud only). They cannot be created in Power BI Desktop, but they can be used by Power BI Desktop to query data. Dataflows aren’t just for Power BI; they can also be created in Power Apps and many other tools.Â
An Overview of Data Flows:
Why is Power BI Query Migration Important?
Users can edit single Power BI files one at a time, but what if the same user needs to work on multiple Power BI semantic models/Power BI Reports at the same time?
To resolve this, we will use dataflows to remove the power query code from the Power BI report and make it available in multiple other Power BI reports from the same source.
How to Migrate Queries to Dataflows
Open/Create a Power BI file in Power BI Desktop:
Users must open/create a Power BI file in Power BI Desktop. After importing the required data to migrate into the file, select Home in the ribbon & click on Transform Data.
Copy the queries from the Power BI file:
The user should open the Power BI file and go to Power Query. Then, the user should select and right-click on the required table/query and click on the Copy option. To copy multiple tables/queries, we must create folders and group the queries, which is a best practice.
Users can group the required tables/queries and Parameters in Power Query on the Power BI Desktop and then bulk copy them at a time, which can help tables stay organized and save the report.
Creating a dataflow in a Workspace in Power BI service.
Users need to open the Power BI service and create a dataflow in the required workspace, Which can be done by selecting the New option in the top left corner. Dataflows use Power Query to perform data transformations, which run online and store data independently. Dataflows are not just for Power BI; it can also be created in Power Apps and many other tools.
Prerequisite: Dataflows and Workspace should be created in the Power BI service.
- Adding queries in dataflows:
There are two approaches to adding the queries in dataflows in the Power BI service.
- Â
Approach number 1:
Open the Power BI file and go to Power Query (click on Transform data). Create a folder with multiple tables, just like the above step.
Then, Go to a dataflow in the Power BI service and select Add new Tables to define them.
Then select Blank query on the following screen, click Next, and then delete the existing query.
Go back to Power BI Desktop and open Power Query. Select the folder created with multiple tables in it, then right-click and Copy.
Switch to the Power BI service, which is opened on the web, go to Power Query in a dataflow, and paste the folder/tables on the queries pane. This imports all the required data from Power BI.
- Refresh a dataflow after giving the required data source connection.
Approach number 2:
After importing data/tables into the Power BI desktop query, right-click on the required table and copy the M-code/query.
Add a blank query in the dataflow and paste the copied code into the blank query
Configure the connection, save & close the dataflow, and refresh it.
Establishing a Connection Between the Power BI Model with Dataflows
Below are the steps to create a connection and refresh the dataflow in service,
Go to Power BI file and go to Power Query (Transform data), to which you need a dataflow connection.
Create a new connection from the Home tab, New Source > More > Power Platform > Power BI dataflows.
Select one of the entities from the dataflow from the window. (repeat the same step for all other entities as well)
Copy the query to the advanced editor of the respective table in the Power Query in Power BI Desktop.
Go to the source table, paste a dataflow M-query (formula language used in Power Query) in the advanced editor & then delete duplicate queries.
Repeat the same steps for other tables and entities as well.
Benefits of this approach: All existing measures and calculated columns will not have any impact. They should work as expected.
Publish the report, refresh a dataflow, and report it in Power BI Service.
Advantages of Query Migration to Dataflows
There are lots of cool features hidden in creating these, such as:
Semantic model Re-usability: Using this process, one can create a shared set of data sources by deploying respective reports to the Power BI service workspace and later establishing a live connection in other semantic models.
Efficiency: Experience seamless integration with Power Desktop reports.
Closing
In conclusion, migrating queries to a dataflow is important to decrease the load on data sources and data duplication and also to ensure the reusability of data in different Power BI reports. Creating dataflows in Power BI is very well explained in detail in one of our posts; please check it out.
If you need any assistance in exploring more effective ways of leveraging this feature, do not hesitate to reach out to our team of experts for support, guidance, and best-practice recommendations. Contact us today!
FAQs
What is the difference between a dataflow and a dataflow Gen2?
Dataflows Gen2 has a strong integration capacity beyond Power BI/Power Platform and embeds Machine Learning models extensively where dataflows cannot.
Can dataflows use direct query connectivity mode?
Yes, it is possible. In fact, connecting to a direct query is more advisable than importing the data because it will ensure high data scalability and prevent data duplication. See this post for more details.
