Create a streaming dataset in Power BI using Microsoft Flow
Microsoft's Guy in a Cube channel on YouTube has been providing tips and tricks for Business Intelligence since 2014. (If you haven't visited their channel yet, check it out!) Earlier this week, host Patrick Leblanc talked about how to use Microsoft Flow together with Power BI to set up a streaming dataset and monitor it to stay on top of your critical business information.
Microsoft Power BI is one of the most popular and feature-rich data visualization tools available. It's also part of the same business applications, platform, and intelligence organization as Microsoft Flow, which means integrating the two is easy and creates powerful possibilities. For example, let's imagine that you wanted to set up a workflow automation to alert you when a technical certificate is about to expire. You have a table with four columns for the certificate name, expiration date, the number of days left until expiry, and the certification status.
We want to use Microsoft Flow to take this data and turn it into a streaming dataset in Power BI, which can then have reports and dashboards built on top of it. How you're storing this data set is up to your organization — you probably already know that Microsoft Flow has a connector for almost everything, from a highly optimized SQL Server or MySQL database to a simple Excel workbook or Google Sheet. Once you've identified your source, you can set it up as streamingdata set on the Power BI service. After logging into the service, click the Create button in the top right corner, and select Streaming dataset.
Microsoft Flow can push data directly through the API, so select that as your data source. Give the streaming dataset a name, and enter the names of the columns from your original table that you want to include. When you're done, it will look something like this:
It's time to create the flow! Log into Flow, and Create from blank. Next, select the Schedule connector and specify when the dataset will update. Click New Step, and then click Add an Action.
Find your data source in the list — SQL Server, Excel, whatever you're using. You want multiple rows, so select Get rows from the available actions. Connect to your data source, and choose your certification table from the drop-down list. Click New Step and Add an Action again, and find Power BI in the list of connectors. We want to Add rows to a dataset, so enter the information needed to connect to your Power BI workspace, dataset, and table. Drag and drop to match the columns from your original dataset to the columns in your streaming dataset.
Save your flow. It's ready to go! After your flow has run at least once, you'll have access to the data in Power BI, and can use it to build up-to-date insightful reports and dashboards that can be shared across your organization.
Looking good! For all the details behind this flow, and to see Patrick go through each of these steps, watch the Guy in a Cube video: