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!
