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.
