📊Power BI and SharePoint 👥Multi-People-Picker Columns
Vložit
- čas přidán 30. 10. 2022
- 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:
• Power Automate & Share...
Feel Free to follow me on other social media platforms as well:
linktr.ee/EneaLicaj
Buy me a pizza 🍕- bmc.link/enealicaj - Věda a technologie
Excellent tutorial. I was struggling on multi value from Person and Group field and this solved the problem. Thank you
2 Hours Later Thanks for the video- very helpful!
Thank you! This was perfectly what I needed! Fixed two issues in one video!!
Happy I could help! Subscribe if you like :)
Just did! I did run into the problem that if there is more than one person, it is showing as 2 rows in a table (all other info in the row is duplicated but the names are the same. Any suggestions?@@Enea
You will either have to concatenate all names from the rows in one row (separated by a delimiter like semicolon) or (my suggestion) duplicate the table and in the first table remove the people column and leave all the other info (the one you said is duplicated) and make sure that they are not duplicated anymore since you removed the people column. Then in the second column keep the ID for the list item and remove everything else apart from the people column. Then in the modeling view you can create a one-to-many relationship between your general info table and your people table. Makes sense?
Thank you! I have been struggling with trying to figure this out for awhile. Excellent tutorial!
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 :)
Happy I could help! Subscribe if you like :)
This was very helpful , kudos🤜
Very helpful!! I couldn't find the solution anywhere... thanks a lot!🙂
excellent and simple tutorial. thanks so much!
Thank you so very much for this! This was extremely helpful!
Thank you Enea fr saving my life from thorn rose
Dont know who that is but happy I could help
This was so helpful! Thanks!
Thanks a lot man 🎉
This is exact what I needed
Thanks man, you saved me so much trouble
bro might just saved my job , thanks man God bless
Cheers mate, subscribe if you want, would help me as well :)
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.
I hope you found my video in time and still got some hair bro. Thanks 😊👍🏻
Hmm, so sadly even after following your video and using the API 14, creating a new table and extracting the values I still get a mashup error: Cannot convert type record to type text :( not sure why it doesn't work@@Enea
@@Brothercole1994 cannot tell from here, would have to see it. Reach out on LinkedIn or Fb
Good Job!
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.
First of all, thank you for your CZcams channel videos. They helped me a lot. I am working on an office project. I made a power app to fetch data to SharePoint list. Now I am making a real-time power bi report out of it. I have some multiple-choice selection fields in my SP list. I can’t extract data in power BI Online. That’s why I am trying to connect the SP online list to Power BI desktop. But it throws credentials errors while connecting. I am unable to connect. I searched a lot and tried many solutions but all in vain. I would be grateful if you could do any help in this regard.
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?
Video how to do it droping today ;)
Thanks for sharing this, very helpful. When you create this in Power BI desktop and publish, does that take new entries automatically into this model or do you need to run a manual process (or power automate)?
Yes, the model puts all new data into the format and structure you predefined. As long as you don’t change anything to the data type, structure, etc. should work good and as expected
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
hello, thanks for the valuable information. this doesn't work anymore what is the new method
Very helpful. Question: are there any side-effects of going back to the API version 1.14? In other words, would it impact our ability to use features that are available on 1.15 or higher?
Good question, couldnt find any warnings in this regard. Here is what I found: "A number (14 or 15) or the text "Auto" that specifies the SharePoint API version to use for this site. When not specified, API version 14 is used. When Auto is specified, the server version will be automatically discovered if possible, otherwise version defaults to 14. Non-English SharePoint sites require at least version 15.
This is very good. I'm wondering if you have ever tried to view columns in power bi from a sharepoint list column which allows users to append changes to existing text. For some reason I'm not seeing any links. Most of the values I'm seeing are null and the ones that I do see the latest text only.
Thanks James, can you please explain a bit more which column type you mean? Happy to experiment on that on a next video :)
@@Enea It is a text field. I selected Enhanced rich text (Rich text with pictures, tables, and hyperlinks). Then I selected Append Changes to Existing Text - Yes. We did this as the team wanted to be able to see additional notes in the same "box". In the Sharepoint List it shows a clickable link that says "View Entries". When I added it to Power BI through Sharepoint Online Lists, the field mostly says null for all records with a few showing values. And I can't seem to transform that field either. Does that help?
@@jamesdeanstephens yes that helps, let me see what I can do :)
FYI, found a solution, will try to do the video in the upcoming 1-2 days
@@Enea that’s great! I’m looking forward to it. Thanks!
very useful