This blog is part of a series on Teams. For more articles, check back often

Written: 20/06/2021 | Updated: N/A

I don’t mind putting up my hands and admitting that even though we are only halfway through 2021 this year has been particularly full on. I’ve already hit a few of what I like to call ‘crush periods’ where the totality of work, community, exams, and everything else reaches a point where you think ‘how am I going to surmount this? Of course, this may be something you have experienced yourself; that point which borders of getting out of hand and the term unsustainable applies. But you know what? It’s been surprising too. These periods haven’t been where I would have expected or anticipated them to be given that I have a lot of experience in conclusively dealing with volume and actively managing workload. They’ve typically emerged out of left field and can then disappear again just as quickly. Now, it’s important to state for the record that I consider this to be very much a first world problem. Given what has happened to many during Covid I am extremely lucky and privileged irrespective of how it gets since I have the luxury of a job and one which is in demand; and if I am being totally honest with you I am also a big reason for my own time poverty since I like speaking on the circuit, and writing blogs like this, and teaching, and many other things besides. The point is, in relation to a busy life it’s becoming really really important to use what is within my means to economize my time. If you have been following the blog over the past several months you may have noticed: a lot of that involves the Power Platform. In my job, I work with others trying to build Power Apps for things which significantly improve existing business processes. I am personally trying to automate my way out of as many things as possible which I do manually with Power Automate. I build dashboards and reports in order to make better and faster data driven decisions. With Power Virtual Agents, I am loading a bot with Q&A so others don’t need to come and ask me about it again and again and again. So as a big advocate of Microsoft Lists, a subject which I’ve written and spoken about often in the past – and will do once again at the European Collaboration Summit in November – it’s been really awesome that Power BI reports can be built on top of Lists. Let’s have a look at this, and then let’s see if we can get this into the Team

This blog will cover

  • Setting up the team/channel ready for the list and the Power BI Report
  • Setting up the list in the Team
  • Creating and Publishing the Power BI Report based on a List
  • Editing the Power BI Report based on a List
  • Getting the Power BI Report into Teams
  • Falling back on an alternative if the Power BI App needs to be used
  • Closing thoughts

Note this blog will have abridged steps which will assume some experience with Teams and navigating the Microsoft 365 environment. I will refer to past blogs throughout

Pre-requisites

  • Teams Licence, SharePoint Licence (In an Office/Microsoft 365 Subscription)
  • Power BI Pro Licence (for Sharing)
  • Permissions in Teams to add tabs and Lists/Power BI App (App Permissions)

SETTING UP THE TEAM/CHANNEL READY FOR THE LIST AND THE POWER BI REPORT

Whilst you do not need to be Team owner to do this, this example will assume you are. Use an existing Team and existing channel or set up the Team and channel ready for the List and the Power BI Report. As you can see here I have a Team called Microsoft Lists, a private channel which is called ‘List Power BI’ (functional so it’s easy to remember) and I am a Team Owner. This channel has several members including Vesku, Mar, Marco, Adam and Karoliina

SETTING UP THE LIST IN THE TEAM

Before we can generate a Power BI Report, we need to add and populate the list. There are several ways to create the List in Microsoft Teams as outlined in this previous blog including from scratch, via excel, via an existing list or from a template. This list, in this fictitious example, is going to be a master list of all the company owned devices which have been issued to users within my organisation and I am going to build this from scratch

1.) In the channel select + to add a tab

2.) Search for and select Lists

3.) Select Save

4.) Select Create a List

5.) Select Blank List

6.) Enter a name, description, icon and colour for the List and then select create. Per the fictional scenario this is to explicitly show that the List being built is to track and manage company owned devices

7.) The List is now created

8.) At this point you will want to build out your list with the appropriate columns and list items. Within this fictional example of devices the list would contain things such as the device owner, what operating system it has, where the owner is based, when it was procured and when it was last serviced. Try different column types for different columns and populate the list with data you will want to ultimately measure. Also, drag and drop the columns to ensure it looks correct in the all items view.

9.) Once done, ensure that members of the team have the right permissions on the list. This can be accessed by selecting the info icon on the top right of the list, and then selecting manage access. By default, members of the team have edit permissions however I have set this, for this fictional example, to be read

CREATING AND PUBLISHING THE POWER BI REPORT BASED ON A LIST

In Teams, the Power BI Report for the list cannot be created because there is no integrate button currently. Clicking on the More Options (…) only gives the options to export or open in SharePoint

1.) Login at https://login.microsoftonline.com and select the Lists icon from the app launcher

2.) In the Lists Web App select the List you are working with. This example uses Core Equipment List

3.) Select Integrate then Power BI then Visualise this List

4.) The list is now visualised. Review the report and edit it at this point – or come back to it later (see next section). This example assumes it is ok to publish so select Publish to the List

5.) Enter a name for the report and select publish

6.) The report is now saved

7.) The report now appears within the Integrate section under Power BI and Visualise this List. Select it to open the report

EDITING THE POWER BI REPORT BASED ON A LIST

The Power BI Report is now published and should update regularly over time as list data is added. However, you may wish to return and tidy it up or amend a specific visualisation since – from this experience, the generated report does not use all data from all columns in the list.

1.) Return to the list within the web app and select Integrate then Power BI then the list name (in this case Device Report)

2.) Select Edit

3.) Select Continue. When first accessing edit mode you’ll lose access to the summarize pane after this point. This is ok since you have everything you need moving forward with the editing tools

4.) The Summarise pane on the right is replaced by the Visualisations and Fields panes where you can amend the report and visualise what you want to see. As you can see below, after editing the second image looks a lot more coherent, uses all the fields of the data and presents a lot more information including device onboarding over time, device servicing over time, device proportion of estate, the number of devices enrolled into Intune, devices by vendor, % of devices both onboarded and enrolled, as well as breakdown of OS version which shows a need for update. The slicer down the bottom can be used to target specific devices

5.) Once done, select Publish to the List at the top left

6.) The report is now saved

7.) Adding items to the list will update the report over time

GETTING THE POWER BI REPORT INTO TEAMS

The new functionality is great. Reports easy to spin up off the back of Lists, all whilst working within the List. However, the question then is can you easily get these into Microsoft Teams? The answer is yes and no.

Yes, because there is a way. And no because at the time of writing you can’t do it via the Power BI App. This is because the Power BI report based on the list doesn’t seem to appear anywhere in Power BI. The report does not appear in your workspace. The report or the List does not appear when trying to connect to SharePoint within Power BI. It cannot be shared via the report created previously; it does not appear in your recent or favourites.

So how then can we open this particular report in Teams? An old fashioned way

1.) Copy the URL of the Power BI report created off the List in the browser

2.) In Teams, select the Team, then Channel then + (Add). Search for and select Website

3.) Copy the Power BI Report URL there and select Save

4.) The Report appears in Teams

The benefits of this is that it’s quick and others in the Team will have permissions on the report since they have permissions on the List. However, an opportunity for development is that this report can/should appear in Power BI since it lacks the integration we are expecting to see: especially since the Power BI App and Teams is now integrated to a high degree

FALLING BACK ON AN ALTERNATIVE IF THE POWER BI APP NEEDS TO BE USED

Ok, using the new functionality is awesome. The Power BI report is easy to create, easy to publish, easy to edit should we need, and we can get it into the Team via the website application within Teams itself. Except, what if the org wants or needs to have the List data in the Power BI App, the report in the Power BI App, so the report can – for example, be consumed within the personal app on the Teams App rail?

Well, this could always build it using App Studio which works

However, for the full experience we’ll fall back onto a method that exists today for Microsoft Lists

1.) Download, install and run Power BI Desktop. Download is here

2.) Select Get Data and then More

3.) Search for SharePoint. Select SharePoint Online List then Connect

4.) Enter the Site URL the list is housed in and select Ok

5.) Select Microsoft Account, ensure the level is correct (the URL) and then Sign In

6.) Once signed in select Connect

7.) Select the List and then Load

8.) As you can see the canvas is empty. This will have to be built from scratch.

9.) It takes a bit more time, but as you can see, this looks like the Power BI created from the List.

10.) Once completed, select Publish from the Ribbon

11.) Select Save

12.) Save a copy of the PBIX file. Here you see it saved to OneDrive for Business synced to the Desktop

13.) Enter your corp address and select Continue

14.) Enter credentials and then Sign In

15.) Select your workspace and then select

16.) The report is now published. Select Got it

17.) In the Power BI Web App the report appears in the workspace. Viewing the Lineage will show that the report is connected to the list housed in SharePoint

18.) Click on SharePoint and if the connection status is disconnected, resolve by setting OAuth2 at an organisational privacy level and re-entering your credentials. Ensure the connection status is successful. Remember also to grant the Team members access to the report under Manage Permissions otherwise they will not be able to see it in Microsoft Teams

19.) In Teams, in the Channel of the Team select + (Add) and then search for and select Power BI

20.) Select the report created and select Save

21.) The report is now open in the Power BI App in Microsoft Teams. Updates to the list in Teams or the lists web app will update the report

22.) It can also be opened from the Personal Power BI App on the Teams App rail. As shown here, it has been marked as a favourite so it can be easily accessed anytime

CLOSING THOUGHTS

Our job here is done

This new functionality of being able to generate Power BI reports off of Microsoft Lists – it is easy to do. It can be done in a matter of minutes. The reports are closely linked to the List and ultimately we can get them into Teams albeit via a web app in a channel or as a personal app via App Studio. The limitation we have at the time of writing is that they don’t surface and we can’t surface them in the Power BI app itself and because of that we lose a level of integration that others may want to see. Some will say this – it’s a Power BI report why can’t I see it in Power BI? I admit the question isn’t without merit. The same applies in terms of using the report with other apps such as Power Automate. How can Power Automate work with the report if it’s not even showing up as a report in Power BI? Now of course, the Power BI report created off the back of the list may be enough for some; it’s quick and you don’t need to be technical to create it – even to do visualisations given that you have some time to get hands on and play around. Yet I imagine others will want their Power BI reports based off lists in the Power BI App along with all their other reports, and in so doing will happily continue to build these reports in Power BI Desktop. Yes, it takes a bit longer but the app is deeper so for the Power User and the BI expert it offers more. Also, the integration speaks for itself. The report appears in the Power BI App, in Teams in a channel, in the personal app sitting alongside all your other reports. My feeling is that this all comes down to where users will expect to see the report, as well as how it integrates into the existing experience across Microsoft 365. Most likely, it’s going to go on a path which looks to bring all of this together. Visible in the Power BI App. Visible in Lists. Visible in Lists in Teams. Visible via the Power BI App in Teams. Consistency across the experience matters. I look forward to seeing how this one develops

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.