Implement Native Query support in your Power Query Custom Connectors
We’ve just released support for implementing native query support in your own Power Query custom connectors through the use of the Power Query SDK.
This new support allows Power Query connector developers to implement support in their connectors to enter a native query that can run against their data sources through the use of the Value.NativeQuery function and allow optional features such as Query Folding over the native query executed.
This has been a top request by connector developers for a long time and its finally here. You can implement the Native Query support for your ODBC based custom connectors and enable a dialog similar to the one from the picture below where the user will be able to input a native query that can be passed to your driver.
What is the Power Query SDK?
The Power Query SDK is a set of tools designed to help you create Power Query connectors. These connectors are often referred to as custom connectors or Power Query extensions.
The Power Query SDK is a Visual Studio Code extension that can be downloaded from the marketplace. If you haven’t tried it yet, we highly encourage you to give it a try and follow our quick introductory guide to find out more about the SDK.
What does Native Query support mean?
Connectors in Power Query allow you to connect to a given data source. In particular contexts, some data sources allow you to write a syntax in their native data source query that can be passed to the data source to execute a specific request for data or action. For example, in the sample that we provide for adding Native Query Support that uses the SQL Server ODBC Driver, the native query used is SQL. For your connector, this could be a different language depending on what the drivers need are but, above all, these languages fall under the concept of a native query in Power Query.
A few of the most common cases where you’ll want to pass a native query rely on not having the ability to create views or consume views directly from Power Query, so as an alternative you are able to pass your own native query to let the data source provide the data in the shape that you need.
How can I implement Native Query support in my connector?
We’ve written a step-by-step walkthrough article to help you understand how to implement the Native Query support in your own connector. Click here to go to the article.
Get involved and submit your feedback
We highly encourage you to check out the Power Query SDK and submit your feedback for any features related to it. We are continuously working towards improving the experience for connector developers and we’ve enabled a new discussion section solely dedicated to the Power Query SDK in our GitHub repo.