Work with multi-choice, multi-lookup & multi-choice-people fields from SharePoint in Power BI

21 september 2025 Oprindeligt udgivet på alexanderhenkel.dk

Learn how to visualize and filter from various multi choice options in Power BI from SharePoint

The challenge

When working with SharePoint data in Power BI, it can be a challenge to expand and filter lookup, people and choice fields with multiple values

Here you will learn how to both visualize and filter multi choice values

Power BI report showing a table and slicer filtering multi-choice SharePoint values across lookup, people, and choice column types

Initial setup

Create a new list called Project with a choice column called ProjectType with the following options

Power Automate
SharePoint
Copilot

Don’t forget to the set value Allow multiple selections to Yes

SharePoint ProjectType choice column settings with options Power Automate, SharePoint, and Copilot and Allow multiple selections set to Yes

Get data
In Power Query create a new blank query and click advanced editor

Insert the following code (with needed adjustments, explained below the code snippet)

let
    BaseURL = "https://YOURTENANTNAME.sharepoint.com/sites/YOURSITENAME/",
    ListName = "YOURLISTNAME",
    Select = "Title,Author,ProjectType,ID",
    ExpandColumns = "Author/Title",
    Expand = "Author",

    CountItems = Json.Document(Web.Contents(BaseURL, [RelativePath = "_api/web/lists/GetByTitle('" & ListName & "')/items?$select=ID&$orderby=ID%20desc&$top=1", Headers = [Accept = "application/json"]]))[value]{0}[ID],
    StartID = List.Numbers(0, Number.RoundUp(CountItems / 5000), 5000),

    #"Converted to Table" = Table.FromList(StartID, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    EndID = Table.AddColumn(#"Converted to Table", "Custom", each [Column1] + 4999, type number),
    #"Renamed Columns" = Table.RenameColumns(EndID,{{"Column1", "StartID"}, {"Custom", "EndID"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"StartID", type text}, {"EndID", type text}}),
    selectedfields = "&$top=5000&$select="& Select & "," & ExpandColumns & "&$expand=" & Expand,
    ListItems = Table.AddColumn(#"Changed Type", "ListItems", each Json.Document(Web.Contents(BaseURL, [RelativePath = "_api/web/lists/GetByTitle('" & ListName & "')/items?$filter=(ID ge " & [StartID] & ") and (ID le " & [EndID] & ")&$top=5000&$select="& Select & "," & ExpandColumns & "&$expand=" & Expand, Headers = [Accept = "application/json"]]))[value]),
    #"Removed Other Columns" = Table.SelectColumns(ListItems,{"ListItems"}),
    #"Expanded ListItems" = Table.ExpandListColumn(#"Removed Other Columns", "ListItems")
in
    #"Expanded ListItems"

Change the following variables

BaseUrl
Change to your own site (don’t forget the last /)
ListName:
Change to your list name (the actual name, the one you see on your site)
ExpandColumns:
If you want to expand any columns like a people field, and get the title (name) or the EMail, you insert the expand columns here, like below:
ExpandColumns = "Author/Title,Editor/Title,PersonColumn/EMail,PersonColumn/Title,LookupColumn/Title",
Expand:
Everytime you introduce a new column to expand, it is important you write the name here, like this:
Expand = "Author,Editor,PersonColumn,LookupColumn",
Select:
We are only selecting the actual columns that we need. You can replace it with an asterix (*) that will return all columns from SharePoint Select = "Title,Author,ProjectType,ID"

Step 2
You can now select the arrow icon and click expand

Power Query showing the ListItems column with the expand arrows icon to click and expand all nested list records into rows and columns

Unclick use original column name as prefix and click OK

Power Query expand columns dialog with the use original column name as prefix checkbox unchecked before clicking OK

Notice that everytime you introduce a new column, you need to go back to this step, and select the new column

Note the that code above will only work if you have at least 1 expand column introduced.

Setup staging and transform

Before you continue with your steps, setup staging, transform, dim, & fact tables.

Do the initial setup like below

Power Query query groups panel showing the initial staging, transform, dim, and fact table structure before processing multi-choice columns

Multichoice

We are doing this change in TransformProject

Your multichoice will be shown currently as a list:

Power Query showing the ProjectType column containing list objects rather than readable text values before extraction

We want to extract the values, so that the user can see the values in the report.

Select the two arrows icon in the top right corner and select Extract Values

Power Query column header two-arrows icon menu showing the Extract Values option to convert the list to a delimited text string

Choose Custom and extract with a comma and a space

Power Query Extract Values dialog with Custom selected and a comma-space delimiter entered to join multi-choice values into a single readable string

The column is now available visually for the user in a report.

Power Query showing the ProjectType column now displaying comma-separated text values visible and usable in a Power BI report

Filtering a multichoice
To be able to filter the ProjectType we will create a new dimension.

Create a reference from your staging table and move it to the MultiChoice group

Either Remove or Choose columns so that we only have ID and ProjectType in our table.

Power Query dimension table containing only ID and ProjectType columns after removing all other columns to prepare for expanding to new rows

This time we expand ProjectType to New rows

Power Query expand ProjectType column dialog with Expand to New Rows selected to create one row per choice value per project

Finally we reference our multichoice table to our dimensions

Power Query multichoice dimension table referenced from staging with one row per choice value ready for use as a filter dimension

You can now close Power Query for the final step

Model
In the model reference ID with ID in both tables, and set the direction to Both

Power BI Model view showing the relationship being created between the Project fact table and the ProjectType dimension table on the ID column

Power BI relationship settings dialog with cross filter direction set to Both to enable two-way filtering between the fact and dimension tables

Report
We can now create a report

From the table called Project insert Title and Project Type into a table visual

From the table called ProjectType insert the ProjectType column into a slicer visual

We can now filter a multi choice column

Here we see the table unfiltered Power BI table visual showing all projects unfiltered with their comma-separated ProjectType values visible alongside a ProjectType slicer

Here we see the table filtered Power BI table filtered by a selected ProjectType slicer value showing only projects that include that choice among their multiple values

Multi people column

Step 1
Let’s go back to staging and add ProjectTeam to our select and expand

Select = "Title,ProjectType,ID,Author,ProjectTeam",
ExpandColumns = "Author/Title,ProjectTeam/Title",
Expand = "Author,ProjectTeam",

Don’t forget to add the new column from your last step Power Query staging table showing the ProjectTeam column added after updating the Select and ExpandColumns variables and re-expanding the query

Step 2
Create a new reference to your staging table.

Remove the other columns so that you only have ID and ProjectTeam

Step 3
Expand ProjectTeam to New rows and next expand to title

Power Query showing ProjectTeam expanded to new rows and then expanded to Title giving one team member name per row

Mark your ID column and in the menu under Transformation select Group By

Choose the settings as below

Power Query Group By dialog configured to group rows by ID and aggregate the team member rows into a nested Group table

Create a new custom column and write:

[Group][Title]

Power Query Add Custom Column dialog with the formula accessing the Title field from the grouped nested table to extract all team member names as a list

Extract the values with a custom “, "

In your transform table we can now merge the project team and expand it

Power Query transform table with the project team names column merged in from the team dimension table showing comma-separated names per project

Your table should now look like this

Power Query transform table showing projects with their ProjectTeam column displaying comma-separated team member names ready for the report

Step 4
Now we need to create a dimension table so that we can filter.

Create a new reference to your staging table and keep ID and ProjectTeam

Expand ProjectTeam to new rows and expand Title

Your table should now look like this

Power Query ProjectTeam dimension table with ID and individual team member Title rows after expanding to new rows for use as a filter dimension

Step 5
In the model view we can now bind the Project table with our dimension table

Power BI Model view showing the relationship between the Project fact table and the ProjectTeam dimension table connected on the ID column with both-direction filtering

In the report view add a slicer with the names from our dimension table, and in the table view add the column from our project view

Our report can now filter on a multi choice people column

Power BI report with a people slicer filtering the project table by team member name demonstrating multi-choice people column filtering

Multi lookup column

The lookup column follows the same principle as the people field.

For this list we will also add ISO to the list, so that we can filter on both the country name and country ISO

SharePoint lookup list with Title and ISO columns containing country names and their ISO codes used as the multi-lookup source

Step 1

Add countries to the staging table and expand both Title and ISO

Select = "Title,ProjectType,ID,Author,ProjectTeam,Countries",
ExpandColumns = "Author/Title,ProjectTeam/Title,Countries/Title,Countries/ISO",
Expand = "Author,ProjectTeam,Countries",

Step 2
Reference a new table and group them by ID and create new custom columns for both Title and ISO

[Group][Title] [Group[ISO]]

Here are the full steps

let
    Source = SharePointProject,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Countries", "ID"}),
    #"Expanded Countries" = Table.ExpandListColumn(#"Removed Other Columns", "Countries"),
    #"Expanded Countries1" = Table.ExpandRecordColumn(#"Expanded Countries", "Countries", {"Title", "ISO"}, {"Title", "ISO"}),
    #"Grouped Rows" = Table.Group(#"Expanded Countries1", {"ID"}, {{"Group", each _, type table [Title=text, ISO=text, ID=number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Country", each [Group][Title]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "ISO", each [Group][ISO]),
    #"Extracted Values" = Table.TransformColumns(#"Added Custom1", {"ISO", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    #"Extracted Values1" = Table.TransformColumns(#"Extracted Values", {"Country", each Text.Combine(List.Transform(_, Text.From), ", "), type text}),
    #"Removed Columns" = Table.RemoveColumns(#"Extracted Values1",{"Group"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"ID", Int64.Type}, {"Country", type text}, {"ISO", type text}})
in
    #"Changed Type"

Step 3
Merge the table into the transform table

Power Query Merge dialog joining the countries grouped table into the transform table to add comma-separated country and ISO columns

Step 4
Reference the staging table to create a new dimension table.

As we did earlier, expand to new rows

Here are the full steps

let
    Source = SharePointProject,
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Countries", "ID"}),
    #"Expanded Countries" = Table.ExpandListColumn(#"Removed Other Columns", "Countries"),
    #"Expanded Countries1" = Table.ExpandRecordColumn(#"Expanded Countries", "Countries", {"Title", "ISO"}, {"Title", "ISO"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Countries1",{{"Title", type text}, {"ISO", type text}, {"ID", Int64.Type}})
in
    #"Changed Type"

Step 5
Relate the dimension table to the fact table

We can now filter on both country and ISO

Power BI report with slicers filtering projects by both country name and ISO code using the countries dimension table

You can download my example Power BI report here

Preview of the example Power BI report available for download showing multi-choice filtering across choice, people, and lookup column types

‎