Susan G
In our last blog post, Susan and I talked about how we had used Power Automate to improve the way we gathered training evaluations. In this blog we’d like to go a step further and explain how this led to improving the related performance measure. Whilst we had improved the way we gathered the data, it still had to be analysed and made into a visual performance measure that told you what you wanted to know at glance. This involved calculating Net Promoter Scores (NPS), creating pivot tables on Excel, exporting to PowerPoint, and manual updates of any written feedback. It was at this point that Jacquelyn stepped in with an idea to improve this part of the process.
Jacquelyn
Susan A and Susan G did a fantastic job of making the process of gathering training evaluation data fully automated and, we hope, providing a better experience for the participants. But as Susan G says, she was still left with manual work to do to summarise the results and display them for review.
This is where Power BI comes in!
By importing the excel data into Power BI I was able to add a column which calculated an average rating for each evaluation. This could then be used to calculate an overall average and averages by course type and by date. This allowed us to easily compare courses and see how ratings have been affected by a change in content or delivery.
It took time to get to the point where we were happy with how the data was displayed in our dashboard. We started with a prototype, close in appearance to the PowerPoint version and gradually made improvements. For example, we initially had two pages, one for internal evaluations and one for external, but Power BI makes this unnecessary with a filter allowing us to toggle between the two types on one page.
We also added date and course title filters that allow us to go straight to the month and/or course we want to examine. All the comments come through to Power BI too which makes reviewing them and discussing them straightforward.
The only issue remaining was that with the data stored in a table in Excel, we kept getting an error message about some rows not loading. This was simply because they were blank, ready for new evaluations to come through via the Power Automate flow. Not a huge issue but one which we knew from experience could be resolved by using a Microsoft List. Back to Susan A…
Susan A
Jacquelyn cautiously approached me and asked if the data source for the performance measure could be moved onto a SharePoint List rather than an excel spreadsheet. I say cautiously because of the work that had already been done – not because she thought I would disagree.
I was happy make this change– it meant messing about with the Power Automate flow and learning a bit more about SharePoint Lists, so I saw this as a chance to play around and practice with it again.
I hadn’t much experience using SharePoint Lists, but turning a spreadsheet into a SharePoint List was actually really easy. One of the options when creating a List is to use an already existing spreadsheet. It is simply a case of ensuring that you use this option when creating the List – I should know – it took me an attempt or two! Importing the data from excel, then a few tweaks to make it the way we needed it to work and changing the column types didn’t take long.
The spreadsheet had had a tab for internal evaluations and one for external, but Lists doesn’t have that functionality, so we had to find a way to distinguish the data from each of these. I added another column to the List for the Power Automate flow to populate the field based on the type of evaluations that were completed so that the dashboard could show them separately.
Changing the Power Automate flow was quite simple, it was almost the same as the old flow, but updating a List rather than a spreadsheet. I set the flow to distinguish which form (internal or external) the participant had completed it and added in the date and time it was submitted. The aim of the changes was to help with the dashboard, but it did provide the opportunity for us to make a few little amendments to the process we talked through in the last blog post.
Susan G
All of this has meant that the most I need to do to gather the data and display it in a way that we can all understand, is hit the refresh button! No more pivot tables and excel pie charts! A bonus about the dashboard is that we can drill down further into what’s displayed, giving us meaningful information about each course. For example, if we make any changes to a course as a result of the feedback we received, we can drill down by date to see if the feedback changes accordingly the next time we run the course, all at the click of a button!
Susan A
So what did we learn from all of this?
Displaying the data on a Power BI Dashboard is much easier than you might think. As long as you have a good source of data, you can show the information in an interactive and informative way.
The three of us have different interests and skill levels with the MS Power tools, but working together, meant that we were able to quickly and efficiently put together the data we needed for the dashboard and then create it. An openness to making changes and experimenting; an understanding of excel and Lists, as well as an ability to use Power BI (whether beginner, intermediate or advanced – like Jacquelyn) is all you need to be able to transform your data.
Help is available from many different sources – we were able to work with each other in this case and could now help others looking to do something similar. Please do not hesitate to get in touch with us if you have an idea and need help. There are other sources available, like the University’s 365 Training site. More advanced help with using Power BI is available from the Cloud Transformation Teams site where you can ask questions and get helpful advice from other users.
.jpg "Susan Greer smiling to camera")