#PowerBI – External tool to connect Excel to the current PBIX file

In the July update of the Power BI Desktop we now can add external tools to the ribbon.

If you install the latest versions of Tabular Editor, DAX Studio and the ALM Toolkit these will be added as tools in the ribbon.

But you can also build and add your own tools.

David Eldersveld (link) has written an excellent series of blogposts about using Python as an external tool – link to part one – and this inspired me to give it a go as well.

The official documentation can be found here.

Short description of what an external tool really is

An external tool will point to an exe file and you can supply the call to the exe file with arguments including a reference to the %server% and %database%.

The information about the external tool needs to be stored in

C:\Program Files (x86)\Common Files\Microsoft Shared\Power BI Desktop\External Tools

And name the file “<tool name>.pbitool.json”.

This will give me these buttons in my Power BI Desktop

My idea to an external tool

When I build models – I use Excel pivot tables to test and validate my measures and typically I would use DAX Studio to find the localhost port to setup a connection to the currently open PBIX file.

So, I thought it be nice just to click a button in PowerBI Desktop to open a new Excel workbook with a connection to the current model. That would save me a couple of clicks.

If I could create an ODC file when clicking on the button in Power BI and then open the ODC file (Excel is the default application to open these) my idea would work.

I have previously used Rui Romano’s (link) excellent PowerBI powershell tools – link to github and link his blogpost about analyse in Excel – so why not use PowerShell to do this.

Here is a guide to build your own version

Step 1 Create a powershell script

I created a powershell file called ConnectToExcel.ps1 and saved the file in local folder C:\Temp – you can save this where you want it stored. (Link to sample files last in this post)

The script is a modified version of Rui’s function Export-PBIDesktopODCConnection – thank you so much these.

Function
ET-PBIDesktopODCConnection

{

# modified the https://github.com/DevScope/powerbi-powershell-modules/blob/master/Modules/PowerBIPS.Tools/PowerBIPS.Tools.psm1

# the Function Export-PBIDesktopODCConnection

    [CmdletBinding()]

param

(

[Parameter(Mandatory =
$false)]

        [string]

$port,

[Parameter(Mandatory =
$false)]

        [string]

$path

)

$port = $port

$odcXml= “<html xmlns:o=””urn:schemas-microsoft-com:office:office””xmlns=””http://www.w3.org/TR/REC-html40″”><head><meta http-equiv=Content-Type content=””text/x-ms-odc; charset=utf-8″”><meta name=ProgId content=ODC.Cube><meta name=SourceType content=OLEDB><meta name=Catalog content=164af183-2454-4f45-964a-c200f51bcd59><meta name=Table content=Model><title>PBIDesktop Model</title><xml id=docprops><o:DocumentProperties xmlns:o=””urn:schemas-microsoft-com:office:office”” xmlns=””http://www.w3.org/TR/REC-html40″”&gt; <o:Name>PBIDesktop Model</o:Name> </o:DocumentProperties></xml><xml id=msodc><odc:OfficeDataConnection xmlns:odc=””urn:schemas-microsoft-com:office:odc”” xmlns=””http://www.w3.org/TR/REC-html40″”&gt; <odc:Connection odc:Type=””OLEDB””>

<odc:ConnectionString>Provider=MSOLAP;Integrated Security=ClaimsToken;Data Source=$port;MDX Compatibility= 1; MDX Missing Member Mode= Error; Safety Options= 2; Update Isolation Level= 2; Locale Identifier= 1033</odc:ConnectionString>

<odc:CommandType>Cube</odc:CommandType> <odc:CommandText>Model</odc:CommandText> </odc:Connection> </odc:OfficeDataConnection></xml></head></html>”

#the location of the odc file to be opened

$odcFile = $path\excelconnector.odc”

$odcXml|Out-File $odcFile -Force

# Create an Object Excel.Application using Com interface

$objExcel=New-Object -ComObject Excel.Application

# Make Excel visible

$objExcel.Visible = $true

# Open the Excel file and save it in $WorkBook

$WorkBook = $objExcel.Workbooks.Open($odcFile)

}

write $args[0]

ET-PBIDesktopODCConnection -port $args[0] -path “C:\Temp”

The script contains a function that creates an ODC file where the Datasource and path of the ODC file is determined by to arguments in the function – port and path, The Script also opens Excel and then opens the file.

The scripts contain a

$args[0]

This will in the end be the value localhost:xxxxx that will be provided when we click the External tool button in Power BI Desktop – and will make more sense after step 2

Notice that I have hardcoded the path where the ODC file will be stored to C:\Temp.

Step 2 Create a .pbitool.json file

The pbitool.json file is relatively simply

Name is the text that will appear in the ribbon.

Description is the tooltip that appears in Power BI Desktop according to the documentation – but it doesn’t work at the moment.

Path is the reference to the exe file you want to activate – and only the exe file.

Arguments is the arguments that you want to pass the exe file – and here we have the to built in references %server% and %database%. Arguments are optional so we could just start Excel or any other program if we wanted .

IconData is the icon that you want to appear in the ribbon – I found an icon via google and then used https://www.base64-image.de/ to convert it to the string.

In this tool we use the Powershell.exe file that can be called with arguments where we specify the script file that we want to be executed and we pass the extra arguments server and database as well – in my script I only use the %server% reference which will give me the server name and portnumber of the local instance.

It means that when the button is clicked in PowerBI Desktop it will execute

C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe C:\temp\connetToExcel.ps1 localhost:xxxxx databasename

The localhost:xxxxxx can is the first argument provided and the value can then be referred to by using $args[0].

The file must then be stored in C:\Program Files (x86)\Common Files\Microsoft Shared\Power BI Desktop\External Tools and in my case I called it OpenInExcel.pbitool.json.

Depending on your privileges on your computer you might be warned that you need administrative rights to save files in that location.

And if you save the script file elsewhere you need to modify the pbitool.json file.

Step 3 – Test it

Now we are ready to restart Power BI Desktop – and

And it does appear

Next – open a pbix file

This will open a Windows PowerShell window and write the server information

And in the background opens Excel and the ODC file – which results in a pivotable connected to the local instance.

With a connection to the localhost:52510

The files

You can download the files needed from here – https://github.com/donsvensen/erikspbiexcelconnector

Feedback

I think the use of PowerShell opens a lot of interesting scenarios for external tools and I look forward to see what other external tools that appear in the community.

Please let me know what you think and if you find it useful.

27 thoughts on “#PowerBI – External tool to connect Excel to the current PBIX file

  1. Pingback: Third-Party Tools to Ease Power BI Development and Increase Analyst Productivity – Olivier Travers

  2. Pingback: External Tools: Document your Power BI Model – Data – Marc

  3. Pingback: Connecting Excel to Power BI as an External Tool – Curated SQL

  4. For some reason, when I run the tool as is (with ps1 file in the Temp directory), Excel will not open. However, I have noticed that the json file has successfully saved the odc file to Temp.

    What could be the problem?

      1. I just ran it in Powershell ISE, it brings up the Analysis Services Connection prompt. I entered in “localhost: xxxx” for the server, select the database (only 1 in list), but nothing happens. Excel does not open. If I try to open the odc file saved to Temp manually it will open properly, but the script itself does not open it.

        No modifications to source files.

      2. The connection prompt you refer to is that when Excel is opened ? And just to be sure you need to specify the actual port number where the active and open pbix file has the tabular instance

      3. I should note, I noticed it does create a Microsoft Excel process when viewing the task manager, but no open window. I tried commenting out the “visible” line in the script but it seemed to make no difference.

      4. Sorry forgot to refresh; didn’t see your latest message. The connection prompt opens by itself and appears to be an excel process. To be clear I put the correct local host number (not XXXX). Not sure what you mean by the open pbix file has the “tabular instance”. How can I confirm it has this?

      5. The port number is correct. I can also see it’s passing it correctly as well when run from PBI (same as your screenshots). However, after selecting the database in the prompt (when running through ISE), nothing happens still.

        I feel that all the correct connection settings are passed to the odc file, but the script simply fails to open the odc file by itself for some reason.

      6. Yes I am; I mentioned this in my first reply. Not sure what the issue is (I’m not familiar with PowerShell scripts).

      7. I added the following line to the beginning of the script, but there was no change:
        Set-ExecutionPolicy -ExecutionPolicy Unrestricted -Scope CurrentUser

        But I’m unsure which Execution Policy or Scope may be required to allow the script to open Excel properly?

      8. Unfortunately, the Business Ops Tool from PBI Tips only installs the tool to external tools ribbon. It does not appear to change its operation or code to enable an “elevated Powershell” state.

        Since I seem to be unable to modify the code properly to add elevated permissions, I suppose I have to resign to not using it. Thanks for your help anyway.

  5. just FYI – I couldn’t get the Ribbon to allow me to select the icon until I followed the instructions from the blog post about Python:

    As long as the “enhanced metadata format” for the data model is enabled, and the JSON in your file is accurate, you should see your new tool in your ribbon after you re-open Power BI Desktop.

    These were in the second post, not the one linked below, but at the bottom of the page, Step 2 for Python tutorial

  6. Just used this concept to add a powershell GUI ps1 to launch from External Tools with the ps1 hiding the console. Works great!

  7. Pingback: One Tool to Install them ALL - Power BI Tips and Tricks

  8. Pingback: Connect your #PowerBI desktop model to #Tableau Desktop via External Tools in PowerBI – Erik Svensen – Blog about Power BI, Power Apps, Power Query

  9. Pingback: #Power BI – External Tool – Open Power BI Report Builder – part 1 – Erik Svensen – Blog about Power BI, Power Apps, Power Query

  10. Pingback: Herramientas externas en Power BI Desktop - dataXbi

  11. Pingback: Power BI – Desktop adding External Tools | audministrator

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s