Tag: SharePoint

Working with Excel files in the Power BI Service

Working with Excel files in the Power BI Service

Published July 20, 2018

Power BI has been able to work with Excel files since it was first introduced. Indeed, it was born from the analytic capabilities in Excel. Users can connect directly to Excel files by using the Power BI service and nothing but a browser. However, depending on the content of the Excel file, and the method of connecting, the resulting products can be very different. In this post I will attempt to clarify this behavior. A subsequent post will detail the options available when working with Excel files in Power BI Desktop.

File Structure

Excel is a multi-purpose tool. It contains all the building blocks of Power BI, and as such, it is an excellent Business Intelligence client. Excel files are also often used (much to my chagrin) as a data storage container, or as a data transport medium. Understanding how the file is structured, and what you want to do with it is key to making the right choice when combining it with Power BI.

Originally Excel files (workbooks) were collections of worksheets. Analysts could import data into those worksheets and then analyze them with the tools that Excel provided. Although Excel was never intended to be a database, it’s ease of use and familiarity led many people to begin using it that was, and “spreadmarts” (spreadsheet data marts) quickly became a problem. The problems arose because the instant data was extracted from a source it became stale, and the fact that it was being stored in worksheets meant that it could be edited (changing history) and became subject to the data size limitations of a worksheet.

To take advantage of Excel’s analytic capabilities without being subject to the issues involved in worksheet data storage, the data model was introduced, initially through PowerPivot. The data model is a “miniaturized” version of the SQL Server Analysis Services tabular engine that runs in Excel. This data model is read only, refreshable, and highly compressed which importantly means that its only data limitation is the amount of available memory available on the machine running it. Importantly, this engine is the same engine that is used by Power BI – the advantages of which we’ll explore shortly.

Excel of course still needs to be able to use worksheets and be Excel, so we can’t just remove the worksheet capability (which incidentally is effectively what Power BI Desktop is – Excel without worksheets). Therefore, today from a data perspective, Excel files can have data in the data model, worksheets or both. From the Power BI service perspective, the important thing is whether the file contains a data model, as it treats the two cases differently.

Getting Excel Data

From the Power BI service, you click the Get Data button, and then the Get button in the Files tile. You are then presented with one of two dialogs depending on whether you are using a personal workspace, or an app workspace.

Personal workspace

Importing files into a Power BI Personal Workspace

Connecting file-based data to a personal workspace

When importing into a personal workspace, there are 4 possible data sources.

A local file is one that is stored on a file system local to the machine being used. Selecting this option will allow you to work with the Excel file stored in that location, but if the file is being used as a data source (data is in the worksheets), then a Data Gateway will be required for any data refreshes. Power BI will also connect to a file stored in OneDrive, either Personal or Business (through office 365). Finally, the service can work with files stored in any accessible SharePoint site (not simply Team sites as the name would indicate).

App workspace

Importing files into a Power BI App Workspace

Connecting file-based data to an App workspace

When importing into an App workspace, there are 3 possible data sources. The Local File and SharePoint – Team Sites options are precisely the same as when importing into a personal workspace. The difference is the OneDrive – Workspace name option replaces the two other OneDrive options. Choosing this option allows you to work with files stored in the “Group OneDrive”. Since every App workspace is backed by an Office 365 or “Modern” group, it also has access to the SharePoint site for that group. The “Group OneDrive” is the Documents library within that SharePoint site. Therefore, choosing SharePoint – TeamSites and navigating to the Documents library will render the same results in a few more mouse clicks, but also give access to all other document libraries within that site.

Connect vs Import

Once you navigate to the Excel file that you want to work with, you select it, and click connect. You will then be presented with two options for the file, Import or Connect.

This choice dictates how the file is brought into the Power BI service. The structure of the file determines exactly what is brought in to the service in both cases.

Connect

Clicking the Connect button allows Power BI to connect to and work with the Excel file in place. The workbook is displayed as an Excel workbook in full fidelity in the Power BI interface using Excel Online. The file itself is shown in the Workbooks section in the Power BI interface, and it stands alone from other Power BI elements (except that regions of it can be pinned to a dashboard). Connecting to an Excel report will not create a Power BI Dataset, Report, or Dashboard. All operations, including refresh (see below) are controlled through the workbook.

At no point is the file moved, or “brought in” to the Power BI service. If the file is being stored in SharePoint, or OneDrive, anything done to the file in the Power BI service will be visible to anyone with access to the file itself, whether they are a Power BI user or not. This includes refresh, which will be discussed further below, but the important part to remember here is that if the data in the connected file is refreshed through the Power BI service, and it is being stored in SharePoint (or OneDrive), all users will be able to see updated data the next time that they open the file.

Connecting to an Excel file behaves the same way whether the file contains a data model or not, but the file must contain a data model in order to be refreshed by the Power BI service.

Connected Excel file within Power BI

Connected Excel file within Power BI

Import

Importing an Excel file behaves totally differently from connecting to it. When an Excel file is imported, it is treated as a data source to Power BI, and the assets within that file are brought into the Power BI service. Subsequent changes to the source file are not immediately reflected within the Power BI service, but are retrieved through the refresh process.

The way that the assets are brought into the service depends very much on the structure of the file, specifically whether it contains a data model or not. If the file does not contain a data model, then Power BI will use the data contained in the Excel worksheets to construct a new one. This is similar to what happens when a CSV file is imported into the service. If the file does contain a data model, then the worksheet data is imported, and that data model is brought into the service as-is. One important exception to this is if worksheet data uses the same query as an existing model, the worksheet data is ignored, and the data model is brought in as-is. This is important because Excel’s Power Pivot editor can be used to edit the model, creating calculated columns, calculated measures and relationships prior to import. The model that is automatically created when the file does not contain a model has no editing capabilities.

When an Excel file with a data model is imported, the data model (imported or created) is added to datasets, and a link to the dataset is added to the default dashboard for the workspace. If no default dashboard exists, one will be created. A report can then be authored in the service. If the workbook contains any PowerView reports, these will be converted to native Power BI reports and added to the service as well. Any embedded 3D maps are not brought in.

Imported Excel File showing calculated measures

Imported Excel File showing calculated measures

Refresh

Data refresh options, and behavior depend on both the Get Data choice (connect or import) and the structure of the Excel file.

Connected Workbooks

If the workbook is connected to the service, and it does not contain a data model, it cannot be refreshed. This is true even if the worksheets in the workbook contain data from Power Query queries. This is the only scenario that does not support refresh in any way.

If the workbook contains a data model refresh is supported. The interesting part is that refresh will be triggered not only for the data model itself, but for any worksheets that have Power Queries as a data source. Therefore, a workaround to the lack of refresh support for a worksheet with no data model is to add a blank data model.

For refresh to work, the data source must be available to the Power BI service. This means that the source must be available in the cloud or registered on an available On-Premises Data Gateway.

The important thing to note about connected workbooks is that the refresh options that are performed on them are permanent – refreshed data is stored with the workbook. This means that if the connected workbook is stored in SharePoint, or shared through OneDrive, updated data is available to all users with access regardless of whether they are Power BI users.

Imported Workbooks

Refresh options for imported workbooks are slightly more complicated. As mentioned above data is either imported from the worksheets, a data model imported into the service or both.

If data was imported from worksheets, then the Excel file is the data source from the standpoint of Power BI. If the file is stored in SharePoint or OneDrive, it will automatically be refreshed every hour by default. This means that changes to the underlying Excel file will be reflected back in the Power BI service within an hour. This feature can be disabled, but it is not possible to change to hourly schedule, nor precisely when it will occur.

Refresh options for workbooks in OneDrive/SharePoint

Refresh options for workbooks in OneDrive/SharePoint

If the file is stored on a file system, it can be scheduled more granularly, but you will need to connect to it through an On-Premises Data Gateway.

If the file contained a data model that was imported into the service, then the original source of data for that data model (the query) is what the Power BI service will refresh from (NOT the Excel file itself). In this case, the refresh options are the same as with most other Power BI data sources – Excel is taken out of the picture completely, and any changes to the source Excel file will not be reflected into the service. The exception to this is if the file had both a data model, and worksheet data that was imported.

In the case of an Excel with both a data model and worksheet data, both cases above will apply. The workbook is used as a data source for the table that was created by Power BI on import, and the original data model’s source is updated independently. This means that changes to the worksheet data are reflected in the Power BI service when refreshed, but any model changes to the original Excel file are not. Both the OneDrive and regular refresh schedules are used for imported files of this type.

Refresh options for a combined data source

Refresh options for a combined data source

The following table summarizes the refresh options available for file structure and connection type.

File Structure	Get Data option
	Connect	Import
Worksheet data	None	Refresh from worksheet
Data model only	Refresh from model source	Refresh from model source
Data model plus worksheet data	Refresh from model source and worksheet source	Refresh from model source and worksheet

Summary

Both Excel and Power BI are powerful tools in their own rights, and the decision to use one does not preclude using the other and in fact there are many good reasons for doing so. Bringing refreshability to Excel files stored in SharePoint is just one of them. It is however important to understand how it all works in order to get the maximum impact.

5 Comments

Using Power BI to Report on Lookup Fields in SharePoint

Published April 18, 2018

This post is the sixth and final post in a series exploring Power BI and complex data types in SharePoint. This post examines the various options in Power BI for working with lookup fields. The previous posts are:

A lookup field in SharePoint contains values looked up from another list in the same SharePoint site. Strictly speaking, the field contains only the ID from the item in the source list, and the value(s) is/are looked up whenever the field is displayed. The lookup field can also be used to display multiple field values from the target list items.

Defining a lookup field in SharePoint

The List

Consider the following list that contains a lookup field named “Neighbourhood”:

The lookup field neighbourhood in the SharePoint view

We can see from the screenshot above that the text value for neighbourhood is displayed in the view, although only the row identifier is stored in the column. We will be able to get both values and more if desired in a Power BI report, but first we need to build the report using 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 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.

Loading data rom the Listings list

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 columns that you don’t need. We can do this by right clicking on the desired column titles and selecting “Remove”. 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” in addition to the special “Neighbourhood” column at the far right of the columns, as we’ll be needing them for our options below.

Using FieldValuesAsText

Examining our columns, we can see that amongst the simpler text fields, we don’t have a “Neighbourhood” column, but instead, a “NeighbourhoodId” column, with numeric values. We do have a Neighbourhood column further off to the right, but it doesn’t display simple text (we’ll come back to this shortly). If we simply want the text value of our lookup target, we can use the “FieldValuesAsText” column quickly.

Scrolling right in the Query editor view, we find the “FieldValuesAsText” column. The record values represent a one to one relationship with the text values of the list row, so we can click on the column expander at the right of the column title. From there we can extract the text value of any column, including our lookup field, “Neighbourhood”.

Extracting text using FieldValuesAsText

With “Neighbourhood” checked, and nothing else, including the “Use original column name..” option, we can click OK, and the “FieldNameAsText” column is replaced by a new column, “Neighbourhood” that contains the text values for Neighbourhood.

If this value is all that is needed, then this is a totally valid approach, and we can move on to report building. However, this is only one way to achieve this goal. If more information is needed, then other methods may be more suitable.

Retrieving all Lookup Field Values from the Extended Column

Given that the lookup target item is a SharePoint list item, all that item’s properties are available to us. We can access them from the extended column set up for the field. In our case, the original “Neighbourhood” column is the extended column. We can expand this column by selecting its column expander.

Extracting field values using the extended column

We then deselect all of the columns except the ones that we want to use in the report. The fields available are the fields available in the target list. In our case, we select the “Title” field, as it is the one being looked up. We can however retrieve any of the fields that we need from the target list.

Keep in mind that “Title” in our example is a simple text field, so no further action is necessary. The retrieved fields can be complex (person, MMS, etc), but keep in mind that if a complex field type is retrieved, it will need to be transformed just like any from the list in question.

The field name in the target list may not adequately describe its function for the report. In our case, “Title” actually means “Neighbourhood” in this report. It’s a good idea to rename it.

Finally, if multiple field values are to be retrieved, the data model could grow significantly. This is because the values for every field are repeated in every row of data. Given that the original lookup column adds a measure of relational behaviour to SharePoint, using this relationship is the most efficient way to work with this data. Power Query allows us to do just that.

Working with Related Tables

To work with related tables, we need not only the original data table (in our case, “Listings”) but also the table for the lookup list itself. To do this, from the Query Editor, we create a new data source like the one created above for “Listings”, but instead we select the lookup list (“Neighbourhoods”).

Once imported, we can remove any extraneous columns, and then set the data type for the ID field to be “Whole Number”.

Setting the ID column to whole number

We also need to set the data type of the “NeighbourhoodId” column in the Listings table to “Whole Number”. Once these options have been set, we are ready to work with the data model and the report. We select “Close and Apply” from the ribbon to load the data into the model.

Once loaded, we launch the relationship builder in the design pane in order to establish the relationship between the two tables.

Power BI relationship editor with default relationship defined

We can see that Power BI has already detected a relationship. However, it is not correct. The model designer assumes that because both tables contain an “id” column, then they must be related. However, the true relationship is between the “Id” column in our “Neighbourhoods” table, and the “NeighbourhoodId” column in our “Listings” table.

We must first delete the detected relationship by selecting the connector between the two tables and pressing “Delete”. We can then create the proper relationship by dropping one of our related columns onto the other. Once this is created, we also need to ensure that the “Cross filter direction” is set to “Both”. We do this by double clicking on the relationship connector and selecting the appropriate option.

Setting cross filter direction

Power BI relationship editor with correct relationship defined

Once the relationship has been established, we can return to the design pane and construct a rudimentary report. We drag a few fields from Listings into a table, create a calculated measure for the number of listings, and we add the “Title” field (renamed to “Neighbourhood”) to the canvas separately. Once we set the visualization for “Neighbourhood” to a slicer, we can easily slice our listings data by neighbourhood.

Slicing report with the values from a lookup field

We can therefore see that there are several options for accessing data for a lookup field, ranging from simple to complex. The trade-off for simplicity is flexibility. Which approach used will depend on your requirements but storing the lookup table separately is the most efficient as the data is only stored once and referenced.

6 Comments

Using Power BI to Report on Hyperlink or Picture Fields in SharePoint

Published April 12, 2018

This post is the fifth in a series exploring Power BI and complex data types in SharePoint. This one details the use of SharePoint Hyperlink or Picture fields. The previous posts are:

A hyperlink or picture field in SharePoint consists of a name-value pair. The value is always a URL, and the name is the descriptor for that URL. When the field is created, the creator can specify the character of the field, whether it is a hyperlink, or a picture.

Hyperlink or Picture field in SharePoint

If picture is chosen, then all values will be treated as images, and SharePoint renders them as such wherever displayed, in forms, views, etc. The name part of the name-value pair is used as the alt tag for the image when it is written. If hyperlink is chosen, the name portion will be displayed wrapped in a link to the value. These behaviours are particularly suitable to the way that Power BI works with both link and images, as we’ll see shortly. In our example below, we’ll be working with a list that contains 2 instances of this field type, one configured as a picture, and the other as a hyperlink.

The List

Consider the following list that contains two of these fields. The first named “Picture” is not surprisingly configured as a picture type, and the other, “More Info” is configured as a hyperlink:

Both Picture and hyperlink fields in a SharePoint view

This view renders a thumbnail of the image for the “Picture” field, and a clickable hyperlink using the link name for the “More Info” field. Behind the scenes however, the data is simply stored as that name-value pair. We will be able to get both field types to render with appropriate behaviours in a Power BI report, but first we need to build the report using Power BI Desktop.

Loading the Data

Loading the Listings data

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 columns that you don’t need. We can do this by right clicking on the desired column titles and selecting “Remove”. 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” column, as we’ll be needing that to extract the text values.

Using FieldValuesAsText

In our example, both the “Picture” and “More Info” fields are displayed with a linkable value of “record” for every row. We will explore using these columns below, but to use FieldValuesAsText, it is best to remove them to avoid naming conflicts. As with most complex field type, the “FieldValuesAsText” column can be used to extract the URL for the Hyperlink or Picture field.

We scroll right and select the expander icon for the “FieldValuesAsText” column, then deselect all available fields except the “Picture” and “More Info” columns. In addition, we want to ensure that the “Use original column name as prefix” option is deselected to avoid a lot of messy renaming later.

Retrieving data with FieldValuesAsText

We then select OK, and two new columns are added in place of FieldValuesAsText named “Picture” and “More Info”. These columns contain the value portion of the name-value pair that make up the Hyperlink or Picture field, but the name portion is dropped.

Raw data for the Hyperlink or Picture Field

If all that is needed for the report is the URL portion, then this approach is sufficient, and you can continue working with the data model and report as below. However, to retrieve both the name and the value from the field, an alternate method is required.

Retrieving all Field Values

Instead of removing the “Picture” and “More Info” columns as described in the previous section, retrieving all of the values requires us to use them. In this case, we can safely remove the “FieldValuesAsText” column first, as it won’t be needed. The “Record” values shown for the field value on each row are Power BI’s way of expressing a one-to-one relationship. In this case, each relationship is with a record that has 2 fields, “Description” and “Url”. To use them in a report, they must first be flattened. We do this by selecting the column expander in the upper right of the column title, selecting both fields, and clicking OK.

Expanding the Hyperlink or Picture field

All Hyperlink or Picture fields will have the same properties, and in our case, this needs to be done for both the “Picture” and “More Info” fields. Because of this, it’s likely a good idea to check the “Use original column names as prefix” box to help keep everything straight. The columns can be renamed at any time if desired. Once This is done for both columns, we will see both the description, and the actual URL value for both of our “Hyperlink or Picture fields, as seen below:

Extracting all Hyperlink or Picture properties

At this point, we are ready to load the data into the data model by selecting the “Close and Apply” button from the ribbon. Once loaded, we are placed into the report design canvas. From here, we need to do a small amount of model editing.

Using Picture or Link Data in the Report

We can add a new table to the design surface, and then add “Picture.Url” to the table. We can quickly see that the default behavior is not optimal – it only displays the URL, not the rendered picture.

Raw picture data in a Power BI report

This is because the data model only knows the contents of the field to be text. We need to tell the model that this is a picture, and we can do that by selecting the Model tab in the ribbon, selecting the field in the field selection pane,

Setting the field properties in Power BI

Once flagged in this manner, the images will automatically render as images whenever they are used in tables, and several other visuals.

Picture field rendering in a Power BI table

The hyperlink field must be categorized in a similar fashion as the picture field, with one difference – instead of Image URL as the data category, we pick Web URL. Once we have done this, we can add it to our table above along with a couple of other fields, including the link description.

Adding Web URL data to the Power BI table

The hyperlinks are active and clickable, but they’re not the nicest to look at. They also take up a significant amount of space on the visual. Happily, there is a table feature that we can take advantage of to help us with this. To turn it on, open the table properties pane (the paint roller), open the Values section, and turn on the option for “URL icon” . All of the long links in the table will be reduced down to a compact link icon.

Web URL data formatted as an icon

Ideally, I would like to be able to recombine the link description and the link in the visual, the same way that it is rendered in SharePoint. However, this does work well, and it lends us a nice level of interactivity in our reports.

As we can see above, the SharePoint “Hyperlink or Picture” field is not only available to Power BI, but much of its utility can also be brought forward into Power BI reports.

3 Comments

Using Power BI to Report on Rich Text Data Fields in SharePoint

Published April 10, 2018

This post is the fourth in a series exploring Power BI and complex data types in SharePoint. It discusses working with using Power BI to report on rich text fields from SharePoint. The previous posts are:

A rich text field in SharePoint is a special instance of the multi-line column type which contains formatting attributes. The column becomes “rich” when either the “Rich text” or “Enhanced rich text” options are selected in the field’s definition.

SharePoint rich text fields

The List

Consider the following list that contains a rich text field named “Description”:

Rich text field displayed in SharePoint view

This view displays the value of the rich text field and retains its formatting. Internally all of the formatting commands are stored as HTML, rendering is a simple task for SharePoint. However, None of the default Power BI visuals support HTML rendering. We are left then with two options. We must either retrieve the raw text from the rich text field, and lose the formatting, or find a visual that supports HTML rendering. Happily, both options are possible. As with any SharePoint data, we must first start with Power BI Desktop.

Loading the Data

Loading the Listings data

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 columns that you don’t need. We can do this by right clicking on the desired column titles and selecting “Remove”. 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” column, as we’ll be needing that to extract the text values.

Extracting Plain Text from Rich Text

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. Rich text fields also show their values directly in the editor, but they include both the text and the html formatting commands. The rich text field in our example is named “Description”, and each entry begins with “<div class=…..”.

Rich text field contents in Power BI

Given that none of the standard Power BI visuals support HTML rendering, this is clearly not what we need for our report.

We could perform a series of text substitutions to strip out all the HTML formatting from the column, but that process would be highly tedious, not to mention messy. Happily, The Power BI SharePoint connector can do this for us automatically through the FieldValuesAsText column.

First, we can remove the Description column altogether. Next, with our example in the Query Editor, we scroll right and select the expander icon for the “FieldValuesAsText” column. We then then deselect all available fields except the “Description” column. In addition, we want to deselect the “Use original column name as prefix” option.

Expanding FieldValuesAsText

We then select OK, and we once again have a Description column, but this time it is free of all HTML formatting tags.

Description field with all formatting removed

At this point, we can click on “Close & Apply” in the ribbon, add a table to the design surface, and add a number of dimensions, including our “Description” field. It is displayed in the visual free of the HTML formatting.

Unformatted rich text in a Power BI table

Showing Rich Text in Full Fidelity

There may be cases where we want to use the rich text formatting, and not remove it. As mentioned above, that’s not possible using the out-of-box visuals. We are therefore left to find a custom visual to do this, and at the moment, there is only one such visual to the best of my knowledge. This is the HTML Viewer visual by Pragmatic Works, and its purpose is to do exactly as the name suggests.

To begin with, we need to start at the beginning of the previous section, prior to the removal of the Description column. In this case, we want to use the HTML codes, so the initial “Description” column is perfectly adequate as-is. All that we need to do is to select “Close and Apply” in the ribbon to load the data into the data model.

We must now get the HTML Viewer from the custom visuals marketplace. We click on the ellipsis in the visuals pane, select “marketplace” and then search for the visual using the search box. Once located, we select it, and click “Add”.

Acquiring the HTML Viewer

A new icon for the visual then appears in the gallery.

The HTML viewer displays and renders the values as a list. Only one dimension is allowed, so it is not possible to use it as a replacement for a table. The best way to use this visual is to make it the target of a selection. For example, to see the description of our listings, we can add a slicer on the page using “Title” and the HTML Viewer using “Description”.

Full fidelity HTML in a Power BI report

While it is limited, it is possible to render rich text fields from SharePoint in full fidelity. However, if only the text is necessary (as is likely the case for reporting), Power BI gives us a rich set of tools to make this process relatively painless.

25 Comments

Using Power BI to Report on Managed Metadata Fields in SharePoint

Published April 9, 2018

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

Managed metadata fields in SharePoint are implemented as a special case of a lookup field. As managed metadata is used in SharePoint lists, a hidden list in the root of the site, TaxonomyHiddenList is populated with the values used. The Power BI SharePoint list connector is aware of this, and it provides helpers to make it relatively easy to get the value of these fields. It is also possible to get retrieve some of the extended properties of these fields, specifically all the language variants of the managed metadata values. We’ll examine several approaches to extracting managed metadata information.

The List

Consider the following list that contains a managed metadata field named “City”:

Example list of listings

This view displays the value of the managed metadata field in the language of the site. If multiple language values have been defined for the term, the appropriate one will be used. While not shown in the view, these multiple language values, along with other attributes of the term are available to Power BI as needed. Depending on requirements, there are several ways to access the terms values and attributes in Power BI. To do so, the report must be built using Power BI Desktop.

Loading the Data

Importing Listings data

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” column (we’ll come back to that shortly). Our simplest requirement will be to extract the value of the metadata column in the default language of the site, the same way that it is represented in the SharePoint list view. We can accomplish this using the FieldValuesAsText column.

Extracting the Term Value

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, our managed metadata column, “City” shows a clickable “Record” value for all rows. Clearly, “Record” is not what we are looking for, but this is how Power BI represents a one-to-one relationship. In this case, the relationship is between the source data row, and the row representing the managed metadata term. We will explore using this column in the next section, but if all we want is the value of the term, we can have Power Query (the Power BI Query editor is really a user interface for Power Query) automatically extract it for us using the “FieldValuesAsText” column.

We scroll right and select the expander icon for the “FieldValuesAsText” column, then deselect all available fields except the “City” column, as well as the “Use original column name as prefix” option.

Using FieldValuesAsText

We then select OK, and a new column “City.1” is substituted for the original column with the value of the term.

Extracting the Managed Metadata value

The reason that “.1” is added to the column name is that we already have a column named City. If the value is all that is needed, it can safely be removed, and this column renamed. However, if additional term data is required, a more complex approach is required.

Extracting the Managed Metadata Properties

As noted above, the “City” column links to the managed metadata term. It can be used to get at the underlying term properties by clicking on the column expander to the right of the column title and selecting the TermGuid column.

Extracting the field properties

The Term column will display the id of the label of the managed metadata field, which is not what we need here. TermGuid will ultimately link to the term properties that we need, but when we click OK, we can see that the resulting values are simply a collection of GUIDs. Since we will use these values to link to the data we need, we must first set their data type. We do this by selecting the column, then the “Transform” tab in the query editor and selecting the “Text” data type.

Setting to Text type

At this point, we are ready to link to our terms list.

Linking to the Hidden Taxonomy List

As mentioned above, SharePoint adds items to a hidden list whenever managed metadata terms are used. This list, “TaxonomyHiddenList” is used for all lists in the site. We therefore need to add this list to our query. To do so, we repeat the procedure used above to get our listings data, substituting “TaxonomyHiddenList” for “Listings”.

As mentioned above, this list will contain values for all instances of managed metadata in the site, not just the field that we’re after. While not strictly necessary, it’s a good idea to filter out the values that are unnecessary, particularly if we want to use the term as a slicer. If we don’t all sorts of values could appear that make no sense to our report. In our example, we have listings with two cities, “Guelph” and “Elora” but there is also a value for “North America” that comes from another managed metadata instance. Examining the data we can see that the “IdForTermSet” column uniquely identifies the “City” column. Filtering on the GUID for our column will remove all extraneous data.

Loading the TaxonomyHiddenList

The Unique ID of the term in question is stored in the “IdForTerm” column. The term’s value is stored in the “Term” column, and the hierarchical path to the term is stored in the “Path” column. These columns will hold the value of the term in its default language. Scrolling right, we can see that there are many columns titled “Termxxxx” and “Pathxxxx”. These correspond to the term’s value in various languages, the language being identified by the xxxx value – it is the code of the language in question. For example, the language code of French is 1036, so if we want the French values for the term, we will need to use the columns Term1036 and Path1036.

If the term is not in a hierarchy, both the Term and the Path values will be identical. If values have not been defined for he additional languages, then the default language value will be returned. It is important to identify the data needed, and to remove all redundant column data at this point to avoid bloating the data model. In our case, we only need the default language, and there is no hierarchy, so we remove all columns with “Path” in the name, and all columns with “Termxxxx”. In addition, we can remove all the extended columns, and system columns. In the end, in our case we are left with the GUID for the term (named IdForTerm), and the term itself in the default language.

Removing Extraneous fields

The final step is to change the data type of the “IdForTerm” and the “Term” columns (and any other columns necessary) to Text, using the procedure for “TermGuid” above. Given that “Term” is generic, and we have filtered ours down to the city terms, we also rename the column to “City”.

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 Taxonomy (renamed from TaxonomyHiiddenList). We then select the relationship editor tab. By default, Power BI may assume a relationship for us. In our case, we retained the id field from the taxonomy list, so it established a relationship between the id fields in the two tables, which is incorrect. We need to select the relationship by clicking on the line between the two tables, and then pressing delete. Once deleted, we need to establish a relationship between “TermGuid” in the Listings table, and “IdForTerm” in the Taxonomy table. We establish this relationship by dragging one column onto the other. Once established, we double click on the relationship line to set the value of “Cross filter direction” to “Both”.

Establishing the relationship

We can now return to the design pane, add a table visual, and add columns from both tables. In this way, we can slice a listing report on the value of the managed metadata column, in any language that we have defined for it.

Working with the report

Clearly, it this case, if we simply want to get the default language value of the term, the FieldValuesAsText approach is far simpler, but linking to the hidden taxonomy list makes it possible to access all available attributes of the managed metadata term.

3 Comments