Skip to content

The White Pages Posts

First impressions of the new “Visualize in Power BI” feature for SharePoint Lists

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:

TypeResult
Person[List]
Multi-Value Choice[List]
ChoiceChoice fields work – the value is shown
Managed Metadata[Record]
Rich TextThe raw HTML value is shown with all markup
Location[Record]
Picture or HyperlinkThe destination URL is show, not linkable
Lookup[List]
BooleanBoolean fields show True or False
CurrencyCurrency 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

Using Power BI with Microsoft Lists

Microsoft Lists debuted in 2020 and they are a (yet another) great way to organize list-based data. Now, when someone says data, I think Power BI. Obviously, we’ll want to report on this data, but how do we do that? There is no Power BI connector for Microsoft Lists. The answer is quite simple, if not completely obvious. You need to use the SharePoint Online List connector in Power BI.

Microsoft Lists are the same thing as SharePoint lists. In fact, they ARE SharePoint lists. The Microsoft Lists service is just a new user interface for interacting with them. You can use Lists all you want and never see SharePoint at all, unless you need to change list settings, but I digress. Given this fact, as far as Power BI is concerned, everything that applies to SharePoint lists applies to Microsoft Lists lists (the grammar here gets awfully awkward).

For reference, I wrote a series of articles some time ago about the idiosyncrasies of working with SharePoint data in Power BI, and these articles are still valid today (2021). The most recent of these articles can be found here and includes links to the others.

There is one thing that is worth mentioning about Microsoft Lists. When a new list is created using the Lists interface, the user can save it to any of their SharePoint sites, but another option is to same it to “My lists”.

When you use the SharePoint Online list connector in SharePoint, it prompts you to enter the URL for the SharePoint site that contains the list that you want to report on. That is straightforward when your list is stored in a SharePoint site, but what if your list is stored in “My lists”? Where are “My lists” stored?

They are stored in a “personal” SharePoint site. We SharePoint old timers would know it as the MySite, and while usage of MySite has been de-emphasized in Microsoft 365, it is very much still there. Each user has one. In fact, this is where the “personal” OneDrive for Business content is stored – in the Documents library of the very same MySite. By storing personal lists in the MySite, Microsoft Lists is just following the same pattern used by OneDrive for Business, which makes perfect sense.

Given this, what URL should you use in Power BI to connect to your lists stored in “My Lists”? You’ll find it in the Microsoft Lists web interface in the URL bar. It’s that portion of the URL up to “/Lists/.

In most cases it will take the following form:

https://TenantName-my.sharepoint.com/personal/LoginID/Lists/ListName

Where:

  • TenantName = the name of your Microsoft 365 tenant, i.e. Contoso
  • LoginID = the email address used to login to Microsoft 365 with the “@” and the “.” replaced with underscores i.e. jpw_contoso_com
  • ListName – the name of you list

Once you enter in this URL, you’ll have access to any of the lists stored in “My lists”. At this point, your personal lists will behave like any list in any other SharePoint site.

Leave a Comment

It’s time to stop using Power Pivot

Excel is an excellent tool for analyzing data. An analyst can easily connect to and import data, perform analyses, and achieve results quickly. Export to Excel is still one of the most used features of any Business Intelligence tool on the market. The demand for “self-service BI” resulted in a lot of imported data being stored in overly large Excel files. This posed several problems. IT administrators had to deal with storage requirements. Analysts were restricted by the amount of data they could work with, and the proliferation of these “spreadmarts” storing potentially sensitive data created a governance nightmare.

A little history

Power Pivot was created to provide a self-service BI tool that solved these problems. Initially released as an add-in for Excel 2010, it contained a new analytical engine that would soon be introduced to SQL Server Analysis Services as well. Its columnar compression meant that millions of rows of data could be analyzed in Excel and would not require massive amounts of space to store. Data in Power Pivot is read-only and refreshable – ensuring integrity. It allowed analysts to set up their own analytical data sets and analyze them using a familiar looking language (DAX), and visual reporting canvas (PowerView) all from within Excel.

The original version of Power BI brought PowerPivot to Office 365 through Excel before Power BI’s relaunch gave it its own consumption interface (the service) and design client (Power BI Desktop). Both the PowerPivot engine, and Power Query were incorporated into the service and Power BI Desktop, while the Silverlight based Power View was replaced with a more web friendly reporting canvas.

Excel support

Throughout all these changes, Excel has continued to be well supported in the Power BI service. Analyze in Excel allows an analyst to connect to a deployed Power BI dataset (built with Power BI Desktop) and analyze it using pivot tables, charts, etc. Recent “connect to dataset” features have made this even simpler. Organizational Data Types allow Excel data to be decorated with related data in Power BI.

Excel workbooks containing Power Pivot models have always been supported by the service. These models can even be refreshed on a regular basis. If the source data resides on premises, it can even be refreshed through the on-premises data gateway. This all because the data engine in Power BI is essentially Power Pivot.

It’s that word “essentially” that causes a problem.

Datasets that are created and stored within Excel workbooks are functional but can only be accessed by that workbook. Contrast this with a dataset created by Power BI Desktop, which can be accessed by other interactive (pbix) reports, paginated reports, and as mentioned above, by Excel itself. The XMLA endpoint also allows these reports to be accessed by a myriad of third part products. None of this is true for datasets created and stored in Excel.

So why would anyone continue to create models in Excel. The reason has been until now that although Excel can connect to Power BI datasets to perform analysis, those connected workbooks would not be updated when the source dataset changes. This meant that those analysts that really care about Excel needed to work with the Excel created models. This changed recently with an announcement at Microsoft Ignite Spring 2021. In the session Drive a data Culture with Power BI: Vision, Strategy and Roadmap it was announced that very soon, Excel files connected to Power BI datasets will be automatically updated. This removes the last technical reason to continue to use Power Pivot in Excel.

Tooling

Building a dataset with Power BI Desktop is fundamentally the same as building one with Excel. The two core languages and engines (M with Power Query, and DAX with Power Pivot) are equivalent between the two products. The only difference is that the engine versions found in Excel tend to lag those found in Power BI Desktop and the Power BI service itself. I’d argue that the interfaces for performing these transforms, and building the models are far superior in Power BI Desktop. not to mention the third-party add-in capability.

In this “new world” of Excel data analysis, Datasets will be created by using Power BI Desktop, deployed to the service, and then Excel will connect to them to provide deep analysis. These workbooks can then be published to the Power BI service alongside and other interactive or paginated reports for use by analysts. With this new capability, Excel truly resumes its place as a full-fledged first-class citizen in the Power BI space.

What to use when

With this change, the decision of what tool to use can be based completely on its suitability to task, and not on technical limitations. There are distinct types of reports, and different sorts of users. The choice of what to use when can now be based completely on these factors. The common element among them all is the dataset.

With respect to report usage, typical usage can be seen below.

ToolUsed byPurpose
Power BI ServiceReport consumersConsuming all types of reports: interactive, paginated and Excel
Excel OnlineReport consumersConsuming Excel reports from SharePoint, Teams, or the Power BI service
Power BI DesktopModel builders
Interactive report designers
Building Power BI dataset
Building interactive reports
Power BI Report BuilderPaginated report designersBuilding paginated reports
ExcelAnalystsBuilding Excel reports
Analyzing Power BI datasets

Making the move

Moving away from Power Pivot won’t require any new services or infrastructure, and existing reports and models don’t need to be converted. They will continue to work and be supported for the foreseeable future. Microsoft has neither said not indicated that Power Pivot in Excel is going anywhere. However, by building your new datasets in Power BI Desktop, you will be better positioned moving forward.

If you do want to migrate some or all your existing Excel based Power Pivot datasets, it’s a simple matter of importing the Excel file into Power BI Desktop. This is completely different than connecting to an Excel file as a data source. From the File menu in Power BI Desktop, select Import, then select Power Query, Power Pivot, Power View. You will then select the Excel file that contains your dataset.

Power BI will then import all your Power Query queries, your Power Pivot dataset, and if you have any it will convert PowerView reports to the Power BI report types. The new report can then replace your existing Excel file. Once deployed to the Power BI service, other Excel files can connect to it if so desired.

Building your datasets with Power BI Desktop allows you to take advantage of a rich set of services, across a broad range of products, including Excel. Building them in Excel locks you into an Excel only scenario. If you already use Power BI, then there’s really no reason to continue to build Power Pivot datasets in Excel.

3 Comments

Using Power BI Incremental Refresh with Azure Data Explorer

Azure Data Explorer (ADX) is a great platform for storing large amounts of transactional data. The Incremental Refresh feature (now available for Pro users!) in Power BI makes it much faster to keep data models based on that data current. Unfortunately, if you follow the standard guidance from Microsoft for configuring Incremental Refresh, you’ll quickly bump into a roadblock. Luckily, it’s not that difficult to get around.

Incremental Refresh works by setting up data partitions in the dataset in the service. These partitions are based on time slices. Once data has been loaded into the dataset, only the data in the most recent partition is refreshed.

To set this up in Power BI Desktop, you need to configure two parameters, RangeStart, and RangeEnd. These two parameters must be set as Date/Time parameters. Once set, the parameters are used to filter the Date/Time columns in your tables accordingly, and once published to the service, to define the partitions to load the data into.

When Power Query connects to ADX, all Date/Time fields come in as the Date/Time/Timezone type. This is a bit of a problem. When you use the column filters to filter your dates, the two range parameters won’t show up because they are of a different type (Date/Time). Well, that’s not a big problem, right? Power Query lets us change the data column type simply by selecting the type picker on the column header.

Indeed, doing this does in fact allow you to use your range parameters in the column filters. Unfortunately, data type conversions don’t get folded back to the source ADX query. You can see this by right-clicking on a subsequent step in the Power Query editor. The “View Native Query” option is greyed out, which indicates that the query cannot be folded.

Query folding is critical to incremental refresh. Without it, the entirety of the data is brought locally so that it can be filtered vs having the filter occur at the data source. This would completely defeat the purpose of implementing Incremental Refresh in the first place.

The good news is that you can in fact filter a Date/Time/Timezone column with a Date/Time parameter, but the Power Query user interface doesn’t know that. The solution is to simply remove the type conversion Power Query step AFTER performing the filter in the Power Query UI.

Alternatively, if you’re comfortable with the M language, you can simply insert something like the following line using the Advanced Editor in Power Query (where CreatedLocal is the name of the column being filtered).

#"Filtered Rows" = Table.SelectRows(Source, each [CreatedLocal] >= RangeStart and [CreatedLocal] < RangeEnd),

If the filtration step can be folded back into the source, Incremental Refresh should work properly. You can continue setting up Incremental Refresh using the DAX editor. You will likely see some warning messages indicating that folding can’t be detected, but these can safely be ignored.

Leave a Comment