Using Power BI to Report on Lookup Fields in SharePoint

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:

• Using Power BI to Report on Multi-Value SharePoint Fields
• Using Power BI to Report on Person Fields in SharePoint
• Using Power BI to Report on Managed Metadata Fields in SharePoint
• Using Power BI to Report on Rich Text Data Fields in SharePoint
• Using Power BI to Report on Hyperlink or Picture fields in SharePoint

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.

The List

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

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.

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”.

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.

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”.

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.

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.

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.

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.