increase number of columns that can be imported using OData Feed

[rsmccall33]

I connected to an OData source, but am having trouble importing data because the table has too many columns.  

I get an error:

Failed to save modifications to the server. Error Returned: 'OLE DB or ODBC error: [DataSource.Error] OData: Request failed: The remote server returned an error: (414) Request-URI Too Long. (Request-URI Too Long).

After reducing the number of columns I can import the data fine, but that isn't a viable solution in this case.

Is this a limitation of PowerBI or the OData protocol, and is there a way around it?  

[rsmccall33 · Accepted solution]

I found a solution.  The limitation I linked to above is not the problem.  The problem is in fact the URI too long.  

In this particular case there are well over 300 columns and many have very long column names.  When the OData connection is made and data is transfered, the column names are tacked onto the URI.  The max characther length is breached and causes the error.

The way around this is to modify the URL you are using for the OData feed.  

After /_vti_bin/ListData.svc you should add the specific table and the select all query

ex. https://siteurl.com/projectserver/db/_vti_bin/ListData.svc/tablename?$select=*

[rsmccall33]

Are these the limitations?  75 columns?

https://msdn.microsoft.com/en-us/library/dn950053.aspx

[v-shex-msft]

Hi @rsmccall33,

AFAK, OData not contain the length limit, but I think the service side has setting the query length limit.

E.g.

<configuration>
    <system.web>
        <httpRuntime maxQueryStringLength = "5000" ... />

For detailed information, you can refer to below links:

OData Url Length Limitations

httpRuntime Element (ASP.NET Settings Schema)

Regards,

Xiaoxin Sheng