top of page
Search

Using Visual Studio Code with Power BI

You can do much more with TOM than simply print a list of tables or update measures. This article is just the first of a series of posts I plan to write describing some of the more useful things you might like to try, such as:

  • Bulk copying measures from one Power BI report to another

  • Auto creating “Sum of”, “Max of” measures for every numeric column in your model

  • Migrating AAS models to Power BI Premium

  • Customizing Power BI Desktop by registering your C# console app as an external tool to the toolbar

I’m not suggesting or recommending this approach should be your primary method of working with Power BI models. We have wonderful tools already – but it is always good to know you can quickly connect to your models to complete some tasks that aren’t so easy in existing tools.

Not all features of TOM work with Power BI desktop. You can browse the entire model, but only add particular objects such as measures. Currently, you cannot add a table to Power BI Desktop – although this will work if you connect to a model hosted in Power BI Premium.

Working with the TOM like this is a great way to improve your understanding of the AS database engine and will also improve your experience when using some of the advanced features of Tabular Editor.

I came across with a this nice article and tried it myself and it worked successfully.

The exercise

The following step by step exercise will be to download and configure VS Code to the point where it can connect to a Power BI Desktop file and add a new measure. The exercise will be on a clean VM.

Step 1 : Download Visual Studio Code

Open a browser and navigate to code.visualstudio.com then download and run the installer for the current version for Windows. Use default settings when prompted during the install.


Step 2 : Install .Net Core SDK

On a browser navigate to the download page for the .Net Core SDK. Note this is not the same as the .Net Core runtime (which gets installed by the SDK) – it needs to be the SDK. I use version 3.1.301, so any version that matches this or later is fine.

Download and install the SDK executable using default settings.

Once the SDK has installed, you can check by opening VS Code and running the following command at a terminal:

dotnet --info

To open a new terminal window in VS Code, click the Terminal menu item from the top – then select New Terminal from the drop-down menu. This action will open an area at the bottom of the VS Code screen where you can type commands such as dotnet –info


The dotnet –info command should display a list of text in the terminal window, and we are looking to see that the .Net Core SDK install was successful as per the following image (you may need to scroll up):


Step 3 : Create a C# console app

At the terminal window, type the following three commands to create a brand new folder for our project.

md PBI-Tool

cd PBI-Tool

dotnet new console

The first command creates a new empty folder (md = make directory) for the exercise. The second command moves into the new folder (cd = change directory), while the final command runs some scripts to download a template for a c# console app in the new folder.

Browsing this new folder from File Explorer should show the following :


Step 4 : Add TOM client libraries

Now we have a sample application; we can add the .Net Core TOM client libraries by typing the following command into a terminal window.

dotnet add package Microsoft.AnalysisServices.NetCore.retail.amd64 --version 19.4.0.2-Preview

This command downloads and installs the relevant DLL files needed to connect with Analysis Services databases.

Step 5 : Open the project

VS Code works with folders, so to open the newly created .Net project, we need to click the Open Folder button in the top right, and then navigate to the PBI-Tools folder created using the md command at step 3.

If you are unsure where the folder is, you can derive this from the prompt in the terminal window. In my case, the folder is c:\Users\seamark\PBI-Tool.


Once VS Code has opened the folder, you can open the Program.cs file shown at step 1. If you see a prompt to install a C# extension in the lower right-hand corner, click install, including any additional dialogs asking


And that’s it. We are all set up and ready to start writing code!

Step 6 : Write some TOM code

First off, we will create a small script that will connect to a local instance of Power BI Desktop and display a list of all the table names.

Paste the following code into the VS Code editor over the top of all the existing code. The port number shown in line 11 will need to be updated.

You can use any of these techniques to find the current port number of your Power BI Desktop file. Note this will change around and will not match the number shown in my example.

using System;

using Microsoft.AnalysisServices.Tabular;

 namespace PBI_Tool

{

    class Program

    {

        static void Main(string[] args)

        {

            Server server = new Server();

            server.Connect("localhost:50654");

            Model model =  server.Databases[0].Model;

            foreach(Table table in model.Tables)

            {

                Console.WriteLine($"Table : {table.Name}");

            }

        }

    }

}

When you are ready to run, click the Start Debugging menu option from the Run menu, or press F5. If you get prompted for a debugging environment, chose .Net Core.


The code should connect to Power BI Desktop and iterate through every table in the model and write the name of each table out to the console window.


Step 7 : Bonus Code

As a bonus step, here is an example of how you can add a measure to the model we just connected in the previous step. This script connects to the same model as previously done, and then looks for a table called “Sales” at line 14 (you may need to change this to match the name of a table in your model).

Then the code checks to see if there is already a measure called “VS Code Measure”. If it can’t find one, it creates a new measure and adds it to Power BI Desktop. If you re-run the script a second time, it will update the contents of the measure to a different DAX expression.

using System;

using Microsoft.AnalysisServices.Tabular;

namespace PBI_Tool

{

    class Program

    {

        static void Main(string[] args)

        {

            Server server = new Server();

            server.Connect("localhost:50654");

            Model model = server.Databases[0].Model;

            Table table = model.Tables["Sales"];

            if (table.Measures.ContainsName("VS Code Measure"))

            {

                Measure measure = table.Measures["VS Code Measure"];

                measure.Expression = "\"Hello Again World\"";

            }

            else

            {

                Measure measure = new Measure()

                {

                    Name = "VS Code Measure",

                    Expression = "\"Hello World\""

                };

                table.Measures.Add(measure);

            }

            model.SaveChanges();

        }

    }

}

Press F5 to run and voila! Jumping over to Power BI Desktop should show a new measure in the Sales table which shows the text “Hello World” when added to the canvas. Run the report a second time will update the expression to now show “Hello Again World”.


Quickfire Summary of steps

  1. Install VS Code

  2. https://code.visualstudio.com

  3. Install .Net Core SDK

  4. https://dotnet.microsoft.com/download/dotnet-core

  5. Create new console app in new folder

  6. dotnet new console

  7. Add TOM client libraries

  8. dotnet add package Microsoft.AnalysisServices.NetCore.retail.amd64 –version 19.4.0.2-Preview

  9. Write your code

  10. using Microsoft.AnalysisServices.Tabular;

  11. Server server = new Server();

  12. server.connect(<your AS DB here>) ;

Article reference and tried and tested from : https://dax.tips/2020/07/09/using-visual-studio-code-with-power-bi/

15 views0 comments

Recent Posts

See All
bottom of page