PowerQuery support

[Brian Lewis]

Power query allows data-driven lists and pivot tables to be sourced from a REST interface. This means that we can use the wide flexibility of options already available in the REST interface, and avoid the need to have an explicit database connection to move EMIS data into Excel. This means they can be accessed efficiently from anywhere

Importantly, these connections are refreshable , so that more structure can be built into these workbooks, for example, as the source of Statistic Handbook or other publication from year to year.

The first version supports:

• selection of a host from a dropdown list
• entry of a rest endpoint
• refreshable list and pivot based on this host/endpoint

The Deflate/Reflate logic used to reduce transfer sizes is supported.

Anonymous access supported at the moment, but the warehouse endpoints are open and so available.

[Brian Lewis]

The PowerQueryTools workbook is in the repo at External Tools/PowerQuery. Please do not commit any changes while playing with it.

‌

How to use:

In the settings page, select a host, and enter a warehouse endpoint. (All query string options are supported)

Go to the pivot and list pages, and Refresh the pivot table and list.

‌

Repeat - select a different host or endpoint, refresh the pivot and list again; they will update according to the new settings.

[Brian Lewis]

@Ghislain Hachey This collects some work lying about since 2021, originally done for KEMIS.

The intention was to allow both the ?xl and ?pq query options on any warehouse rest endpoint.

‌

?xl returns an Excel workbook with the warehouse data as a static Excel list

?pq is intended to return the PowerQuery workbook set up with the current host and selected endpoint.

Some of this code is already there; looking at #1153 is how I saw this, and made we investigate how much of this was still available/worked/could be made to work.

Overall this feature would be a selling point with external consultants I think: access to detailed data sets, refreshable and from anywhere on the internet.

‌

‌

[Brian Lewis]

Afterthought: Also could be useful in development, to quickly test warehouse endpoint options.

‌

[Brian Lewis]

Testing of ?pq endpoints:

‌

First make sure that PowerQueryTools.xlsx is in the assets folder. You can get this by opening ExternalTools\PowerQueryTools.xslm and running the macro Publish. (For production this will be installed from webinstall, same as the Census and UIS Survey workbooks.

Then try in the browser e.g.

‌

https://localhost:44301/api/warehouse/enrol/district?pq

https://localhost:44301/api/warehouse/enrol/school?pq

https://localhost:44301/api/warehouse/enrol/authority?pq

https://localhost:44301/api/warehouse/enrol/schooltype?pq

https://localhost:44301/api/warehouse/enrol/region?pq

https://localhost:44301/api/warehouse/enrol/electoratel?pq

https://localhost:44301/api/warehouse/enrol/electoraten?pq

https://localhost:44301/api/warehouse/enrol/nation?pq

You can change the name of the output file by supplying a value for pq:

https://localhost:44301/api/warehouse/enrol/school?pq=PivotTableBook

You can use all the additional parameters of each endpoint format: e.g.

https://localhost:44301/api/warehouse/enrol/school/ail100?pq

https://localhost:44301/api/warehouse/enrol/school/ail100?pq&ByAge&year=2020

etc

‌

‌

[Brian Lewis]

Note that a difference between PowerQueryTools.xlsx in Assets, and PowerQueryTools.xlsm in ExternalTools is that the Assets version does not allow for the selection of the host from the dropdown. Instead, the dropdown is restricted to a single item, and that is picked up from the current collection when the workbook is configured in response to the ?pq switch.

‌

[Brian Lewis]

in progress - criticality updated following conversation with @ghachey