Provides functionality related to the development and testing of Custom Connectors for Power Query and Power BI.
- Custom connector template
- Build connector file (.mez)
- Set credentials
- Run test queries
- View query results
- Launch Visual Studio Code
- Select the Extensions view (Ctrl+Shift+X)
- Click the ellipse in the upper right hand corner of the Extensions view, and select Install From VSIX ...
- Close and reopen Visual Studio Code
This article focuses on the experience available for the Power Query SDK found in Visual Studio Code. You can learn more on how to install the Power Query SDK for Visual Studio from the article on Installing the SDK.
Before creating an extension project, it is recommended that you create a new folder where you'll store your extension project. During the creation of a new project, if no folder is selected, the Power Query SDK will help you locate or create a new folder before creating your extension project.
Once in Visual Studio Code, In the main Explorer pane of Visual Studio Code, you'll be able to see a section with the name Power Query SDK. This section will have only one button that reads Create an extension project. Select this button.
This button will open an input field at the top of the Visual Studio Code interface where you can enter the name of your new extension project and hit Enter.
After a few seconds, your Visual Studio Code window should open the main *.pq file for your extension project that contains your connector logic. The Power Query SDK will automatically run some necessary tasks to complete the setup of your workspace and you can check this in the Output console in Visual Studio Code.
The Power Query SDK automatically creates the following set of files:
- A settings.json file that dictates specific settings to work with at your workspace level
- It builds the extension as a .mez file and stores it in a new bin\AnyCPU\Debug folder
- A set of connector icons as png files
- A resources.resx that serves as the main storage for strings to be used in the extension
- A .pq file that holds the main logic of your extension or connector
- A .query.pq file that main purpose is to be used as a way to create test queries that you can later evaluate
- A .proj file that holds information about the extension project
Once a extension project is recognized by the Power Query SDK, the section for the Power Query SDK will change its appearance, and will now display a list of tasks that you can run against your new extension project.
Before you can evaluate any queries of your data connector, it is required to have a credential set.
The Power Query SDK offers multiple tasks through its user interface to allow you to set, list and delete credentials from your extension project.
The Power Query SDK is primarily driven by tasks that can be triggered via multiple entry points. Setting a credential can be done in two ways and this is true for most tasks.
- Through the entry in the Power Query SDK section in the Explorer pane
- Through the Terminal by selecting the "Run Task..." option and selecting the group of tasks for powerquery
When you run this task, Visual Studio Code will guide you through a series of prompts to allow you to set the credential. These series of prompts are predictable and will always consist of the same stages:
- Choose the data source kind
- Choose the connector file
- Choose the authentication method
For the existing extension project, the authentication method available is anonymous and once the authentication is set a message box confirming that a credential has been generated successfully should be shown at the bottom right corner of the window.
Similar to setting a credential, the task to list credentials has two entry points in the same places: Power Query SDK section in the Explorer pane and inside the Terminal menu.
When this task is executed, it showcases the available credentials inside of the output terminal.
Similar to the previous two tasks, the task to list credentials has two entry points in the same places: Power Query SDK section in the Explorer pane and inside the Terminal menu.
This task serves as a way to clear all credentials from your current session when you need to set a new credential to evaluate your queries.
The informational messages for this task are also shown in the output console.
Before you can evaluate any tests queries, a credential must be set. Using the connector that was created in the previous section, you can open the *.query.pq file that serves as your test query file.
For this specific connector where the project name was MyConnector, the code looks as follows:
// Use this file to write queries to test your data connector
let
result = MyConnector.Contents()
in
result
Before evaluating the query, let's change the code to look as follows:
// Use this file to write queries to test your data connector
let
result = MyConnector.Contents("Hello World")
in
result
Make sure to save the file after changing its code.
To evaluate this query you have three options:
- Right select the file that's in use and select the option that reads "Evaluate current power query file"
- Going through the Terminal menu and selecting the "Evaluate current file" task
- Use the native Run & Debug option from Visual Studio Code, select the hyperlink to create a launch.json file and then evaluate the file
After evaluating the query, the results will be displayed in the console at the bottom of the window and in a new panel called the results panel on the right.
The results panel consists of three tabs:
- Output tab: Displays a data preview of the query evaluated. If it's a table it will be displayed as grid.
- Summary: Displays a summary of the Activity that ran the evaluation with statistics around it.
- DataSource: Displays general information about the data source used for the evaluation.
To evaluate a different query you simply need to modify the *.query.pq file, save it and then run the evaluation task again with any of the three methods.
The Power Query SDK does not manage any sort of caching mechanism for the evaluations.
This section is created to help connector developers who have extension projects that were created with the legacy Visual Studio based Power Query SDK.
To follow along, we recommend downloading the connector projects available from our DataConnectors repository for the TripPin sample, specifically the sample 9-TestConnection.
To bring the legacy extension project to the new SDK, follow these steps:
- Navigate to the folder where your extension project is located with Visual Studio Code's "Open folder" option in the File menu
- Setup a workspace using the existing folder and its contents via one of the following two methods:
- The Power Query SDK has a mechanism to recognize the contents of your folder and suggest you to enable the conversion to a Power Query SDK workspace
- You can run the Setup workspace and the Build Task from the terminal menu. These will effectively create the .mez file and the settings.json files needed for the workspace
The addition of the two new folders and files is what transforms the current workspace into a new Power Query SDK workspace.
What the Setup workspace task does is effectively create a settings.json file for your workspace that dictates some variables that will be used for your workspace when it comes to evaluations and general settings.
The build task allows you to create the .mez file for your extension on demand.
TestConnection is a function that enables your connector to be refreshed in the Microsoft Cloud through services such as Power BI. It's a record implemented inside of your connector data source record. You can learn more about the implementation of the TestConnection handler from the sample available for Test connection.
The task to run the TestConnection enables you to test such handler inside the Power Query SDK without having to manually try this handler in the Microsoft Cloud.
To run this task, first set a credential for your connector and then run the task either from the Power Query SDK section in the Explorer or through the list of tasks inside the terminal menu.
The result of such task will be displayed in the output terminal at the bottom of the window.
Install project dependencies:
npm install
Build the project and create the vsix installer (npx vsce package):
npm run vsix
Install the extension from the command line (code --install-extension <pqsdk.vsix>):
npm run code-install
This project welcomes contributions and suggestions. Most contributions require you to agree to a Contributor License Agreement (CLA) declaring that you have the right to, and actually do, grant us the rights to use your contribution. For details, visit https://cla.opensource.microsoft.com.
When you submit a pull request, a CLA bot will automatically determine whether you need to provide a CLA and decorate the PR appropriately (e.g., status check, comment). Simply follow the instructions provided by the bot. You will only need to do this once across all repos using our CLA.
This project has adopted the Microsoft Open Source Code of Conduct. For more information see the Code of Conduct FAQ or contact [email protected] with any additional questions or comments.
This project may contain trademarks or logos for projects, products, or services. Authorized use of Microsoft trademarks or logos is subject to and must follow Microsoft's Trademark & Brand Guidelines. Use of Microsoft trademarks or logos in modified versions of this project must not cause confusion or imply Microsoft sponsorship. Any use of third-party trademarks or logos are subject to those third-party's policies.