The Power Query SDK is the development kit for Power Query, the Microsoft query engine found in Excel and Power BI. It lets you create customized connectors for data sources. Find out what it's for and how to use it!
To take full advantage of data and the valuable information it contains, professionals today need a dynamic and powerful approach to manipulating and transforming data.
This is precisely what the Power Query SDK offers, enabling you to create connectors for data sources on well-known Microsoft solutions such as Power BI and Excel.
Whether you’re an experienced data analyst looking to optimize ETL processes, or a beginner looking to get started in this field, discover all the possibilities!
What is the Power Query SDK?
The initials SDK stand for “Software Development Kit”. In this case, it’s a software toolbox developed by Microsoft to extend the functionalities of Power Query.
This query engine, natively integrated into applications such as Power BI, Excel and Azure Data Factory, offers a user-friendly graphical interface for data preparation and transformation.
However, the SDK goes beyond this graphical interface by enabling developers to extend and customize Power Query‘s functionalities. It therefore offers greater flexibility to meet the specific needs of each data integration project.
Whether for automating recurring tasks, creating advanced data transformations or integrating customized data sources, this tool provides a dynamic playground for professionals.
With this set of tools, you can create your own Power Query connectors. These are often referred to as custom connectors or Power Query extensions.
These connectors can be used to create new data sources, or to customize and extend an existing one.
What is the purpose of the Power Query SDK?
The main use cases for the Power Query SDK include the creation of a view accessible to business analysts for a REST API.
The aim may also be to provide branding for a source that Power Query supports with an existing connector, or to implement an OAuth v2 authentication flow for a SaaS offering.
It can also mean exposing a filtered or limited view on your data source to improve usability, or enabling DirectQuery for a data source using an ODBC driver.
By connecting Power Query to data sources, it becomes possible to use the visual interface or scripting language to extract, transform and load data using queries.
Several key features make this SDK a preferred choice, including the ability to create custom connectors for integrating context-specific data sources.
These include the ability to apply filters to extract specific data or sort it according to defined criteria, as well as to join data from different sources.
It also enables the creation of complex transformations through advanced scripting, for exceptional adaptability to different data transformation scenarios.
Error handling and debugging also facilitate the development process, enabling potential problems to be quickly identified and resolved.
In this way, the SDK transcends the limitations of Power Query and delivers a broader palette of tools. Let’s now take a look at the prerequisites and the various stages of its use…
Installation and configuration: the quick and easy guide
Before starting the installation process, it’s imperative to ensure that the environment is correctly configured.
Technical prerequisites may vary depending on the specific application you’re using, such as Power BI or Excel, but there are some common elements that require particular attention.
First of all, make sure you have a compatible IDE installed, such as Visual Studio. Also check that your version of the host application is compatible with the version of the kit you intend to use.
Regular updates are crucial, not only to benefit from the latest features, but also for security patches.
Your environment must also have the necessary permissions to access the data sources you plan to integrate. This may include databases, APIs, or other systems.
If all these prerequisites are checked, the installation process can begin. First of all, visit the official Microsoft website or use the package management tools to download the latest version of the SDK.
There are currently two versions of the Power Query SDK: Visual Studio Power Query SDK launched in 2017 as an extension to Visual Studio 2017 and 2019, and Visual Studio Code Power Query SDK (Preview) launched in 2022 and recommended for creating Power Query connectors.
Microsoft is now encouraging developers to install and use this new version, which will soon become the default SDK.
However, if you would like to install the Visual Studio Power Query SDK, start by installing it from the Visual Studio Marketplace. Create a new data connector project, and define your logical connector. Build the project to produce an extension file.
To install the new Visual Studio Code Power Query SDK, available since September 2022, go to the Visual Studio Code section of the Visual Studio Marketplace. Select “Install” to initiate the process.
In addition, certain configuration parameters may be required to align the SDK with your development environment. These include authentication, authorizations and data source connections.
After installation, the crucial step is to configure the SDK according to the specific needs of the project. Connections to data sources must be configured using available connectors, or by creating custom connectors if necessary.
Also define the appropriate security settings to protect sensitive data. And if your project requires advanced scripting, configure the parameters related to customization to meet the requirements!
How do I create a Power Query connector?
Both SDK versions use a similar process for developing a high-level connector. When creating a new project or workspace, the same main files are used.
These are the connector definition file (<connectorName>.pq), a query file (<connectorName>.query.pq), a resource file (resources.resx) and PNG files of various sizes used to create icons.
The SDK provides basic query execution capabilities, enabling you to test your extension without having to switch to Power BI Desktop. In addition to the extension file, a query file (name.query.pq) can be used to run tests within Visual Studio.
Query evaluation automatically includes the extension code, without the need to save the .pqx file. It is therefore possible to call or test any function shared in the extension code.
The query file can contain a single expression, a let expression like the one Power Query generates, or a section document.
Building the project produces a ZIP file with a .mez extension. At runtime, Power BI Desktop loads extensions from the [Documents]\Microsoft Power BI Desktop\Custom Connectors folder.
The Power Query SDK in the Microsoft ecosystem
Power Query connectors created with the SDK can be used not only on Power BI Desktop, but also throughout the Power Query Online experience, and in particular with Power BI dataflows and datamarts.
On Power BI Desktop, simply copy the .mez or .pqx file extension to [Documents]/Power BI Desktop/Custom Connectors.
However, you need to go to the settings, File > Options > Security > Data extensions and authorize the loading of any extension without validation. A restart of Power BI is required, and the extensions will be loaded immediately.
If you are the owner of the data source and connector, you can also submit your connector to the certification program so that it is delivered with Power BI Desktop with every update. On Power Query Online, only certified connectors are shown.
This SDK extends the data transformation capabilities of Power BI, and also meets the specific needs of Excel data integration. It thus offers consistency between the various Microsoft tools.
What’s more, it can be used in Azure Data Factory to create complex data pipelines, facilitating large-scale transfer and transformation. During the integration process, it also helps automate ETL tasks.
It can also be used in conjunction with SQL Server for more robust data integration scenarios, and with many other analysis tools for data preparation…
Conclusion: a Power Query SDK to create your own connectors
By allowing you to customize your data connectors, this SDK extends the possibilities offered by Power Query and provides an unprecedented degree of flexibility.
To learn how to handle Power Query and all its subtleties, you can choose DataScientest. Our various online training courses will enable you to acquire real expertise!
Our Power BI curriculum covers Power Query, the M and DAX languages, Data Visualization and advanced concepts such as Dataflow and incremental updating.
The two parts of this training course can be completed in just five days full-time or 30 days part-time, and lead to the state-recognized “Analyzing Data with Microsoft Power BI” certification.
You will also be prepared to take the Microsoft PL-300 exam, included in the training, to acquire the official Power BI Data Analyst Associate certification.
We also offer a Microsoft Excel training program that lets you study at your own pace, with unlimited access to our learning platform for 3 months.
The program covers Power Query, pivot tables, task automation via macros, as well as conditional formatting and function libraries.
At the end of the course, you’ll be fully proficient in Excel and able to take the TOSA RS5252 exam, which is highly recognized on the job market.
Our Data Analyst, Data Management and Digital Marketing & Data training courses also include Power Query and its SDK.
These courses, offered as BootCamp, continuing education or sandwich courses, give you all the skills you need to start working in Data Science.
All our courses are offered online, and our organization is eligible for funding options. Don’t waste another moment, and discover DataScientest!
