PortiBlog

Error with People or Group Field when using Power BI with SharePoint Online

22 december 2016

For our migration project we wanted to create a report in Power BI to give our stakeholders insight in the progress our project made. For this situation we created a Power BI report in the desktop application of Power BI. The project team makes use of a SharePoint Online site to manage the progress of the migration. For this we have created several lists inside SharePoint Online and created a workflow on our main list "PortalsOverview".

If we included our main list in the Get Data wizard of  the report and choose "SharePoint Online List" we get the following error:

error-multivalue-persons-field

The error message is:
"DataSource.Error: We couldn't parse OData response result. Error: A null value was found for the property named 'ApproversID', which has the expected type 'Collection(Edm.Int32)[Nullable=False]'. The expected type 'Collection(Edm.Int32)[Nullable=False]' does not allow null values."

After some research I found out this was caused by a  Person or Group column with the "Allow multiple selections" enabled.
In our case this was caused by the "Approvers" People Field, which we use for an approval workflow on multiple persons.

You have several options to fix this error:

  1. Disable the allow multiple selections on the People field "Approvers"
  2. Redesign the logic in SharePoint and run the workflow on another list
  3. Change the datasource in Power BI.

In our case the workflow was already live and in use on the SharePoint Online List, so option 1 and 2 were not viable. So we went for option 3. You can change your SharePoint Online List query into a OData Query and then only select the columns you need from the SharePoint Online list using the following steps:

  1. Go to the Edit Queries interface:
    step1_editquery
  2. Open the Advanced Editor:
    step2_editquery
  3. See the existing query and get the listid:
    step3_editquery
    From the above image you can get the {listid} from the second line in our case "762a5075-b973-415d-adb1-7959b9101fd5"
  4. You can change the query using the following logic, in the $select use the internal names of the columns you want in Power BI:
    Source = OData.Feed("https://{tenant}.sharepoint.com/sites/{siteurl}
    /_api/Web/Lists(guid'{listid}')/Items?$select=Title,
    Site_x0020_Title, Storage_x0020_used, MigrationWorkflow,
    Batch, NumberOfWebs, DestinationUrl")

    step4_editquery

You then can use the datasource with the correct columns you need from your management list in SharePoint Online.

To summarize, if you run into this error you have 3 options to fix this, but only one if you would like to keep the existing configuration in your SharePoint Online List.

Submit a comment