topic Re: The HTTP header ACCEPT is missing or its value is invalid - SharePoint List in Power Query

The HTTP header ACCEPT is missing or its value is invalid - SharePoint List

mwand — Tue, 07 Mar 2017 15:15:11 GMT

Receiving an error which I was hoping to receive some help with. I am attempting to connect to a Sharepoint list - when loading data into the model I receive the following error message:

Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error: [DataSource.Error] SharePoint:Request failed: the remote server returned an error: (406) Not acceptable. (The HTTP header ACCEPT is missing or its value is invalid.)

Thanks.

Re: The HTTP header ACCEPT is missing or its value is invalid - SharePoint List

mwand — Tue, 07 Mar 2017 15:18:14 GMT

Hello,

receiving the following error when attempting to load data from a sharepoint list.

Failed to save modifications to the server. Error returned: 'OLE DB or ODBC error: [DataSource.Error] SharePoint: Request failed: The remote server returned an error: )4-6_ Not Acceptable. (the HTTP header ACCEPT is missing or its value is invalid.).

Thank you for your help!

Re: The HTTP header ACCEPT is missing or its value is invalid - SharePoint List

Anonymous — Wed, 08 Mar 2017 05:49:10 GMT

Hi mwand,

Could you please provide more information about your scenario?

1. Do you connect to on-premises SharePoint list or SharePoint Online list in Power BI Desktop?
2. How do you connect to the SharePoint list in Power BI Desktop? And what authentication type do you use?
3. What type of columns do you have in SharePoint list?

Thanks,
Lydia Zhang

Re: The HTTP header ACCEPT is missing or its value is invalid - SharePoint List

timberwolf720 — Mon, 12 Jun 2017 13:05:34 GMT

Hi Lydia,

I am also experiencing a similar error. I am trying toconnect to a SharePoint List via teh SharePoint Online List in Power BI. THe list opens okay in the Query Editor, howevever, when I close the Query Editor window and apply the changes, I get the aforementioned error message in mwand's original post.

I am connecting to the SharePoint List using Windows authentication (AD) user credentials I believe. The columns contain mostly text and some numbers, but the datatype is general (any).

Any way to fix or otherwise resolve this issue?

Thanks

Re: The HTTP header ACCEPT is missing or its value is invalid - SharePoint List

gmohrman — Wed, 17 Jan 2018 00:45:18 GMT

I'm getting the following error message ["OLE DB or ODBC error: [DataSource.Error] SharePoint: Request failed: The remote server returned an error: (406) Not Acceptable. (The HTTP header ACCEPT is missing or its value is invalid.)]" and I appreciate the Power BI community forum feedback. However, I don't see that this conversation thread has a solution per se. Did you ever get this resolved? Does anyone else have any instructions or advise to help me with this issue (406)? If so, please help. Thanks,

Re: The HTTP header ACCEPT is missing or its value is invalid - SharePoint List

gmohrman — Fri, 02 Feb 2018 20:14:06 GMT

Did anyone get this resolved? If so, how ...

Re: The HTTP header ACCEPT is missing or its value is invalid - SharePoint List

gmohrman — Wed, 07 Feb 2018 19:12:31 GMT

Did you ever resolve this issue? If so, please share.

Re: The HTTP header ACCEPT is missing or its value is invalid - SharePoint List

phil_m — Sun, 18 Feb 2018 01:01:34 GMT

Hi Lydia,

I am also getting the error:

1. In Power BI Desktop connecting to an on-premises sharepoint list

2. Using Windows Authentication

3. The list has the following column types:

Date and Time
Single line of text
Choice
Number
Multiple lines of text (plain text only, no RTF)
Person or Group (single select)
Calculated field which uses DATEDIF on two other Date and Time fields in the list

Thanks.

Philip

Re: The HTTP header ACCEPT is missing or its value is invalid - SharePoint List

Anonymous — Wed, 03 Oct 2018 10:28:13 GMT

I receive this error also. I am connecting to an on-premise sharepoint list. The data loads in the query editor but flags this error upon attempting to load the data into the model?????????

Re: The HTTP header ACCEPT is missing or its value is invalid - SharePoint List

gmohrman — Wed, 03 Oct 2018 16:26:58 GMT

Can a Microsoft MVP and/or PowerBI Specialist please help with this issue?!

Re: The HTTP header ACCEPT is missing or its value is invalid - SharePoint List

Anonymous — Thu, 04 Oct 2018 20:41:51 GMT

After some investigation work using fiddler our on-prem SharePoint doesn't accept the default header from the SharePoint list connector, if this connector had the custom header option that the web connector has I would be able to get our SharePoint to accept the header. Our SharePoint 2013 is pre sp1, are others getting this post sp1?

Thanks

Re: The HTTP header ACCEPT is missing or its value is invalid - SharePoint List

P3UAL — Mon, 26 Nov 2018 14:31:25 GMT

Hi,

Yes, we've encountered this issue with the SharePoint List connector.

Using Fiddler, I traced what the SharePoint List connector was doing.

You can see the blackbox connector is requesting JSON to come back as JSON Minimal. Unfortunately SharePoint PRE SP1 does not support JSON Light. In the SharePoint SP1 update it included an enhancment to add JSON Light support.

You simply need to amend the headers being sent using the SharePoint REST API however and ask for verbose JSON which is supported pre SP1, you won't be able to use the SharePoint List Connector blackbox as this does not allow you to customise any request headers..

My solution was to use the web connector to send your own SharePoint REST API call. Authentication remains the same if you were using kerberos.

URL: https://SITENAME/_api/web/lists/GetByTitle('YOUR_LIST_TITLE')/items?$top=5000

Header: ACCEPT application/json;odata=verbose

You should get data from the list but you'll get lots of metadat too, so you can just transform the data pick the columns you require.