📊Power BI and SharePoint 👥Multi-People-Picker Columns

In this video, we will take a look at how to work with Multi-Choice columns and Multi-People-Picker
columns from #sharepointonline in #powerbi.

SharePoint Multi-Choice columns are shown in Power BI as lists or tables depending on the API
version being used to connect to SharePoint. If it's API version 15 the column is seen as a list and the
values can either be extracted in the same row separated with a delimiter of our choice or extracted
into new rows. The latest will break the distinct values condition and the table cannot be used for a
one-to-many relationship anymore (being on the 1 side of the 1:n relationship schema).
If it's API version 14 the column will be recognized as a table which then allows us to expand the
column called "Value" and get the data in new rows. This will again break the distinct values of the
table.

The Multi-People-Picker column throws errors with API version 15 and I haven't been able to find any
solution other than switching to API version 14. This will cause the already applied steps to not be
recognized anymore and force us to retake the steps in Power Query from the beginning. After that,
the column is recognized and the data can be extracted.
To keep the main list with distinct values, I moved the two columns into their own tables and extracted
the values there into new rows. That way I was able to create a one-to-many relationship between the
main list table and the other two tables, one containing the Multi-Choice column and the other one
containing the Multi-People-Picker columns.

Hope this video helps!

If you want to learn more about Power BI, you are welcome to follow the channel and subscribe to the Power BI playlist here:

Feel Free to follow me on other social media platforms as well:

[IsatuThomas]

Thank you! I have been struggling with trying to figure this out for awhile. Excellent tutorial!

[lillianjensen]

Thank you! This was perfectly what I needed! Fixed two issues in one video!!

[GiudiciFan]

You don’t know how much I love you, literally every problem I had using PBI you had a video for it!

[prasanna]

Excellent tutorial. I was struggling on multi value from Person and Group field and this solved the problem. Thank you

[AmanShaikh-blqi]

Bhaaiii...!! absolutely genius. Nothing but respect 🫡

[aidasouzadasilva]

Extremely helpful tutorial! I had no idea how to make a multi-selection Person column to work in Power BI and here is the solution. Thank you :)

[mercedesiacuzzi]

Very helpful!! I couldn't find the solution anywhere... thanks a lot!🙂

[Brothercole]

bro, I have been pulling my hair out trying to migrate SharePoint list data to Dataverse but was having issues in the table power query because of multi select choice columns and you have explained it perfectly. liked and subbed. Thank you so much.

[icecole]

bro might just saved my job, thanks man God bless

[MateuszSlifirczyk]

This seems to work when you have just one multi value column. I have 3 and although I have done 3 tables to separate them all, I am unable to use them together for visuals as there's no relationship. The only relationship is to the main table with the key. Creating relationship between those dim tables only allows many-to-many which doesn't really work.

[janetzahner]

excellent and simple tutorial. thanks so much!

[itikasarkar]

Thanks a lot man 🎉
This is exact what I needed

[traveladventures]

Thank you so very much for this! This was extremely helpful!

[andre]

Thanks man, you saved me so much trouble

[BPN]

Thank you this is perfect! What if using your example, I want to keep all the owners in a single row rather than separate rows, like seperated by a semi colon?

[MO-diup]

Hi. Thank you for the video. I have a multiple SharePoint lists with at more than 20 choice each… so it is too time consuming. Any other alternative?

[Lauer]

Thanks for the great tutorial, it helped me a lot! I have a question, this method still sincronizes the sharepoint list data with the power bi dashboard?

[sandeshkhilari]

I'm using similar data its working but when i'm using data with appended data then owners detail coming as blank.

I have 10 shp where owners data coming but 1 tsharepoint using people format to update name which is not coming.


Can you try with your same data and append data with another shp and try owner name is coming or not

[LouieTESTLoboTEST]

2 Hours Later Thanks for the video- very helpful!

[AmaEliggi]

I am getting an error when trying to change to API 14. "DataSource.Error: SharePoint: Request failed: The remote server returned an error: (409) Conflict. (Conflict)". Do you know how to fix this?