Earlier this month, Microsoft announced a public preview of the new Power Query SDK! This is an exciting advancement for those engaged in the world of custom connector development! The initial goal is for the new SDK to achieve feature parity with the long since abandoned legacy SDK, then to keep going by adding additional features to the new SDK that enhance connector development.
Microsoft has provided several online resources related to the new SDK:
- The official public preview announcement shares more details on the project and Microsoft’s plans for it.
- Visual Studio Marketplace is the place to go to install the SDK.
- On GitHub, you can log feature requests and issues, as well as ask questions related to custom connector development.
I thought it would be interesting to highlight a few of the feature additions and enhancements present in the current preview version (version 0.1.7).
Aside: Is the Power Query SDK relevant to you? The SDK (Software Development Kit) is geared specifically toward the niche of custom connector development, so is not particularly relevant to general business users who use Power Query solely to ingest data into Microsoft Power BI or Excel.
Big Changes
A couple of the big differences:
Host Framework/Tool
Old SDK: Visual Studio-based.
New: SDK VSCode-based.
Microsoft Visual Studio is a great tool. However, it is a heavy install. Many of the powerful features it offers are not relevant to Power Query. In contrast, setting up Microsoft Visual Studio Code (VSCode) is lightweight. Additionally, VSCode brings with it an ecosystem of extensibility, lending to the likelihood that various entities will contribute chunks of complementary functionality that augment what the SDK provides.
Automatability
Old SDK: Interactive, GUI based.
New SDK: Automatable. In a project, what you can do from the user interface, you can also do from the command-line.
The legacy SDK tends to limit you to using it interactively, with your keyboard and mouse. The new SDK can be utilized from the UI and from the command line. A suite of command-line utilities are provided which enable the SDK’s functionality to be used outside the UI, and which can be used to access advanced functionality not exposed in the UI.
To aid in understanding what the UI is doing, when an activity in the UI trigger an action, the corresponding command-line command is echoed out to either the Terminal or Output pane.
The command line-ability of the new SDK opens up significant possibilities in the realm of automation, such as CI/CD. For example, in your connector project’s source code repo, you could set up a commit trigger that uses the new SDK’s utilities to build the extension then automatically run the extension’s unit tests!
Smaller Changes
A few of the smaller differences:
Multiple Test Query Files
Old SDK: Only one .query.pq test query allowed in a project.
New SDK: Many .query.pq files are just fine.
Being limited to a single test query file impedes organization. You may find it desirable to group sets of related tests into separate files. Then there’s your ad hoc scratch test code, where you experiment with arbitrary expressions that use your connector, which seems like it should be in a file of its own.
However, with the old SDK, all your test-related code had to be contained into a single test .query.pq file. This limitation is no longer present in the new SDK! In it, you can create as many test files as you’d like.
Test With Multiple Extensions
Old SDK: The test query file is evaluated against one custom extension; that’s it.
New SDK: Multiple custom extensions can be in scope during a test file evaluation.
Why might you want your test file to run against more than one custom extension? How about to keep your test framework code separate from your test file!
With the legacy SDK, everything relevant to testing with your test file must be in that file. This includes the functionality defining your unit testing framework, such as its assert and report results methods. The net effect is that your test file likely ends up containing a combination of your tests and the test framework—components that really should be stored and version controlled separately.
With the new SDK, if you package the unit test framework as its own extension, you can include that extension in your test runs when you trigger those runs from the command line. This way, your connector test file’s contents can be focused just on true tests.
(Aside: Microsoft is considering including a test framework as a built-in component of the new SDK.)
Inject Environment Variables
Old SDK: Not available (to my knowledge).
New SDK: Yes (though could change).
Imagine that automation has been set up…say your connector’s test M code is being run by a CI/CD process. That process might want some way to dynamically pass configuration values into your M logic. To facilitate this, it could be attractive to have a means for the outside world to set some form of environment variables/configuration that can be read by the M code that’s being evaluated.
With the new SDK, this is possible. Key-value pairs can be passed in when “run-test” is used from the command line via argument --environmentConfiguration/-ec.
Inside the test PQ file, a specific environment configuration value can be read out with Environment.FeatureSwitch(name as text, optional default as any) as any or the entire environment’s contents can be accessed using Environment.Configuration() as record.
Test Connector
Old SDK: Had to test by installing extension on a gateway.
New SDK: Simply use Run TestConnection function.
Data gateways use a connector’s “test connection” functionality as part of checking credentials. The legacy SDK does not expose a way to test this test functionality, requiring that the custom connector be installed on a gateway in order to validate. The new SDK makes testing “test connection” as simple as clicking on the appropriate option in the sidebar!
There you have it…select highlights of the new Power Query SDK’s features and improvements. I am looking forward to watching this tool as it continues to mature!