Using Power BI and Microsoft Flow for SharePoint Reporting
Being a data analyst at Code Creators Inc. I keep looking for better methods of data visualization. With the increasing demand and significance of effective data visualization, Power BI has come into trends lately. With user-friendly features for data visualization and reporting, Microsoft Power BI has become one of the most sought after tool by business enterprises. We find Power BI especially useful when it comes to reporting on SharePoint lists and folders on regular basis; which is actually a great to make sure that your end-users are actually adopting and using the tool in right way.
While using it in blend with Microsoft Flow, you can also explore a whole new world of features, knowing that Microsoft Flow can also send data to Power BI automatically, if you want to. And, when it comes to SharePoint, we all know that entire MS Office 365 suite is an assemblage of highly productive collaborative tools; but it will only be as much useful, as the end-user will make it.
In this post, we will teach you about using Power BI and configuring MS Flow for SharePoint reporting.
What is Power BI?
This short overview is for those, who don’t have primary knowledge about the Power BI tool. Power BI is an intelligent data visualization tool originated and managed by Microsoft. The tool is available in multiple versions ranging from Free to Premium Power BI version. Power BI is a cloud-based BI (Business Intelligence) service, along with a desktop-based interface. Both environments allow its users to create dashboards and reports for their desired custom visualizations.
If you have ever used reporting tools such as SQL Server Reporting Services then please don’t get Power BI confused with it, because Power BI has a very attractive, engaging and user-friendly interface. Using Power BI, you can publish your reports and dashboards online to any group of MS Office 365, in just few clicks.
Power BI and SharePoint
Today, a lot of business organizations choose modern intranet on SharePoint in Microsoft Office 365. Hence, it is very useful to create and store Power BI reports on SharePoint development. At Code Creators Inc. we provide extensive training to client companies and their users about how they can use the tool efficiently. Below are the step-by-step instructions given to create a Power BI dashboard while using data from SharePoint.
How to Create a Power BI Dashboard?
1. Download Power BI for Your Desktop
Power BI is available in two versions or environments i.e. Desktop and Online; however, we would suggest using Power BI Desktop for creating efficient and high-performing dashboards. And, you can always publish them with your colleagues by using Power BI Online.
Read also: Microsoft Power BI Key Features & Benefits for Your Business
2. Import Data From SharePoint
Once your Power BI Desktop is downloaded, logged-in and open, then you can simply click on the ‘Get Data’ button in the Home tab, present on the top ribbon, and then can access the data sources that are available to be used.
3. Select The Right Data Type
Choose from three available SharePoint data connectors.
Add your SharePoint URL.
Once you reached to this step, you should be able to see three different types of SharePoint data connectors available;
- Online Lists.
- Folders
- Regular Lists.
Now select your object type and then add SharePoint URL to it.
4. Load Your Data
Now select the relevant list or folder from the left menu. Putting your cursor on the display options, you can see the data preview in the Navigator’s window.
5. Edit Your Query To Make Good Visuals
Before importing and loading your data into Power BI Desktop, you might want to shape or adjust your data. In fact, it is a great idea to edit a certain query before loading it, specifically when working with large data sets. This can be achieved by clicking on Edit and then selecting further adjustment options from the Query Editor.
Query Editor is designed to shape and adjust your data needs. For instance, to keep data that can produce impactful visuals while removing the irrelevant parts.
This can be done through following steps;
- Combining & Shaping Data.
- Grouping Data Rows.
- Pivot Columns.
- Create Custom Columns.
6. Publish Your Report to Power BI Online
You have the option of publishing your report in your workspace or in any of your Office 365 Group workspaces.
When you are done and satisfied adjusting your visuals, you can then publish your reports to Power BI Online, simply by clicking on the Publish button. In case, your Power BI is not activated within your MS Office 365, you will be then asked to activate it then and there.
7. Find Your Published Report On Power BI Online
You can find reports in the Grey Section, in the center of the left column, as soon as you log into your Power BI home-screen. You can also find the following section;
- Workspace: Workspace in Power BI is your personal space of creativity. Workspace could be personal as well as there could be shared groups workspace.
- Dashboard: It is a high-level view of your reports that you have just created. You also have the option to transform your reports by pinning their tiles down.
- Reports: This is the data in report form that you are going to publish from your Power BI Desktop. In order to transform your Power BI Reports into Dashboards, you just need to pin your report tiles into dashboards.
- Datasets: Datasets are basically your data sources. You can go to this section and schedule auto refreshed in order to keep your data sources dynamic.
8. Transform Your Report into A Dashboard
This step is very straightforward. All you need to do is to access your Power BI report and then click on the pin icon.
You can then create or choose the dashboard visuals that you want to be on.
9. Schedule An Automated Refresh
This is a very important step; because your dashboards will never refresh if you don’t do it. Go to the section of Datasets and then click on ‘…’ button, then you can now select and schedule your dashboard refresh. Here you will be given option to defined refresh frequency and to enter your credentials.
10. Share Your Dashboard
Now, your dashboards are fully functional with automated dashboard refreshing features enabled; and these dashboards can be easily shared. However, if you have designated workspace, you are required to grant access to your users. You can now connect them to Power BI Online, or can send them through URL. And you’re done.
Microsoft Flow and Power BI
Now that you have learnt how to set up a Power BI Dashboard by importing data from SharePoint, now let’s go to another step ahead. Let’s bring Microsoft Flow into the mix now, but let’s learn about its background quickly.
What is Microsoft Flow?
MS Flow is a SaaS (Software as a Service) that automates workflow across multiple apps and services. These automated workflows are commonly called Flows, and they are triggered to perform automatically perform different actions when any specific event occurs. With MS Flow, you can now automate tasks such as synchronizing files, getting notifications, collecting data and much more. Basically, MS Flow is a perfect way to automate repetitive and exhaustive tasks. With Microsoft Flow, you can easily automate tasks such as File Synchronization, Data Collection, Notifications and much more.
In this case, we will be using MS Flow to collect SharePoint data for us and then it can be sent to Power BI without writing a single line of code.
How to Create A Microsoft Flow?
- Navigate to the homepage of MS Flow and create an account, if this is your first time to use. You can try out the service for free by choosing the Free Plan.
- Do a quick search to see if the exiting flow template suits your needs. Also, you don’t need to create a workflow from the scratch, if it’s not needed. Now, let’s do a search for ‘SharePoint’.
- Now search for ‘Visualize Power BI’.
- Enter URL for your needed SharePoint site and name the list, you want to track, also mention the destination within Power BI. (In the given case, we chose dashboard as the destination that we just set up). Now click Save.
- Again, search for ‘Update Power BI Dataset’ and turn up another template that could work for you.
- Again, after you confirm your selection, you can see and customize your flow, then click Save.
