We loaded our sharepoint-hosted “barstow” table into a fresh excel worksheet, having re-enabled the data source and content (from now untrustworthy sources, for some reason).
Our next task to move away from the traditional excel world used so far and onto the world of power query, where we get all the power of websso, access control, and sharing.
So, we used the signin feature of the power query addon itself – logging into our office365 “netmagic” environment using websso and oauth2. We used user “firstname.lastname@example.org”. These credentials, sessions, and tokens are stored on our computer, recall (and not in the workbook).
Next, we used the power query featureset and loaded-up a query “from table” in the current workbook – this being the resultset and now “table” of RETS data obtained from the ODBC connection.
We loaded the result of executing the ‘power query’ into a second worksheet. And then we shared the query, using the office 365 power BI catalog:
We see that in a new excel workbook (logically operating on a different computer to that used by email@example.com), we can use the search tool, for ‘organizational” queries, shared within the netmagic community.
we can load the preview data into the query itself (vs a workbook, that will attempt to talk to the true data source with all the data)
Of course, if one executes this query from a workbook that does not already contain a sheet with the (local) table referenced by the query, one gets no data:
This all nicely shows that the access control is on the queries and their sharing, not on the data (which is assumed to be available). One is a realtor, say, sharing one’s intelligence and data analysis skills and results (with others).