cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
3sun_Jack
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
karo
Helper V
Helper V

Any news regarding this error from the Microsoft side?

abhijeet01
Frequent Visitor

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 !!!

phammk
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
rgparisoto
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
rgparisoto
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?

NicolaeGP
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.

alex
Helper I
Helper I

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

Rémi
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

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors