Power BI – Create a Dataflow for Reusable Transforms
Dataflows are a great way to give your report writers reusable data source that includes any complex transformations required and doesn’t require unique data logins for every report writer. It just requires access to the workspace that contains your dataflow. This post is a quick guide to create a dataflow and connect to a dataflow.
Dataflow Series
This post is part of a series on dataflows.
- Create a dataflow
- Set up dataflow refresh
- Endorsement
- Diagram View
- Refresh History
- Create dataflow from Export json file
- Incremental Refresh
YouTube Version
Create a Dataflow
Staring in the workspace online in the Power BI service, click on New and then select Dataflow.
In the next screen click on Add new entities button to start creating your dataflow.
The next screen lists all the data sources supported for dataflows. Select your data source. There is a search box in the top right if required. In this example I selected SQL Server database, although I could have picked Azure SQL Server.
As soon as you click in the data source, you will be prompted to enter in connection settings. When complete click Next to continue.
You will then be prompted to select the tables or views to add to the dataflow. You can see previews if that helps. When ready select Transform data button.
This will open an online version of Power Query. Add the transforms you need to each query. When you have finished click Save & close to finish creating your dataflow.
It will then prompt you for a name for your dataflow and an optional description.
Refresh the Dataflow
Although you saw data in the dataflow as you were building it, its not there until you refresh. You get a message for a short while when you save it asking if you want to refresh in the top right hand corner. If you miss that you can return to the workspace and when you hover your mouse point over the dataflow the refresh icon appears.
Connect to a Dataflow
A report, in Power BI desktop, can connect to the dataflow as a data source. Click on Get Data to open the dialog, click Power Platform to filter the options and then select Power BI dataflows. Click Connect to start the connection to dataflows.
When the Navigator dialog appears, expand the correct workspace and dataflow to select the tables. Click Load to load the data into your report. You can click transform to add extra transformations into your data if required.
Note on Refreshes
If a report is based on a dataflow, the dataflow needs to refresh before the report refreshes. Care needs to be taken on setting refreshes to co-ordinate correctly.
Conclusion
Dataflows are a great part of the data reporting structure that can reduce the hit on databases, reduce the number of accounts accessing the data and give the report writers clean well structured data to build their reports on.
