cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
stvcdms Regular Visitor
Regular Visitor

Power Query SharePoint List Error with Allow Multiple Selection on People or Group Field

Hello,

 

I noticed today that if I am trying to connect to a SharePoint list and that list has a People or Group field with the allow multiple selection option enabled, that it causes a null value error. Changing the field to disable allow multiple selection resolves the issue. Has anyone else noticed this or know a way around this?

 

Steve

24 REPLIES 24
Vicky_Song Established Member
Established Member

Re: Power Query SharePoint List Error with Allow Multiple Selection on People or Group Field

Hi Steve, 

 

Yes, I can reproduce your issue. When I set the Person or Group column to be "Allow multiple selections", and at the same time has null value in the column, I get the error message: "DataSource.Error: We couldn't parse OData response result. Error: A null value was found for the property named 'omqwId', which has the expected type 'Collection(Edm.Int32)[Nullable=False]'. The expected type 'Collection(Edm.Int32)[Nullable=False]' does not allow null values." when connect to the SharePoint list in Power BI.

 

I need to either fill these null values, or disable allow multiple selections as you figured out. 

 

I will report this issue internally, and will post back once I get any updates. Thanks! 

 

 

Vicky_Song Established Member
Established Member

Re: Power Query SharePoint List Error with Allow Multiple Selection on People or Group Field

@stvcdms, It is identified as a known issue with SharePoint that the team is actively working to resolve. Will keep you updated once I get any updates. 

radian21 New Member
New Member

Re: Power Query SharePoint List Error with Allow Multiple Selection on People or Group Field

Hi.  I'm encountering the same error trying to connect Power BI to a SharePoint list that contains multi-select choice fields.  I recall having a similar problem connecting Excel to the same SP list via the SharePoitn OData REST service.  Excel could not parse the XML node containing the multi-select values.  Likely something similar happening here.  

 

Any updates?

amenne Frequent Visitor
Frequent Visitor

Re: Power Query SharePoint List Error with Allow Multiple Selection on People or Group Field

I am having the same issue!

 

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

 

Where are we with a fix on this?

 

ShaneC Frequent Visitor
Frequent Visitor

Re: Power Query SharePoint List Error with Allow Multiple Selection on People or Group Field

Any further updates to this? It is a pretty common scenario and the fact that Microsoft is supporting the ability to allow Power BI to connect to a SharePoint Online list as a supported datasource, and it fails, is pretty crazy!

 

Out of the box it fails and you have to set the API version from 15 to 14 to to get something working.

 

Please help with this!

vanghjort Frequent Visitor
Frequent Visitor

Re: Power Query SharePoint List Error with Allow Multiple Selection on People or Group Field

If I change the API version from 15 to 14, I still get an error:

 

Expression.Error: The key didn't match any rows in the table.
Details:
    Key=Record
    Table=Table

 

Do some of you have a way to remove this error?

 

Thx in advance

julianmbs Frequent Visitor
Frequent Visitor

Re: Power Query SharePoint List Error with Allow Multiple Selection on People or Group Field

I was getting a similar error:

 

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

 

I have "Assigned To" column in my list, and I changed it to not allow multiple selections. So now I am getting this error:

 

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

 

I don't even have "Previously Assigned To" column in my list, so where is it coming from?

 

If I change the API version from 15 to 14, I get an error as the poster above:

 

Expression.Error: The key didn't match any rows in the table.
Details:
    Key=Record
    Table=Table

 

Is there some kind of work-around for this issue?

 

julianmbs Frequent Visitor
Frequent Visitor

Re: Power Query SharePoint List Error with Allow Multiple Selection on People or Group Field

I got an advice from a colleague that dealt with the same problem: Connect to the Sharepoint List as OData Feed instead.

 

I have tried it before like this: https://companyname.sharepoint.com/sites/Home/Analytics, but was getting an error "User not authorized". So he had me try this: https://companyname.sharepoint.com/sites/Home/Analytics/_vti_bin/listdata.svc and it worked!!

umamahesh2u Frequent Visitor
Frequent Visitor

Re: Power Query SharePoint List Error with Allow Multiple Selection on People or Group Field

Is there any fix available for this?

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

Top Ideas
Users Online
Currently online: 6 members 2,554 guests
Please welcome our newest community members: