PowerBI-VSCode

A VSCode extension for managing your Power BI tenant using the Power BI REST API and modify Power BI datasets using TMDL (Tabular Model Definition Language) from within VSCode.

Installation

The extensions can be installed directly from within VSCode by searching for this extension (GerhardBrueckl.powerbi-vscode) or downloaded from the official Visual Studio Code extension gallery at PowerBI VSCode and installed manually as VSIX.

Features

• GUI to browse your workspace and run operations like rebind, clone, ...
• Modify Power BI datasets using TMDL - see TMDL
• Supports VSCode and vscode.dev
• Connect to remote tenants where you are invited as a guest user - see Configuration
• Run DAX queries in a notebook against your Power BI Datasets using %dax magic
• Run arbitrary REST API calls in a notebook using %api magic - see Notebooks
• Drag & Drop to run certain commands (e.g. drop a report on a dataset to rebind it)
• GUI to manage Capacities, Gatways and Pipelines

Configuration

The extension supports the following VSCode settings:

Notebooks

You can open a new Power BI notebook via the UI from the header of each treeview or by running the command Open new PowerBI Notebook (command PowerBI.openNewNotebook). Power BI notebooks have the file extension .pbinb and will automatically open in the notebook editor.

There are two core features of notebooks:

• running arbitrary REST API calls
• executing DAX queries against a dataset
• set variables to be used in subsequent cells

For proper visualization of the results I highly recommend to also install the Data Table Renderers extension!

Run REST API calls

To run a REST API call from the notebook you can simply write the following:

METHOD endpoint
{JSON-Body}

For example to create a new dashboard in My Workspace you can run the following command:

POST /dashboards
{
  "name": "SalesMarketing"
}

The JSON-body can also be omitted, e.g. for a GET request. Supported METHODs are GET, POST, PUT, PATCH and DELETE. the endpoint can either be absolute (e.g. https://api.powerbi.com/v1.0/myorg/dashboards), relative to the root of the API (e.g. /dashboards) or relative to the path set via notebook variables API_ROOT_PATH (e.g. ./refreshes) (see Using Variables below)

Execute DAX queries

To run a DAX query from within the notebook, you have to use the cell magic %dax in the very first line of your cell. The following lines will contain the actual DAX query:

%dax
EVALUATE MyTable

For this to work, you first need to set the variable DATASET in your notebook before - see Using Variables below.

Using variables

You can also define and use variables within the notebook. To set a variable you can use

%cmd
MY_VARIABLE = my_value

There are some special variables that must be set in combination with %dax magic to identify the dataset. The main variable that needs to be set is the DATASET (aliases are also DATASET_PATH or API_ROOT_PATH) to identify the dataset to which the DAX query is sent. the value has to be an API path pointing to the dataset:

%cmd
SET DATASET = /groups/d1f70e51-1234-1234-8e4c-55f35f9fa758/datasets/028d20ca-7777-8888-9999-7a253c7bb6b3

Current values of variables can be retrieved by running SET MY_VARIABLE.

Note: you can also set/get multiple variables within the same notebook cell!

TMDL

Using this extension you can modify your Power BI Datasets directly within VSCode using Tabular Model Definition Language (TMDL). For datasets that reside in a Premium Capacity, you can select Edit TMDL from the context menu: The TMDL definition files will be added to your current workspace and you can modify them from there. When you are done with your changes, you will find a [Validate TMDL] and a [Publish TMDL] button at the right top of your editor.

Prerequisites

The following prerequisites have to be fulfilled to use all TMDL features:

• ASP.NET Core Runtime 7.0.10 (or higher)
• make sure that the VSCode Setting powerbi.TMDL.enabled is set to true (=default)
• the dataset must reside in a Premium Capacity
• optionally you can also use VSCode to manage the authentication as it already happens for the rest of this extension. This has the advantage, that you will not be prompted every time you open VSCode. To use VSCode authenticaiton, make sure that the VSCode Setting powerbi.TMDL.clientId is set to either 058487e5-bde7-4aba-a5dc-2f9ac58cb668 or a custom AAD Application configured as described below:
  • Redirect URIs for Mobile and desktop applications: https://vscode.dev/redirect
  • Delegated Permissions: Workspace.Read.All, Dataset.Read.All, Dataset.ReadWrite.All
  • Consent granted for the permissions above
• technically you can also use the same ClientID also for powerbi.clientId. In that case please make sure that you also provide additional permissions to e.g. read reports (Report.Read.All), etc.

TMDL Configuration

The setting powerbi.TMDL.enabled can be ued to enable or disable all TMDL features.

The setting powerbi.TMDL.clientId can be used to specify a custom ClientID/ApplicationID (details see above) of an Azure Active Directory Application that will be used when communicating via with the XMLA interface. By doing so, VSCode will manage the authentication for you and prompt for your credentials. Those credentials will then be remembered and stored in VSCode and you will not be prompted again every time you open VSCode.

Using TMDL Editor

The TMDL Editor loads the TMDL definitions directly from a server and exposes them via the custom URI scheme tmdl:/. This allows you to add as many models as you want to your workspace to quickly access them. This is the very same that happens if you click Edit TMDL in the context menu of a Power BI dataset - it will add the folder tmdl:/powerbi/<workspace>/<dataset> to your VSCode workspace and load th model. Alternatively, you can also omit the /<dataset>. In this case, a list of all available databases(=datasets) within the workspace tmdl:/powerbi/<workspace> will be shown and you can drill into the datasets from there.

To reload the latest definition from the server again, you can right-click the folder of the dataset in the VSCode File Explorer and select [(Re)Load TMDL from Server] (see screenshot above).

Integration with other TMDL Tools

TMDL is a standard defined by Microsoft which also means that different tools can be used to work with the TMDL files. To use all the TMDL features of this extension with files authored by other tools (e.g. Tabular Editor), you need to configure the target where the TMDL files should be deployed to. This can be done by creating a file called .publishsettings.json in the root of the TMDL folder structure (on the same level as model.tmdl). This file has to have the following content:

{
  "connectionString": "Data Source=powerbi://api.powerbi.com/v1.0/myorg/SomeWorkspace;Initial Catalog=SomeDataset;"
}

The value for connectionString can be obtained from the settings pane of the dataset in the Power BI service where you want to deploy to. If the dataset does not yet exist (e.g. if you have modified the exported connectionString in the file), the dataset will be created for you. This can be very useful to copy datasets between workspaces without down-/uploading .pbix files or if you have modified the dataset via the XMLA endpoint with an other tool (e.g. Tabular Editor) before.

TMDL Proxy

This extensions leverages existing libraries for TMDL which are available as .net Core package: Microsoft.AnalysisServices.NetCore.retail.amd64. However, as this package cannot be directly integrated into VSCode which uses JavaScript/TypeScript (and not .net), we implemented a .net Core application/api that acts as a proxy between VSCode and the target Tabular Server (e.g. Power BI dataset). This allows our VSCode extension to communicate with the target from within VSCode without having to implement the library in JavaScript/TypeScript.

As soon as you start using TMDL features, this proxy process will be started automatically in the background within a VSCode terminal:

The terminal also serves as a log which shows all individual requests sent to the proxy.

Building Locally

1. Make sure you have installed NodeJS on your development workstation
2. Clone this repo to your development workstation, then open the cloned folder in VSCode
3. Install Visual Studio Code Extension Manager by running npm install @vscode/vsce -g --force
4. To install all dependencies, switch to the terminal and run npm install
5. To run the extension in debug mode (for using break-points, etc.), press F5
6. To generate the .vsix, switch to the terminal and run vsce package

VSCode Extension Development Details

Please refer to the official docs and samples

FAQ

Q: I have a guest account in a remote client, can I still use this extension?

A: Yes! The only thing you need to do is to specify the TenantID of the remote tenant in the setting powerbi.tenantId. I would recommend to create a separate VSCode workspace in this scenario and change the setting there.

Q: I tried to query the Admin-APIs in a notebook but its not working, any ideas why?

A: By default, this is not supported as the VSCode built-in authentication does not have the necessary permissions/scopes to read these APIs (Tenant.Read.All or Tenant.ReadWrite.All). To be able to query the Admin APIs you need to use a custom ClientID (settig powerbi.clientId) and add those permissions to your custom AAD application (also see Prerequisites on how to configure a custom AAD application to be used with this extension).

Q: I tried to run a command from the context menue but the dropdown that appears does not contain the values I want to use. What can I do?

A: Unfortunately VSCode is quite limited in the way how users can enter data (e.g. a dropdown box) and we simply display the last 10 items that the user selected or expanded. So if you e.g. want to do a rebind of a report and the target dataset does not show up, please make sure to select/click it the Workspace Browser just before you run the rebind.

Q: I am using the TMDL features and VSCode keeps prompting me for credentials over and over again. What can I do?

A: This is by design, basically the very same way how also other tools like Tabular Editor or DAX Studio work. This authentication process happens within the TMDL Proxy. However, you can also hand over the authentication to VSCode which would then also store your credentials in the system's key chain. All you need to do is to create a custom AAD application and specify the ClientID/ApplicationID in powerbi.TMDL.clientId (also see Prerequisites).

Q: Something went wrong or the extension is stuck, what can I do?

A: Unfortunately this can happen, sorry! Please try to restart VSCode or run the command PowerBI.initialize from the command menu. If the problem persists, please open an issue at our Git Repository.