I need to better understand the communications and query architecture of Excel Power Query and SharePoint Online Lists as a data source. NOTE: Some of the source lists have 100,000+ rows. There are Indexes on the list to allow indexed access to subsets of the rows.
- Based on the Power Query to SharePoint interface it would seem that I'm allowed to select List objects but not particular SharePoint Queries associated with those lists. Is this true?
- I'm am allowed to filter the columns and rows after I have selected the List. Question: are these filters part of the query against the source SharePoint List or are they applied after the list is downloaded into Excel?
- Assuming the row and column filters are part of some sort of ODATA query against the list how can I control the order of the statements to ensure that indexed columns are used to initially find a subset of the data.
- The Row Filter only allows me to add two filters. This is very restrictive. For example, if the column is a date column I may need to exclude nulls and then have an after and before date statement. [Note: when I was not excluding nulls first I was getting errors??]. A solution to this nightmare would be to allow us to pick a list AND then pick a VIEW.
- If the Excel Spreadsheet is opened online then is the data update done server side or client side. In other words, is the excel spreadsheet power query data updated before the excel spreadsheet is opened or after. I'm hoping before so that the update is done once rather than once per user.
NOTE: An alternative is using Odata connections. BUT, that requires me to build to build the query string and I can't find a good example of what the XML would look like. Of course, if the ODATA interface allowed me to pick a VIEW on a SharePoint List that would make things a lot easier.
HELP!!!
Other recent topics
