Teams Real Simple with Pictures: Lookup Columns in Lists. Surfacing in Power BI.

This blog is part of a series on Teams. For more articles, check back often

Written: 21/08/2022 | Updated: N/A

I had an enquiry from a co-worker this week. It was as follows: they had access to two lists that they used daily. These lists were in two different teams’ channels. However, they wanted to bring some of the data from one list into another to ‘make their life easier’. On top of this they wanted – as a bonus – to display the data visually. However, they were not permitted to amalgamate the lists due to others using them and the business wanted them separate. So how could we solve this to make things easier? This is almost a textbook case for using lookup columns. What we need is a list relationship, where information from one list (the source list) can be used in another list (the target list). List relationships let you join information from two lists and keep it consistent while people edit and delete list items. Sound good? It is. However, there is four really important things to know with lookup columns before we get going. First, they don’t currently support all column types. Whilst Single Line of Text, Date and Numbers are supported, other types such as Choice and Currency are not. Secondly, they are only supported in the same SharePoint site. In Teams land that means only if the lists are in public channels in the same team or if both lists are in the same private channel. No cross team. No cross public and private channels. Third, if you are using large lists then lookup columns may not be a solution. At that point we may be looking at something like SQL and Power Apps. But the biggest one – number four – is this. A lookup column does not automatically add values from a source list. A lookup column allows you to add values held in a source list. In other word the source list seeds values for the target – but they have to be added manually. This is an important distinction. Here is how to create a list with lookup columns to another list. And as a bonus, connect that List to Power BI to report on it

Let’s go.

This blog will cover

• Creating the Target List
• Creating the Source Lists
• Adding the Lookup Columns
• Using Power BI

Note this blog may have some abridged steps which will assume some experience with a Microsoft 365 environment and Teams

Prerequisites

• Teams and SharePoint Licence (Within Microsoft 365 Licence)
• Access to the Lists app and to create tabs within the Team
• Use of Power BI for Lists. Power BI Desktop does not render lookup columns even though you can connect the list.

CREATING THE TARGET LIST
Let’s create the target list. Since we are looking at List relationships there needs to be a relationship between the source(s) and the target list. The relationship we are going to explore is Department Vs Device where these are tracked independently. I am going to create a target list called Department first. Whether you create the source or target first is completely optional. Use the methodology that feels best

1.) Select the Team, then the Channel then select Add (+). This example is creating the Department list in the Admin Team general channel

2.) Search for, and select, Lists

3.) Select Save

4.) Select Create a List

5.) Select Blank List

6.) Name and Describe the List and then select Create

7.) Amend the columns as required. In this example we will Rename the title column to User and then add one Single Line of Text column for the Department. The end result is two columns ready to go all of which are supported when it comes to doing lookups

8.) Fill the columns with data. Once done it should look something like this. Our target list is ready to go

CREATING THE SOURCE LISTS

Repeat the steps for the source lists in the second Teams channel – remember that the lookup will only be able to pull from lists in the same SharePoint Site. This example will create two source lists in a second channel called Devices and these will have both have one Single Line of Text column – Primary Device and Mobile Device respectively. Make sure that the source list columns you intend to put into the target list are clearly distinguishable in their names to avoid confusion. Also note since the source lists are seeding the target there is no need to make them equal size to the target since the values of the source could occupy multiple fields in the target

ADDING THE LOOKUP COLUMNS

Now that the target and sources have been added it’s time to take the two columns from the source lists called Primary Device and Mobile Device and add them to the target list

1.) In the target list (Here the Revenue List in the General channel) select add column and then lookup

2.) State a Name for the column (Recommended to mirror the Source Column Name) and link it to the Source List and Source Column. Once done select More Options

3.) The More Options section includes a few items which may be of interest for the continued sync and management between source and target. This includes pulling in other columns from the source list, ensuring source list items cannot be deleted as well as enforcing unique values. Once reviewed select Save

4.) What we now have is an empty column called Primary Device. Rinse and repeat for the second column Mobile Device. As stated in the introduction values are not automatically populated from the source list

5.) Edit the items, or Edit in Grid View and now populate the lookup columns with surfaced values from the source list

6.) We now have the target list with lookup values. Modifying these in the source list will change them in the target list

USING POWER BI

So, having tested Power BI Desktop does not support lookup columns since they do not render correctly, they show integers as opposed to the text

However, we can use the Power BI Integration through the Lists web app via Office.com

1.) Log into the Lists Web App via Office.com and select The Target List

2.) Select Integrate, then Power BI then Visualize This List

3.) The Power BI Report is created. Select Edit

4.) Clean it up and select Publish to this List

5.) Name the Report and select Publish

6.) The report is now saved and accessible via the Lists Web App. Unfortunately, it cannot be surfaced into Teams at the time of writing via the Power BI Teams App or via the Web Page app, but it does prove that reports can be based off Lists with lookup columns meaning you can build reports on multiple lists which pull through to a target list and which update dynamically.