Tag Archives: SharePoint

Using Power BI to Report on Person Fields in SharePoint

This post is the second in a series exploring Power BI and complex data types in SharePoint. The first post explores working with multi-value columns. In this one, we’ll explore some of the nuances of working with person fields

Person fields in SharePoint are just a special case of the lookup field, and the Power BI SharePoint list connector is aware of them. As such, it provides helpers to make it relatively easy to get the person’s name. However, more information is also available. We’ll examine three approaches to extracting this information. It is worth noting that all SharePoint lists contain person fields for “Created By” and “Modified By”, and they are always available.

The List

Consider the following list that contains a multi-value choice field named Amenities:

The view displays the person’s name, although the column is a complex data type. There is more information than just the person’s name available behind it but this is unavailable to the SharePoint view. Power BI can however access this information in reports. Report requirements will ultimately dictate the best approach to extracting this information, but the good news is that there are several to choose from. In all cases the data first needs to be brought into Power BI Desktop.

Loading the Data

We first launch Power BI Desktop, select “Get Data” and then choose SharePoint Online list (if connecting to SharePoint Online) or SharePoint List (if using SharePoint Server). We are then prompted for the URL of the SharePoint Site. The dialog is titled SharePoint lists, but the value is actually the URL of the site, NOT the list itself. Once this is entered we are prompted for credentials if we haven’t connected to this site before. After entering credentials, we can select the list that we want to report on. In our case, it’s named “Listings”. We select it, and then click on the Edit button.

Once the data loads in, one of the first things that you’ll notice is that there are a lot of columns to choose from, and it’s a good idea to remove the data that you don’t need. In this case, we can remove the ContentTypeId column and everything to the right of it, with two important exceptions We want to keep the “FieldValuesAsText” and “Agent” columns (we’ll come back to that shortly). Remember, for our purposes here, we want to report on the person column, “Agent”. The simplest way to represent this data is with the person’s full name, as it is displayed in the SharePoint view. As noted above, it is also possible to use this data in a more sliceable, or structured way. Let’s start with the simplest.

Extracting the Full Name

One thing that you will notice right away is that he more simple column types like “Title” show their value directly in the Query editor. In our case, there are two fields related to Agent, the “AgentId” and “Agent” columns. The Agent ID column displays a number, and the “Agent” column displays a record data type. We will explore these columns, but if all we need is the user’s full name, we can use the highly useful “FieldValuesAsText” Column.

We scroll right and select the expander icon for the “FieldValuesAsText” column, then deselect all available fields except the Agent column.

We then select OK, and rename the column to “Agent Name”. The Full name of the Person is retrieved and used for the column. At this point, it’s ready to use in a report.

Linking to the User Information List

In many cases, the user name of the person may not be enough. As mentioned above, the Person Field is really just a lookup column that is automatically looking up data from a specific list. That list is the User Information list which is a hidden list that exists in the root site of every SharePoint site collection. This list gets populated automatically when the site collection is accessed. When Power BI loads a person column, it automatically creates a ColumnNameId column as well containing the ID value of the person field from this list. In our example, this is the “AgentId” column.

To leverage the data in this list it must first be loaded into the model. Following the same steps taken for loading the Listing data above, we select the “User Information List” which does get exposed to the Power BI Query editor. Once loaded, we remove all of the unnecessary columns from the query, being sure that we leave the ID column.

When ready, we select the “Close and Apply” button from the Query Editor Ribbon. At this point, we have two tables in the model, Listings and User Information List. We then select the relationship editor tab. The “AgentId” column in the Listings table is related to the “id” column un the User Information list table, and we establish this relationship by dragging one onto the other. Once established, we double click on the relationship line to set the value of “Cross filter direction” to “Both”.

We can now return to the design pane, add a table visual, and add columns from both tables. In such a way, we can show the agent’s name, email, phone, etc.

Expanding the Person Column

Although linking to the User Information List is powerful, and easier, and arguably better way to do the same thing is to use the automatically generated person column. This column is named the same as the original person column and contains a series of “Record” type values. The records in question are the corresponding records from the User Information List.

To access the data in this column, we click on the column expander and then select all of the columns that we will work with. Values from the related User Information List will be added to the table automatically.

This approach is clearly simpler than manually loading the entire User Information List, and only loads the records that are related. It will however likely result in a large amount of repeated data that the two table approach avoids. It is possible to achieve a two table solution with the person field using the technique outlined in my earlier article on working with multi-value fields, but the resultant table will still only contain related records. If it is necessary to show people regardless of whether or not there is a related record, then the manual approach is the only way.

Which approach is ultimately used will depend on the requirements of the report, but it is possible to reach deep into the person object in a SharePoint person field.

Using Power BI to Report on Multi-Value SharePoint Fields

Power BI has direct support for reporting on SharePoint lists and documents whether SharePoint is on-premises or in the cloud. Getting at your data is relatively straightforward if you know the URL, but as I’ve written about before, SharePoint data can be idiosyncratic. Text and number fields work right away, but more complex data types require a little more thought, and this is certainly true of multi value fields.

Multi value fields are really SharePoint’s way of approximating the behavior of a one-to-many relationship of a relational database. SharePoint can store list-based data, and even though it has the concept of a lookup field, it is not a relational database. Chances are however that if we’re reporting on this type of field, we want it to behave as though it were. Fortunately, Power BI gives us several options for doing this.

The List

Consider the following list that contains a multi-value choice field named Amenities:

In the view, the different values are flattened, and displayed as a single value using a comma to separate them. How do we go about reporting on this data in Power BI? There’s no right answer, as it depends on the report requirements, but there are several options. In all cases the data first needs to be brought into Power BI Desktop.

Loading the Data

We first launch Power BI Desktop, select “Get Data” and then choose SharePoint Online list (if connecting to SharePoint Online) or SharePoint List (if using SharePoint Server). We are then prompted for the URL of the SharePoint Site. The dialog is titled SharePoint lists, but the value is actually the URL of the site, NOT the list itself. Once this is entered we are prompted for credentials if we haven’t connected to this site before. After entering credentials, we can select the list that we want to report on. In our case, it’s named “Listings”. We select it, and then click on the Edit button.

Once the data loads in, one of the first things that you’ll notice is that there are a lot of columns to choose from, and it’s a good idea to remove the data that you don’t need. In this case, we can remove the ContentTypeId column and everything to the right of it, with one important exception We want to keep the FieldValuesAsText column (we’ll come back to that shortly). Remember, for our purposes here, we want to report on the multi-value column, Amenities. The simplest way to represent this data is as a string of comma separated values, which is the same way it is shown in a SharePoint view. It is also possible to use this data in a more sliceable, or structured way. Let’s start with the simplest.

Extracting a Simple Text Field

If our report is to show all the amenities associated with a listing, then a simple text string will do the job. Examining the data in the Query Editor, you will notice that there are no values displayed for Amenity, the way that there is for simpler field types like “Sold” or “Asking Price”. Instead, each cell contains a “List Value”. Each of these Lists contains the Amenities values for each record and clicking on one will extract those values for that one item. However, of we want to extract the values for all items, so we need to expand the column by clicking on the expand icon in the column header.

We are then given two options, “Expand to New Rows” or “Extract Value”. To extract the values into a single value, we select “Extract Values”

We are then given the opportunity to choose a delimiter, and after that the values in the column are replace with simple text values.

Another way to accomplish this same thing is to use the FieldValuesAsText column (referenced above). The fields in this column contain a single record containing the textual values for all the complex data types in a SharePoint list. If you are doing reporting with SharePoint data in Power BI, FieldValuesAsText is your friend. To use it, we just click the expand icon in its column header, deselect all of the columns that we don’t need (which in our case is everything except Amenities) and click OK. We are returned the contents of the list in a comma separated string for each item.

At this point, we can report on the data.

We click on Close and Apply in the ribbon to return to the report canvas. We add a table visual, and add some of the fields like address, street number, and Amenities to it. You’ll see that the table looks much like it would in a SharePoint list. Next, we add a slicer to the report page, and use one of the other columns, like City. Clicking on a city will filter the table by that city as expected. Now, if we want to filter by Amenity, we can add the Amenity column to the slicer, but it won’t behave the way we want it to. Each combination of amenity values will be represented as a single value. We are unable to slice on a single value like “Garage”.

Flattened multi value field as slicer

To use discrete values for our multi-value field, we need to do a little more work in the Get Data (Power Query) interface.

Extracting Discrete Values from a Multi-Value Field

We follow the initial steps from the “Simple Text Value” section above, but when the expand icon is selected, we select “Expand to new rows” and NOT “Extract values”. When this is done, each row will be copied to accommodate all of the individual Amenity values. If there were 3 Amenity values, we wind up with three rows of identical values, but with each of the values for Amenity.

Expanding to new rows

We can now go to the design surface and add a table, and a slicer that uses the multi value column, and now we can filter on discrete values. This may be sufficient in many cases. However, if we create a measure that based on an aggregate value for the table (like COUNTROWS or SUM), the value will not be correct if there are no slicer values. It works with all of the duplicated rows.

Incorrect number of listings

Again, this approach may work in many situations, but for ultimate flexibility, we need a true one-to-many relationship. Luckily, Power Query gives us a few simple tools to do this.

Creating a One-to-Many Relationship

Again, we follow the initial steps from the “Simple Text Value” section above, but do not click on the expand icon in the Amenities column. A One-to-many relationship requires at least two tables, so now is the point where we need to add a second one. We do so by copying the first. We right click on the query in the Queries pane and select “Duplicate”.

This creates a second query identical to our first, which will load into another table. We can then give the new query a better name, like “Amenities” in our case. Next, we select the Id, and the Amenities columns, right click and select “Remove Other Columns”, and we are left with only the ID and Amenities columns. Now we select the expand icon and select “Expand to New Rows”.

We now go back to the original query and remove the Amenities column. The result is two tables, Listings and Amenities, with Listings containing the bulk of the information. We then click on Close and Apply in the ribbon to load the data into the model, and the relationships icon to take is to the relationship builder. We need to establish a relationship between the two tables, and we do so by clicking on the Id field in one table and dragging it to the Id field of the other. Finally, double click on the relationship line between the tables, and be sure to select “Both” for Cross Filter Direction – that way Amenity values can filter List values. If “Both” is selected, the directional arrow will point two ways.

Now, the same visuals from above can be added using the Amenity column from the second table as a slicer, and everything should work as expected. Listings will be filtered by the value, if no amenities are selected, the correct number of listings will be shown In the data card, and no duplication will appear in the table.

SharePoint multi-value columns are an attempt to replicate the capability of a relational database. With a few simple steps, Power BI can take SharePoint data, normalize it, and analyze it as if it were a true relational database.

Understanding the Power BI Capacity Based SKUs

Power BI licensing has changed again. This week at Microsoft Ignite, Microsoft introduced a new capacity based SKU for Power BI Embedded, intended for ISVs and developers: The A SKU. This brings the number of capacity based SKUs to 3, with each category having numerous sub categories. This means that there are a number of ways to embed content by using Power BI Pro, Power BI Embedded, or Power BI Premium. The trick is to know what will be needed for what circumstances. This post will attempt to help with the distinctions.

The SKUs are additive in nature, with A (Power BI Embedded) providing a set of APIs for developers, EM (Power BI Premium) additional ad-hoc embedding features for organizations, and P (Power BI Premium)providing a SaaS application that contains everything that the Power BI service offers. For some background, the EM SKU was initially introduced to serve the needs of both Independent Software Vendors (ISVs) and of organizations that needed to do some simple sharing within the organization, and give them access to the latest Power BI features. However, ISVs have a different business model than enterprises, which is why the A series was introduced.

Power BI Embedded A SKUs

The A SKU (A is for Azure) is a Platform-as-a-Service and set of APIs for those ISVs who are developing an application to take to market. These ISVs choose to use Power BI as the data visualization layer of that application to add value to their own application. As such, Power BI assets contained in Power BI Embedded capacities cannot be accessed by a licensed Power BI user, but are only accessed by customers of the ISV’s application.

Power BI Embedded capacity is billed hourly, can be purchased hourly, and can be paused – meaning no long-term commitments to a specific capacity. This pausing capability is critical for small ISVs that don’t yet have the revenue stream to support monthly commitments, and it addressed one of my largest concerns over moving Power BI Embedded over to the Premium model. Power BI Embedded is purchased through Microsoft Azure. Additionally, Power BI Embedded can scale up and down as needed to accommodate the requirements of the ISV business model as the vendor’s application grows.

Running the entry level A1 capacity for a full month equates to approximately $750/month, so while the capacity of the Power BI Embedded A1 SKU is equivalent to the Power BI Premium EM SKU, ISVs pay a slightly higher effective monthly price for the flexibility mentioned above.

There are 6 sizes of Power BI Embedded available, each capacity mapping to an existing Power BI Premium capacity so ISVs can grow their business as needed. Pricing starts at about $1/hour:

Name Virtual cores Memory (GB) Peak renders/hour Cost/hour
A1 1 3 300 ~$1
A2 2 5 600 ~$2
A3 4 10 1,200 ~$4
A4 8 25 2,400 ~$8
A5 16 50 4,800 ~$16
A6 32 100 9,600 ~$32

Power BI Premium EM SKUs

The EM SKU (EM is for embedding – NOT Embedded) covers off everything contained in the Power BI Embedded A SKU, but also offers the ability to share Power BI reports within an organization through content embedding. Currently, this can be accomplished through the use of the SharePoint Power BI web part for modern pages, or the through tabs using Microsoft Teams.

There are three EM SKUs, and while the largest, EM3, can be purchased through Office 365 monthly, the smaller 2 (EM1 and EM2) must be purchased through Volume Licensing. Volume licensing represents an annual commitment, and may be an incentive for ISVs to remain on the A SKU even if they are not pausing their service. EM SKUs cannot be paused – a month is the smallest available billing unit. Additionally, scaling on EM SKUs requires that you retain your monthly or annual commitment to the initial SKU purchased until the end of the contract term.

Details on the EM SKUs are below:

Name Virtual cores Memory (GB) Peak renders/hr Cost
EM1 1 3 1-300 $625/mo
EM2 2 5 301-600 $1,245/mo
EM3 4 10 601-1,200 $2,495/mo

Power BI Premium P SKUs

The P SKU (P is for Premium, but it helps to think of it as “Power BI Service”) is the “all in” version of Power BI licensed through capacity. It offers everything that is available with Power BI, which includes everything available in the A and EM SKUs. It also offers the ability to share Power BI assets in the Power BI service through apps, or if personal workspaces are in a Premium capacity, through dashboard sharing.

The entry point of the P SKU is significantly higher than EM as well, but you’re getting a business application vs a set of APIs. It also comes with significantly more resources attached to it. For example, P1 comes with 8 virtual cores and 25 GB of RAM, whereas the largest EM offering is EM3, with 4 cores and 10 GB RAM.

All the P SKUs can be purchased through the Office 365 administration center, and can be billed monthly. Details are below:

Name Virtual cores Memory (GB) Peak renders/hr Cost
P1 8 25 1-300 $4,995/mo
P2 16 50 301-600 $9,995/mo
P3 32 100 601-1,200 $19,995/mo

What to use when

Sharing capabilities:

PBI Embedded A PBI Premium EM PBI Premium P
Embed PBI Reports in your own application Embed PBI Reports in your own application Embed PBI Reports in your own application
  Embed PBI Reports in SaaS applications (SharePoint, Teams) Embed PBI Reports in SaaS applications (SharePoint, Teams)
  Share Power Reports, dashboards and datasets through Power BI Apps (workspaces)
 Ad hoc dashboard sharing from personal workspaces

With the addition of the Power BI Embedded capacity based SKUs, many of the concerns around Premium pricing have been addressed. I would still like to see all EM SKUs available monthly, and to see a “P0” premium SKU, but it’s fairly clear as to which scenarios call out for which licenses.

An ISV that is embarking on the use of Power BI embedded will at the very least need a Power BI Pro license. When development gets to the point where sharing with a team is necessary, a Power BI Embedded A SKU can be purchased from Azure. Once 24/7 availability is required, the ISV may wish to switch to an Premium EM capacity. An ISV should never require a P SKU unless capacity demands it, or they have additional requirements.

An organization that has a few data analysts or Power Users that need to share reports with a broader audience would likely be well served with one of the EM SKUs. This scenario assumes that the organization is also using SharePoint Online, Microsoft Teams, or both. This approach will allow the power users (who will require a Pro license as well) to embed Power BI content within a SharePoint page or a Microsoft Teams tab where it can be accessed by users without a Pro license. This organization would need to include more than 63 users accessing the reports to be financially viable.

Finally, larger organizations with a significant investment in Power BI, or organizations that don’t currently utilize SharePoint Online or Microsoft Teams would benefit from a Premium P capacity. With this, the Power BI interface could be utilized by end users to access shared content without a Pro license. Given it’s monthly cost, compared to the monthly cost of Pro, the organization would need to have at least 500 active report consumers for this approach to practically considered.

SharePoint and the New SSRS/PBIRS Native Mode Report Viewer Web Part

When SQL Server 2016 was released, I wrote a post that covered how to integrate SharePoint 2016 with SQL Server Reporting Services (SSRS) in native mode. I wrote that post because although SSRS was still available in SharePoint Integrated Mode, many of the new features were only in Native mode. It also wasn’t difficult to surmise that Integrated mode had a limited lifespan at that point, a fact that was confirmed in November 2016.

As my integration article pointed out, one of the glaring problems integrating Native mode with SharePoint was that the Native Mode web part had significantly less capability than the one that was available with Integrated mode, not to mention the fact that it was deprecated. Parameter support was first and foremost among those missing features. Organizations that had incorporated SSRS reports into their SharePoint based dashboards couldn’t just move to Native Mode, and those dashboards relied heavily on the ability to pass parameter values to the SSRS reports through the web part. SSRS did make it easier to embed reports into pages without a web part through the rs:Embed URL directive, but there was no real way to make that dynamic.

The introduction of Power BI Report Server (PBIRS), and its ability to render Power BI reports also left the SharePoint dashboard folks without a solution. Although PBIRS is a superset of SSRS, it is only available in Native Mode, in fact, there’s no longer any need to mention modes. SharePoint dashboards that used SSRS report components were stuck at the 2016 version.

Until now.

Today, Microsoft unveiled the new, fully capable SSRS/PBIRS Report Viewer web part for SharePoint.

Integrated Mode Native Mode

“Meet the new boss, same as the old boss” – Pete Townshend

The web part allows paginated reports from either SSRS in Native mode, or Power BI Report Server to be embedded into a SharePoint page. Essentially, it brings all the capabilities of the integrated mode web part to the native mode web part. Organizations that have bet on SSRS running in Integrated mode can now seamlessly move their reports to SSRS Native mode, or to Power BI Report Server. Let’s have a quick look at some of the details.

Parameter and view support

All the options that we’ve come to expect from the integrated mode web part are here in the new web part. Toolbar options can be controlled at a fine level and web parts can be connected to pass in parameter values. In fact, as seen above, the only difference between the two modes now is the report location option.

C2WTS and Kerberos required… unless

With Integrated mode, Kerberos constrained delegation (KCD) was only required if you needed the user’s identity passed back to the data source. This was because Integrated mode and SharePoint itself shared the same access mechanism. Native mode and PBIRS have their own membership providers, and this new (server side) web part needs to connect to the report server itself to authenticate the user. This is an additional “hop” and therefore requires Kerberos constrained delegation. This only gets the user as far as the report server itself. To get the user’s identity further downstream, KCD will also need to be set up between the report server and data source(s).

SharePoint 2016 in most cases uses claims authentication, and SSRS/PBIRS does not. This also means that the Claims to Windows Token Service (C2WTS) must also be running in your SharePoint environment. This allows the user’s NTLM identity to be extracted and sent via KCD to the SSRS/PBIRS server.

There is one case where KCD is not required, and that is when SSRS is running on a machine in the SharePoint farm along with C2WTS. In a small environment, this may be suitable, but if scaling is a factor, you may need to crack open your Kerberos books.

Paginated reports only

Even though SSRS supports the new RSMobile report format, and PBIRS also now supports both Interactive (Power BI) and analytical (Excel) reports, this web part is only built to render paginated reports. Up until 2016, paginated was the only report type supported by SSRS, so this will not be a problem for those looking to move forward. However, for those people that are looking to embed these other formats in SharePoint on premises, there are other options.

Both Power BI and mobile reports can be embedded into a SharePoint page using the script editor web part alongside the rs:Embed=true URL directive. Parameters can be passed to mobile reports using this technique, and text filters can be passed to interactive reports as well. Excel reports can be embedded with the Excel Services web part, the way that they always have which itself allows for passing slicer values. However, for this to work, the Excel files need to be stored in SharePoint, not PBIRS.

On-premises only

The new web part deploys to the SharePoint farm as a WSP solution file, which means that this is an on-premises solution only. Given that SSRS/PBIRS are also on-premises products, this shouldn’t pose much of a problem. It IS possible to ebbed on prem reports into SharePoint Online pages with the script editor web part for classic pages, and the embed web part for modern pages. A complete summary of report/SharePoint edition embed options can be seen below.

Report type

SharePoint Online modern page

SharePoint Online classic page

SharePoint on-premises

PBIX on Power BI Service

Power BI WP




Modern embed content WP

Script editor WP

Script editor WP

RDL on PBIRS or SSRS Native

Modern embed content WP

Script editor WP

Report Viewer WP NEW!

RDL on SSRS Integrated mode

Modern embed content WP

Script editor WP**


XLSX (stored in SharePoint)


Excel Services WP

Excel Services WP

RSMobile on SSRS/PBIRS (Native)

Modern embed content WP

Script editor WP**

Script editor WP

*    NOT recommended

**     Requires adding sever to HTML Field Security options in site collection settings

In summary, the new native mode web part will be very welcome for those organizations that have been feeling stranded by the deprecation of SharePoint Integrated mode. The very fact that it is now available also underscores that SharePoint still plays an important role in the Microsoft Business Intelligence ecosystem. It may no longer be a prerequisite, but it still adds value, and those are both very good things.

Which Premium SKU is Needed to embed Power BI Reports in SharePoint and Microsoft Teams

A short time ago, I posted an article explaining the difference between a Power BI Pro license, and Power BI Premium capacity, and the fact that you’ll at least need one or the other in order to share a Power BI report on a SharePoint page via the Power BI web part. Although that article didn’t mention it, the same requirement is also true for embedding a report in a Microsoft Teams tab.

Power BI Report embedded in a Microsoft Teams tab

Power BI Report embedded in a SharePoint Page

Since there are two major SKU types for Power BI premium, and that there was (and is) a fair amount of confusion around this area, I also published another article attempting to clear up the confusion. While this article was based on all the information publicly available at the time, new information has pointed out that it is incorrect.

The two major SKU types are P and EM, with P standing for Premium and EM for Embedded. This matters significantly because the two SKU types have significantly different entry points and therefore costs.

The P SKU was the only one introduced when Premium was originally announced. It gives organizations the ability to place Power BI assets in a premium capacity container (a Power BI “app”), and once this is done, anyone can consume these assets whether or not they have a license.

Subsequent to this, an additional SK (EM) was introduced to address Power BI Embedded. Power BI embedded allows an ISV to use Power BI to add reports to their own applications. In this scenario, the reports run from the ISV’s tenant. Originally the assets were housed in Azure, but with the availability of Premium capacity, the decision was made to shift Power BI Embedded to use this new model. Given that the requirements of an ISV are not the same as a general organization, this new  SKU was introduced. The EM SKU comes with a significantly lower entry point and cost, but also with significant restrictions. This is where the confusion sets in.

The wording around the restrictions on the EM SKU indicated that it could not be used for the SharePoint web part, and that a P SKU, or a Pro license would be required for that use case. This is where my earlier article is incorrect. I have since had conversations with the product team, and have been informed in no uncertain terms that the EM SKU CAN be used for both SharePoint web part, and Teams tab embedding of Power BI reports.

This is a very significant difference. An organization that is using Power BI casually, but has a few reports that they want to share with a broad audience was looking at a cost of almost $5,000 per month to do this. Given that the cost of a Pro license is $10/user/month, this meant that the organization needed to have at least 500 frequent report consumers before Premium was even worth considering. Also given the fact that the embedding features available in both SharePoint and Teams require that Pro or Premium SKU, this could be a real disincentive to its use. However, given that the EM SKU start at approximately $650/month for the entire organization, this becomes much more approachable, and it lowers the bar to entry significantly. This should result in significantly greater adoption of these Power BI embedding features, and consequently, of Power BI as a whole.

To be clear, there are still restrictions around the EM SKU. You cannot share Power BI apps with this SKU, but you CAN use it to embed reports in both SharePoint and Microsoft Teams.