I need the filters to be applied SERVER SIDE in a SharePoint Online environment.  Lists have well over 100,000 rows and over 100 columns.  I only want a subset imported.

the listdata.svc absolutely allows filters, etc.   But when I select odata as an external source in Excel all I get is the option to select a list -- not a filtered list. When I look at the connection string I can see the following:

1. Connection Type is DATAFEED
2. Source is /_vti_bin/listdata.svc    and this is definitely a REST interface because I can test it on the URL line.
3. Command Type = TableCollection
4. CommandText has a bunch of lists

Searching on line it says that the contents of the CommandText depends on the Connection Type and CommandType.  But I can't find any documentation on how to format/modify the CommandText <yuck>.

Question 1:  Is it possible to use an odata connection to sharepoint and include standard odata filter statements?

Question 2:  How do I modify the command text to get a filtered set of columns and rows for a particular list?

HELP!!!  Someone in Microsoft wrote this code -- it should be possible to get an answer to this question!!!

Chris

Hi Savin,

You want to find a result about modify the command text to get a filtered list from Sharepoint to Excel by using OData.

Please refer to this article, it talks about the standard OData system query options. And based on your requirement, $filter can get your result (It returns the subset of rows that satisfy the conditions specified in the value of $filter).

https://msdn.microsoft.com/en-us/library/office/jj163211.aspx?f=255&MSPPError=-2147217396

If you need more information about functions with $filter please refer to this article:

http://docs.oasis-open.org/odata/odata/v4.0/errata02/os/complete/part2-url-conventions/odata-v4.0-errata02-os-part2-url-conventions-complete.html#_Toc406398094

Please Note: Since the web site is not hosted by Microsoft, the link may change without notice. Microsoft does not guarantee the accuracy of this information.

Hope it's helpful.

Regards,

Emi Zhang

Hi Savin,

You want to find a result about modify the command text to get a filtered list from Sharepoint to Excel by using OData.

Please refer to this article, it talks about the standard OData system query options. And based on your requirement, $filter can get your result (It returns the subset of rows that satisfy the conditions specified in the value of $filter).

https://msdn.microsoft.com/en-us/library/office/jj163211.aspx?f=255&MSPPError=-2147217396

If you need more information about functions with $filter please refer to this article:

http://docs.oasis-open.org/odata/odata/v4.0/errata02/os/complete/part2-url-conventions/odata-v4.0-errata02-os-part2-url-conventions-complete.html#_Toc406398094

Please Note: Since the web site is not hosted by Microsoft, the link may change without notice. Microsoft does not guarantee the accuracy of this information.

Hope it's helpful.

Regards,

Emi Zhang
TechNet Community Su

Emi,

Thank you for the information.  My issue is different.  I understand how to use the odata interface using either the ExcelRest.aspx to extract data FROM an Excel table.  I also understand how to use the listdata.svc to extract data from a SharePoint List.

My problem is in using the odata data connection service that is part of Excel to CONNECT to a SharePoint List and get data.  Excel builds a connection string to the list.   But it does not seem to allow any other odata filter statements.  The connection string does have an area called Command Text and this is where you would typically place any additional odata statements.  But I'm unable to guess what the format of such statements would be or even if the connection string allows for any additional statements.  Using the Excel interface it puts a list of all the SharePoint lists that the user selected in the Command text surrounded by quotes and separated by commas.  Since its an odata interface you would think that we could modify the text with statements like $top=100.  But I can find no documentation and so far I have been unable to guess a format that works.

By the way, I would be equally happy to use PowerQuery if it had a way to do server side filters.  Its probably using an odata connection as well -- but I have the same problem.

Any ideas?  It would be a shame to have to write my own VBA code to use the odata interface (listdata.svc) to perform the query and add it to a the PowerPivot data model.

Hi Savin,

I consult your issue to other staffs but we have no idea about it, this is a not a break-fix issue, but a how to issue.

I suggest that you may select Microsoft Advisory Services option. Microsoft Advisory Services provides short-term advice and guidance for problems not covered by Problem Resolution Service as well as requests for consultative assistance for design, development and deployment issues. For specific information about the types of Advisory Services available, visit the http://support.microsoft.com/gp/advisoryservice web page.

Hope it's helpful.

Regards,

Emi Zhang
TechNet Community Su

Emi,

I would be happy with a simple yes or no answer for now.  There are only three options as I see it to each question:  (first question is about ODATA interface and second question, which is identical, is about PowerQuery interface just in case the answer is different.

1. The Excel odata connection interface to consume data from an SharePoint Online list was designed so that odata filters could be added to the connection string. POSSIBLE
2. The Excel odata connection interface to consume data from an SharePoint Online list was designed so that only a SharePoint List could be targeted but no additional standard odata filters and verbs could be added. NOT SUPPORTED in CURRENT DESIGN
3. The programmer that designed that portion of the code was abducted by aliens before he could document what was actually coded.  Abducted by Aliens.

There is only a point to investigated further the "How To" once we know its even possible.

Any chance that someone could ask the right person if its even supported?   I'm in contact with two authors of Power Query Excel books and both of them have no idea to the answer and both think we should really be able to get an answer.

By the way, I actually spent an hour on the Phone with a Microsoft Power Query/BI expert who knew a lot of stuff about Power Query and its interface to everything EXCEPT the interface to a SharePoint list as a data source.

HELP!!!