Forum Discussion
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
33 Replies
Am still running into this issue as of today. Hard to believe it's still not working correctly--the thread is a year old.
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!
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.
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!
Hey Vicky,
Was there ever a fix for this scenario?
I'm encountering this situation also.
Thanks!
Evan
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=TableIs there some kind of work-around for this issue?
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!!
julianmbs wrote: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=TableIs there some kind of work-around for this issue?
I am having the same issue as him...please advise....thanks.....:)
I just downloaded this product to confirm that the editor (and problem) is the same as the Power Query engine in Excel 2013, and it is. Those of you changing your ApiVersion to 14 still get an error because the 15 version uses different filtering syntax than the 14 version - the error is with the second line.
If you delete the second step and have the query pull from source, you can then visually navigate through the UI to your list and it should work. Or, you can open the advanced editor from a blank query and just paste / replace the default text with this:
let
Source = SharePoint.Tables("https://YourSharePointServerHere.com", [ApiVersion = 14]),
List = Source{[Name="YourListNameHere"]}[Content]
in
ListThe alternative is to learn a little OData syntax and query through ListData.svc, which is a little trickier (but seems to be much more efficient). Or, you know, maybe have the SharePoint team actually fix the 15 API. But you shouldn't be holding your breath (or worse, giving up on it!) in the meantime.
same here:
i try this:
let Source = SharePoint.Tables("https://YourSharePointServerHere.com", [ApiVersion = 14]), List = Source{[Name="YourListNameHere"]}[Content] in Listan get this error:
Expression.Error: The key didn't match any rows in the table. Details: Key=Record Table=TableChanging to [ApiVersion = 14] in Source fixed my problem with the SharePoint List column names not updating to the current column SharePoint title when pulling the list into power Bi. Microsoft should fix this for 15!
The problem "Expression.Error:" went away for me if you rebuild the applied steps in the query editor after the Navigation (default name) step. (Note: in ThoSch's code step was called List.)
Is there any fix available for this?
I am still facing this issue. Any resolution/workaround we have for thi issue?
Does anybody have an idea when this SharePoint issue will be resolved?
same problem for me with multi selection columns and even the version column !
I'm still getting this problem. I have a few Person columns that are single selection only and I am able to exapnd those fine. I have one multi select column which shows as a table rather than a record and I get an error when I try and expand it.
I just want to get a colon separated list of names like you do when you export to Excel.
Very frustrating!
Is there any update to this?
If I was to use the OData fix, I don't get columns "Managed Metadata" column types returned. If I change the "[ApiVersion = 15]" from 15 to 14 it switches me back to SP2010 which doesn't allow me access to "Managed Metadata" column types either. Setting certain columns to not be null or not multi-select is not an option.
Environment:
- SP2013
- Power BI Desktop version 2.56.5023.942 64-bit (March 2018)
Any updates regarding this error? It seems that the issue occures since 2015 and still not resolved...
