I’ve brought this scenario to multiple ‘Ask the Experts’ at both Ignite and the recent Data Insights Summit. We do not have any on premise servers and instead use O365/SharePoint to collect our data by way of Lists. These lists are complicated in that they have multiple lookup columns and are inherently tied to several other lists in our environment. Because of this, Power Query has never been an option for us to query this data. What we’ve done in the past is a sort of ‘hot fix’ where we have a C# application that bulk deletes and bulk updates a SQL Azure Database from an exported Excel sheet from said SPO List. Then in data modeling mode, we query the SQL Azure DB. To me, it seems like there has got to be a better way. One thought I have is to create a ‘Friendly Form’ much like the intelligence forms we have now with lookup columns, autofill options, validation rules, etc and then have a workflow run in the background to copy the list item to a plain list that we can then configure as an External Content Type using Business Connectivity Services to populate a SQL Azure DB as we had before and query that. In this architecture, I suppose I’d also need a workflow that ran if an item was updated in the Friendly List that would then update the simple list configured with BCS. Any thoughts are greatly appreciated!
One a side note, I’m looking forward to April’s PBI Desktop update where SharePoint Online List is an actual data source. They could possibly solve the limitations I’ve experienced thus far.
