Step 1. Select the Workspace 0:37
Go to desired workspace where you wish for your dataflows to reside.
Step 2. Create Dataflow 0:54
Click Create on the top right and select Dataflow. This will allow you to begin the process of building out your dataflows.
Step 3. Add New Entity 1:00
Click Add New Entities. This will allow you to select from a multitude of data sources in which you can connect your dataflows.
Step 4. Select Data Source 1:29
You now see a multitude of data sources that Power BI can connect to. For this example, I connect to an Azure SQL database with example data. If you wanted to connect to your own SQL Server instance you would select SQL Server Database, but the logic for filling out the connections for dataflows is the same.
Step 5. Connect to Data Source 1:44
Fill out the connection settings in order to connect to your data source. Make sure that your gateway is configured properly to ensure you have valid credentials. If you have valid credentials, you will see that Power BI fills out the credentials for you. Click Next.
Step 6. Select Database 2:37
Select the database, as well as the tables that you wish to use from your data source. As you can see in the video, I select the SalesLT.Customers table to bring into my dataflow. After you have selected your tables, click Transform Data.
Step 7. Use Advanced Editor 3:53
You will be directed to Power Query within the Power BI service. You will notice the SalesLT.Customer table is present; however, I want to make some transformations. While you can use tools inside of Power Query, you can also use SQL queries within advanced editor to get the data that you desire. Click on Advanced Editor.
Step 8. Use SQL Query 4:02
You can see that the Advanced Editor has the necessary let and in statements, along with the Azure SQL database which is the source we are pulling from. We can actually rewrite this to allow us to use a SQL query to transform the data. To do this we write let Source=Sql.Database(“SQL Server Name”, “Database Name”, [Query= “ “,]) in Source. You can see the example within the video.
Step 9. Name Dataflow and Refresh Data 4:36
Select Save & Close to save the transformations you made to the data. Type in the name of your dataflow into the prompted box. You also have an area where you may leave a description of what your dataflow holds. Click Refresh Data to get the updated data. This can take a bit longer, as you can see in the video.
Step 10. Get Data in Power BI Desktop 6:32
Open up Power BI Desktop. Click Get Data. Under the options, click Power BI Dataflows. Under this option, you will see all of the dataflows that you have created within this workspace, as well as dataflows within other workspaces. Click on the workspace you built your dataflows in. Click on the table or tables you wish to import into Power BI. Now you have effectively created a dataflow in Power BI!
From database architecture and design to the deployment of best in class data platforms and business intelligence tools, the Advanced Analytics Group can transform complex data into insights which drive digital transformation.