Using Power BI and Microsoft Flow for additional SharePoint reporting

As a data scientist at Sharegate, I’m always on the lookout for better ways to visualize data.

A few months ago, I started using Power BI, a user-friendly data visualization and reporting tool created by Microsoft. I find it especially useful to report on SharePoint folders and lists regularly—which is a great way to ensure end users are adopting a tool and using it properly. Now I’m hooked!

Related reading: Learn how to use SharePoint Online, best practices & common issues to avoid

Then, I configured Microsoft Flow to automatically send the data to Power BI for me—and it was a whole new world.

At the end of the day, SharePoint—and the entire Office 365 suite of productivity tools—is a collaborative platform; it’s only as useful as the way end users use it on a day-to-day basis!

What is Power BI?

First things first: what even is Power BI?

Power BI is a data visualization tool that’s available for free through your Microsoft account. Power BI provides cloud-based business intelligence (BI) services, as well as a desktop-based interface—both of which enable you to create your own reports and dashboards for custom visualizations.

If you’re somewhat familiar with reporting tools already, I can guarantee you this is nothing like SQL Server Reporting Services (SSRS). It has a gorgeous user interface and is much easier to use.

With just a few clicks, you can publish your dashboards and reports online and to any Office 365 Group.

Why try Power BI?

1. It’s free. Yes, there’s a Pro version that lets users present even more data. But you should be just fine with the free version for most of your reports.
2. There are close to 65 different data sources available that can be integrated into Power BI. From an SQL database to third party tools like Salesforce, Google Analytics, and others.
3. No coding necessary. You can configure your data using simple operations—or use Simili-Excel operations for even more flexibility. These are called DAX basics operations.
4. Sharing dashboards has never been easier. In Office 365, sharing dashboards has never been easier. You can share dashboards with only some individuals—like everyone in an Office 365 Group—and the online versions are all accessible through your Microsoft Services page. You can even share your dashboards with guests.

Power BI and SharePoint

Many organizations choose to build a modern intranet on SharePoint in Office 365. That’s why it’s so useful to create reports on its usage, and also to store the subsequent Power BI reports there.

At ShareGate, every employee goes through rigorous training on how to use our tools. So I built a Power BI dashboard to monitor the lessons being created and updated on the ShareGate team site—that way I can make sure employees are constantly collaborating to keep the training material current.

Below, I’ve laid out the step-by-step instructions to produce a Power BI dashboard using data from your SharePoint environment:

How to create a Power BI dashboard

Download Power BI for Desktop

Although Power BI has two versions (Online and for Desktop), I suggest using the Desktop version to create the dashboard. Then you can publish it through Power BI Online to share it with your colleagues.

Get data from SharePoint

Once Power BI is open, click on the Get Data button from the Home tab on the ribbon to access the data sources that are available.

Select the right data type

You should see three types of available SharePoint data connectors:

1. Folders
2. Online lists
3. Regular lists

Pick your object type and add your SharePoint URL.

Load your data

On the left side, select the relevant folder or list. A preview of the data that can be loaded should appear in the Navigator window.

Edit your query to make good visuals

Before loading the data in Power BI Desktop, you might want to adjust, or shape, the data.

It’s an especially good idea to edit a query before loading when working with a large data set. You can do this by selecting Edit, and then making further adjustments in the Query Editor.

The Query Editor is where you shape the data to meet your needs—for example to keep the data that will create impactful visuals and remove any data that’s irrelevant.

You can do this by:

• Shaping and combining data
• Grouping rows
• Pivot columns
• Creating custom columns

For a full list of query tasks and instructions, check out the official Microsoft documentation.

Publish your report to Power BI Online

When you’re satisfied with your visuals, publish the report to Power BI Online by clicking on the Publish button. If you haven’t activated Power BI in Office 365, you’ll be asked to activate it.

You have the option of publishing your report in your workspace or in any of your Office 365 Group workspaces.

Tip: I suggest creating new designated workspaces if you plan on sharing your dashboard with colleagues. This gives you more control over who has access to your dashboard, and also lets you collaborate with colleagues. Read about how to create a new workspace experience in the official Microsoft documentation.

Find your published report on Power BI Online

If you go to the Power BI home screen and login, you should see the report in the grey section in the middle of the left column.

Transform your report into a dashboard

This part is pretty straightforward.

All you have to do is access your report and click on the pin icon.

Then, you can select or create the dashboard you want your visual on.

Schedule an automated refresh

This step is very important. If you don’t do this, your dashboard will never refresh.

Go under the Datasets section, find your dataset and click on the … button. Then select Schedule refresh. You’ll have the option to enter your credentials and select the refresh frequency.

Share your dashboard

You now have a fully functional, automatically refreshing dashboard that can be easily shared!

If you create a designated workspace, you’ll need to grant access to users. Then, you can ask them to connect to Power BI Online, or you can simply send them the URL—and voilà!

Microsoft Flow and Power BI

Now that you know how to set up a Power BI dashboard using data from SharePoint, let’s go one step further: let’s bring Microsoft Flow into the mix. But first, a bit of background.

What is Microsoft Flow?

Microsoft Flow is a SaaS that automates workflows across a number of applications and services. These automated workflows are called flows, and are triggered to perform different actions automatically when a specific event occurs. With Flow, you can automate tasks to get notifications, synchronize files, collect data, and more.

Basically, Microsoft Flow is a great way to automate repetitive tasks.

With Flow, you can automate tasks to get:

• Notifications
• Synchronize files
• Collect data
• And more!

In this case, we’re going to use Flow to collect our SharePoint data for us, then send that data to Power BI—all without needing to write a single line of code!

How to create a Microsoft Flow

1. Now, navigate to the Microsoft Flow homepage. If it’s your first time, create an account—there’s a free plan that lets you try out the service.
2. Perform a search to see if an existing flow template can accomplish what you want. You don’t want to create a new flow from scratch if you don’t need to. Let’s do a search for “SharePoint”.

3. Hmm… nothing is quite right. Let’s try searching for “visualize power bi”.

4. This seems like it could work for us! Now enter in the URL for the relevant SharePoint site and the name of the list you want to track, as well as the info for your destination in Power BI—in this case, the dashboard you just set up. Then click Save.

5. Another search—this time for “update power bi dataset”—turns up another template that could work.

6. Again, after you confirm your selection, you can see and customize your flow, then click Save.

For step-by-step instructions on creating a flow from scratch, check out the official Microsoft documentation.