Connecting to a SharePoint List Office 365 error

[3sun_Jack]

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

[karo]

Any news regarding this error from the Microsoft side?

[abhijeet01]

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]

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

[djeepy1]

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

[rgparisoto]

Hi @djeepy1! The Power Bi return me this:

"The User was not authorized"

I don't know how to fix it. 

[djeepy1]

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

[rgparisoto]

Yes @djeepy1!

It works.

[djeepy1]

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

[rgparisoto]

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]

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]

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

[Rémi]

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.

[3sun_Jack]

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

[Rémi]

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

[ssinchak]

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.

[Anders99]

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.

[kleinbth]

This error message is also been given by a multiuser field

[kleinbth]

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

[kompas_ua]

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

[kompas_ua · 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