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

Connecting to a SharePoint List Office 365 error

Hi I am receiving the following error when I try and link a SharePoint site to Power BI

 

DataSource.Error: We couldn't parse OData response result.  Error: A null value was found for the property named 'Actionee1Id', which has the expected type 'Collection(Edm.Int32)[Nullable=False]'. The expected type 'Collection(Edm.Int32)[Nullable=False]' does not allow null values.
Details:
    DataSourceKind=SharePoint
    DataSourcePath=https://#######.sharepoint.com/BD/_api/Web/Lists(guid'#####-####-###-###-#####')/Items

 

I have seen this error before, but previously I could delete the column in question, unfortunately, this time I cannot

the field in question is a people field which is non mandatory

 

any help would be gratefully received

 

Kindest regards

 

Jack

 

1 ACCEPTED SOLUTION


I changed the ApiVersion from 15 to 14 in the Query Advanced Editor, and it works now 😃

let
    Source = SharePoint.Tables("https://###.sharepoint.com/teams/Team020/", [ApiVersion = 14]),
    ProjectList = Source{[Name="ProjectList"]}[Content]
in
    ProjectList

View solution in original post

37 REPLIES 37
Advocate III
Advocate III

Any news regarding this error from the Microsoft side?

bit late to this forum, posting what we tried in team, and could be helpful to few new ones.. 

 

when I was getting error, 

1) cleared all my attempted data source connections 

PowerBi -> File -> Options and settings -> clear permissions to sharepoint folder

 

2) connected again 

Get Data -> SharePoint Online List -> https://xxx.xxx.com/sites/companyName/lean
(my datalist is coming from https://xxx.xxx.com/sites/companyName/lean/Lists) 
PS: Use correct onganisational account to connect and you need right permissions in sharepoint (https://docs.microsoft.com/en-us/sharepoint/understanding-permission-levels
view only permission didn't worked so i requested "Read" and succeeded. cheers !!!

New Member

let
  origem = OData.Feed("http://xxxxxxxx/_api/Web/Li​sts(guid'FEF2602C-661B-4268-8056-1CC347B21489')/Items?$select=Accomplishments,ADDepartmentCPMO")
in
  origem

 

Give me this error

 

DataSource.Error: Microsoft.Mashup.Engine1.Library.Resources.HttpResource: Request failed:
OData Version: 3 and 4, Error: The remote server returned an error: (400) Bad Request. (The expression "Web/Li​sts(guid'FEF2602C-661B-4268-8056-1CC347B21489')/Items" is not valid.)
OData Version: 4, Error: The remote server returned an error: (400) Bad Request. (The expression "Web/Li​sts(guid'FEF2602C-661B-4268-8056-1CC347B21489')/Items" is not valid.)
OData Version: 3, Error: The remote server returned an error: (400) Bad Request. (The expression "Web/Li​sts(guid'FEF2602C-661B-4268-8056-1CC347B21489')/Items" is not valid.)
Details:
    DataSourceKind=OData
    DataSourcePath=http://xxxx/_api/Web/Li%E2%80%8Bsts(guid'FEF2602C-661B-4268-8056-1CC347B21489')/Items
    SPRequestGuid=5714d39d-8fd8-b0bc-319d-ee537d33ffb1, 5714d39d-4fe3-b0bc-319d-e42451efe2a8, 5714d39d-0fee-b0bc-319d-eecf29d902df

You get a HTTP 400 error that indicates your request is incorrect (HTTP 400 Bad Request).

In error stack, I noticed :

 

     [..]DataSourcePath=http ://xxxx/_api/Web/Li%E2%80%8Bsts[..]

 

%E2, %80 and %8B are inserted in your URL. Can you check by typing your URL in a web browser ?

 

Jean-Pierre

Jean-Pierre Riehl
MVP Data Platform
Helper I
Helper I

Hi @djeepy1! The Power Bi return me this:

"The User was not authorized"

 

2016-07-01_15-13-58.png

 

I don't know how to fix it. Smiley Sad

If you test the URL in a browser (being previously logged in O365), does it work ?

Jean-Pierre Riehl
MVP Data Platform

Yes @djeepy1!

It works.

When you type URL in OData feed, Power BI Desktop should prompt for authentication (O365 windows). Be sure to choose OAuth authentication.

 

Can you verify you have the lastest version of PBI Desktop ?

Did you use the OData Feed source in Get Data menu ?

You can also check on in Data Source Settings window if Authentication is correct.

 

JP

Jean-Pierre Riehl
MVP Data Platform
Helper I
Helper I

Guys!

I've tryed to chance the version 15 for the 14 and this another error ocurred:

 

Expression.Error: A chave não correspondeu a quaisquer linhas na tabela (In English: The key does not match any rows in the table)

.
Detalhes:
Key=Record
Table=Table

 

 

Has someone any idea about this?

Frequent Visitor

Is there any workaround without using the API 14? IF I use that, I don't have my custom lists, those only show up if I am using API 15 which in turn gives me the same error but for another property.

Helper I
Helper I

Thanks for posting the question and the subsequent answer.  Would be great to see this fixed.

Resolver III
Resolver III

Hi,

 

Check that all lines have a value into this column.

Although, you can try to delete the "not null" property of the field (I don't know if it's possible, but it might solve your problem)

 

Then I advice you to put a default value into this field, otherwise the issue may appear again.

thats the problem that not all entries require this field to have a entry, i agree that if they were all populated this wouldn't be an issue. but i can not find any where where it stipulates that this field is required, it certainly isn't set in SharePoint

If this field is not always set, put an entry "none" and choose it by default.

I am having the same issue wth a task list with the assigned to field.  There is no option to set a default value for this one.  

 

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.Int32)[Nullable=False]'. The expected type 'Collection(Edm.Int32)[Nullable=False]' does not allow null values.

This error also exists in newer versions of Power Query, we tracked it down to an error in the Query code that Power BI and the newer versions of Power Query use to query the oData feed. Quite a serious bug that needs resolving.

 

Update: A clarification this error will appear if you have any multichoice fields.

This error message is also been given by a multiuser field

Even when I try to remove the fields in advanced mode it will give the error message.

So, as I understood, now there is no any workarounds?


I changed the ApiVersion from 15 to 14 in the Query Advanced Editor, and it works now 😃

let
    Source = SharePoint.Tables("https://###.sharepoint.com/teams/Team020/", [ApiVersion = 14]),
    ProjectList = Source{[Name="ProjectList"]}[Content]
in
    ProjectList

View solution in original post

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors