Best Practices

 View Only

Ian's Insights Ep. 9 - Microsoft Lists and PowerBI: What to do with your data!

  • 1.  Ian's Insights Ep. 9 - Microsoft Lists and PowerBI: What to do with your data!

    Posted 06-02-2022 10:00

    Lists and PowerBI - What to do with your data!

    Did you know that the PowerBI Desktop app is free in the Windows Store? Did you know that you can easily grab data from your Sharepoint List or Document Library and push it into PowerBI? Did you know that you can easily visualize your data that is collected with ProntoForms inside PowerBI and publish it to your users? All of this is possible and you likely already have access to all these features if you are using Microsoft for other aspects of your business.

    In this article, I am not going to get into how to model your data or how to use PowerBI (there are tons of online resources for how to use and play with PowerBI). I am just covering how to connect your List to PowerBI. You've been using ProntoForms to collect data, why not make that data look nice and presentable

    Step 1: Download the PowerBI App from the Windows store (sorry Mac users....)

    Step 2: Have some data flowing already into your Microsoft List. If you don’t have this set up already, I would suggest you start! Data is only as good as the story that it tells! Here is a great video on how to get going: Microsoft SharePoint List Integration Video | Integrations

    Step 3: Make that connection between PowerBI and your Microsoft List.

    1. Open up PowerBI Desktop and click on ‘Get Data’
    2. In the search box, search for List
    3. Select SharePoint Online List and Connect
    4. It will then ask you for your Site URL, this would point to the site where your list is located. In my case, it is the URL of my sharepoint with /sites/Implementation after it.
      Will look like: XXXXXX.sharepoint.com/sites/Implementation (replacing XXXXX and Implementation with what is applicable to you)
    5. It will prompt you for your credentials to log into the Sharepoint Site, you will likely use Microsoft Account to do this.
    6. Once you have connected and it has loaded, it will give you a list of your available Lists, find the one that you want to use the data from and click Transform Data at the bottom of the window. We will want to change some of the data types before we load everything in.
    7. Side Scroll through your columns that have been pulled into the Power Query Editor, looking for Date/Time columns or columns that can be classified as a different data type. To change the data type, select the column header and in the Home > Transform ribbon you can select a data type:
    8. Once you have gone through all the columns and ensured that the data types match what you are working with. Click on the ‘Close & Apply’ button. PowerBI will then begin to load your data in a model that you can build with.
    9. You will see on the right side, some icons for Visualizations and all of the Fields that are available from your List.
    10. Now this is where you will need to think about what you want out of your dashboard and this is where I will leave you in your journey to tell the best story that your data can tell.
          Here is an example of a dashboard built using ProntoForms data:


          ------------------------------
          Ian Chamberlain
          Solutions Architect
          ProntoForms
          ------------------------------


        Reminder: Content posted to our Community is public content.  Please be careful not to post Intellectual Property that you do not have permission sharing.  For more information please refer to our Terms Of Use