SharePoint Lists have been a data source for Power BI ever since its introduction. Power BI Desktop can connect to those lists to build reports, and once published in the Power BI service, those same reports can be surfaced in SharePoint through the Power BI web part, or in Teams through tabs or the Power BI app. In fact, I wrote a series of articles a few years ago on using Power BI to report on complex field types.
While those articles are still valid, the need for such articles means that report creation has required a high level of sophistication, and the various tools required puts report creation out of reach for a whole class of users. With this in mind, the Power BI team has recently rolled out its first iteration of a report creation feature for Lists. Given my SharePoint background, I had to dig in. What follows are my first impressions.
Using Quick Visualize
Currently this feature is part of the Microsoft 365 Targeted Release experience but will roll out more broadly over the coming months.
At the top of each list is an “Integrate” button. This button is available in both Microsoft Lists, and through the standard SharePoint interface. Selecting it allows you to create a Power App or Power Automate Flow from the list, and once the feature is rolled out, you can create a Power BI report from this button. To begin with, I started with a relatively simple list to collect donation pledges:
A specialized Power BI window then opens with a pre-configured report. The service makes a guess at what you’ll want to visualize. Measures are summarized in cards and sliced by various dimensions horizontally. At the bottom is a table laying out the detail. For my list above, the default report appeared as follows:
In my list, monetary value was the only numeric field, so a count was also added. Along the right of the report is the brand new Summarize pane, which is how you select which measures and dimensions are included in the report. In my case, I’d rather slice by year than quarter and Count of Availability doesn’t mean much so I want to deselect it and add Count of rows. Just by using the Summarize pane, the report will appear as follows:
There is a limit of 4 dimensions that can be selected for slicing the measures.
If you want to customize the report further, you can select Edit, and you will be presented with a more classic report editing experience in Power BI. When you do this, you will lose access to the Summarize pane, but you will be able to make a lot of fine-grained changes to the report.
When complete, you can use the “Publish to the list” button to save it and share it.
What’s Different
Reports created in this manner are stored along with the list that created them in a specialized workspace. They cannot be viewed in the Power BI service at all. In addition, it’s not possible to download the report as a .pbix file to publish it elsewhere – it lives in the list. To access it, simply press the Integrate button again, and the reports that have been published from this list will all be available.
Like most other Power BI reports, these reports are driven from a data set. In this case, the data set is created behind the scenes automatically, but like most datasets, it contains a cache of the list data. This means that the report will not always be up to date with respect to the list data, and therefore must be periodically refreshed.
With these types of reports, the refresh happens every 3 hours automatically. This allows the data to be refreshed 8 times per day, which is the maximum allowed for the Power BI Pro license.
You don’t need a Pro license to use this feature, but you will if you want to publish your reports back to the list. The key here is that any sharing features in Power BI require a Pro license, or a dedicated capacity (Premium) backed workspace. At the moment, there is no way to move these reports to a dedicated capacity.
Complex SharePoint Fields
Lists are infamous for their specialized column types. Lookup columns, rich text fields, managed metadata can be tricky to work with from an integration standpoint. Happily, Power Query makes working with them fairly simple as I describe in the article series that I mentioned above (the latest one on location columns with links to the others is here – Using Power BI to Report on Location Columns in SharePoint – The White Pages (unlimitedviz.com)). However, the Quick Visualize feature doesn’t use Power Query. So how does it handle complex SharePoint fields?
Not that well as it turns out. At least not for now.
I decided to turn this loose on my “Properties” list that I use for demos. It contains one of every SharePoint field type. The initial result can be seen below.
To be fair, there is only one numeric column in this list, and it is composed entirely of complex field types. However, it does mean that for the moment at least, if you’re working with complex field types, this feature will only be of limited use to you. Even in the first example above you can see that the donor value was always “[List]” because donor was a person field.
This is a list of all the complex SharePoint field types, and how they are expressed when using Quick Visualize:
| Type | Result |
|---|---|
| Person | [List] |
| Multi-Value Choice | [List] |
| Choice | Choice fields work – the value is shown |
| Managed Metadata | [Record] |
| Rich Text | The raw HTML value is shown with all markup |
| Location | [Record] |
| Picture or Hyperlink | The destination URL is show, not linkable |
| Lookup | [List] |
| Boolean | Boolean fields show True or False |
| Currency | Currency fields work |
Impressions
It’s important to keep the design of this feature in mind. This is for users that do not have a lot of experience with Power BI that need a way to quickly visualize their list-based data. This is not for report designers that intend to publish reports for a wide audience – Power BI Desktop is there to serve that purpose.
This is a great move in the right direction. The lack of support for complex SharePoint field types mean that its usage will be limited to simple lists, but there are a lot of those out there, and I have every confidence that support for them will be coming. The lack of control of the refresh schedule may also prove limiting in several circumstances.
Overall, “Visualize in Power BI a great step in the right direction, and I suspect that we’ll see a lot more along this line in the not too distant future.
Leave a Comment
