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″”> <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″”> <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.
Erik Svensen - Blog about Power BI, Power Apps, Power Query 
Pingback: Third-Party Tools to Ease Power BI Development and Increase Analyst Productivity – Olivier Travers
I wrote a fairly extensive entry on external tools that you might find interesting:
https://www.oliviertravers.com/power-bi-third-party-tools-development-addons/
Pingback: External Tools: Document your Power BI Model – Data – Marc
Pingback: Connecting Excel to Power BI as an External Tool – Curated SQL
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?
Hi Curtis, Have you tried to run the script via PowerShell ISE to see if it throws an error message ?
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.
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
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.
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?
The prompt is from Excel so that is a good sign 🙂 – the correct port number can you check using DAX studio or https://www.google.dk/amp/s/www.biinsight.com/four-different-ways-to-find-your-power-bi-desktop-local-port-number/%3famp
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.
Are you able to open the ODC file manually?
Yes I am; I mentioned this in my first reply. Not sure what the issue is (I’m not familiar with PowerShell scripts).
Hi curtis, you might check whether Steves comment about the execution policy could help you
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?
Hi Curtis – it requires elevated PowerShell Permissions – you might wanna try out PowerBI tips – https://powerbi.tips/product/business-ops-beta/
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.
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
Just used this concept to add a powershell GUI ps1 to launch from External Tools with the ps1 hiding the console. Works great!
Nice work Erik. Just good to note a PowerShell ExecutionPolicy must be defined for the script to run properly and Excel to launch. https://go.microsoft.com/fwlink/?linkid=135170
Pingback: One Tool to Install them ALL - Power BI Tips and Tricks
Pingback: Connect your #PowerBI desktop model to #Tableau Desktop via External Tools in PowerBI – Erik Svensen – Blog about Power BI, Power Apps, Power Query
Pingback: #Power BI – External Tool – Open Power BI Report Builder – part 1 – Erik Svensen – Blog about Power BI, Power Apps, Power Query
Pingback: Herramientas externas en Power BI Desktop - dataXbi
Pingback: Power BI – Desktop adding External Tools | audministrator
Pingback: Support of external tools in Power BI Desktop is GA - Power Community
Pingback: Support of external tools in Power BI Desktop is GA - Power Community