Multivalue parameters in #PowerBI paginated report when using #PowerAutomate to export to file

I have had a couple of people posting comment on one of previous blogposts “Setup data driven report subscriptions for #PowerBI paginated reports with #PowerAutomate” asking me on how to specify values for multi value parameters in a Paginated reports.

So, I decided to write a blog post on how you can do it – if you have solved in other ways than my solution please let me know in the comments

How to do it

In my example I have created a simple report that has one multi value parameter that is called “StoreChain” and the parameter label is Chain.

And in the Power BI Report Builder the Report parameter is setup to Allow multiple values

Now let’s create the flow

I will create a flow where the user can type in the values for the Chains that the report should be filtered by – So I choose to create an instant cloud using the trigger “Manually trigger a flow”

I add an Text input called Chain and add a description to the input field

When we then select the Action “Export To File for Paginated Reports” – we can select the workspace and report we want to run

At the bottom of the action dialog we have the ParameterValues name and value section

But if we specify the values by referring to the trigger input – we will get a string value containing all the chains separated by ; (semicolon)

For example running with this

Will give us

And because there is no chain called this we will get an error when we try to test the flow

Solution

We need to specify the parameter values a little bit different

If you click on the button “Switch to input entire array” we can specify the Parameter values as an array instead

And this reveals that in order to specify more than one chain we need to specify an array of values containing a column called name and value – like this.

This means that before we call the action to export the paginated report we need to build an array of the specified chains (parameter values).

First I create an array variable called pv containing an empty array

Next, I use an “Apply to each” where I use the split function to create an array of the text specified in the trigger.

And inside the Apply to each step I build the parameter value array by using the action “Append to array variable” where I construct an item in the pv array for each of the result of the split function

Then I can use the pv Array variable to Export To File for Paginated Reports – ParameterValues setting

And then to finish the flow we can add a “Send an email (V2)” action to send the report

The final flow will have the following steps.

Next – let us test it

With this entered as text for the input

The steps will specify the ParameterValues as an array

And after about 35 seconds the flow has emailed me the requested reports

Success 🙂

Hope you find this useful.

Using Calculation Groups in #PowerBI to implement a Many 2 Many (M2M) filter

Yesterday I tweeted about how I solved the implementation of a Many 2 Many filter using Calculation groups.

And was encouraged to write a blog post about this – so here it is 😊.

The scenario

Imagine a simple star schema where we have a fact table with sales and a customer and Date dimension.

And a in the demo I have also created a few measures – (in my actual customer case I had over 100 measures)

Now we want to expand the model to handle that a customer can belong to one or more customer groups –

This is done by adding the table Customer Groups that contains the Customer Groups and then a bridge table that contains the link between the customers to the different customers groups.

For instance, Customer Key 12031 both belongs to Customer Group 1 and 2.

But when we try to report on the Sales Value the value for the groups will be identical

This is because row context comes from the table Customer Groups and because the filter from that table can’t be passed to the customer table as the cross filter direction points the other way (red circle)

We could change the cross filter direction to Both via the Edit relationship dialog

And it would solve the problem.

But as Alberto Ferrari highlights in this blog post – Bidirectional relationships and ambiguity in DAX – SQLBI – this can be dangerous.

Change all the measures

For all our measures to work I would have to update them all and add a CROSSFILTER statement each of the measure statement

This will give me the result I want but also some work to update all measures.

Use Calculation groups to add filter to all your measures

Instead let’s create a Calculation Group to apply the filter – and the tool to do this is the Tabular Editor – Tabular Editor

We will add a Calculation Group via Tables

And add a group called “Customer Group Filter”

Next step is to add a Calculation Item

I will call it “Apply Customer Group Filter”

And then in the expression editor I will add the expression that applies the CROSSFILTER to the SELECTEDMEASURE().

Clicking Save and returning to Power BI Desktop will prompt me to refresh the calculation groups

Now I will have a Calculation Group Filter in my Fields list

And we can add a slicer or a filter to the visual, page or all pages

And if we select the Calculation Item we can see the filter is being applied.

You can download the demo file – here

Summary

Typically, the calculation groups examples are normally focused on Time intelligence but absolutely not limited to time – I hope this example can give you some inspiration to use Calculation Groups in other scenarios as well.

In my actual case I saved time on updating over 100 measures – so I really ❤ calculation groups.

Let me know what you think and if you find it useful as well.

#PowerBI – Change the data source in your composite model with direct query to AS/ Power BI Dataset

I have been playing around with the new awesome (preview) feature in the December Power BI Desktop release where we can use DirectQuery for Power BI datasets and Azure Analysis services (link to blogpost)

In my case I combined data from a Power BI dataset, Azure Analysis Services, and a local Excel sheet. The DirectQuery sources was in a test environment.

I then wanted to try this on the actual production datasets and wanted to change the datasources – and was a bit lost on how to do that but luckily found a way that I want to share with you.

Change the source

First you click on Data source settings under Transform data

This will open the dialog for Data source settings and show you the list of Data sources in the current file.

Now you can either right click the data source you want to change

Or click the button “Change Source…”

Depending on your data source different dialogs will appear

This one for my Azure Analysis Services Connection

And this one for Power BI Dataset

And this one for the Local Excel workbook

Hope this can help you to.

Happy new year to you all.

You must know about this shortcut key in #PowerBI Desktop

Working with the field list on a large model in Power BI Desktop can quickly make you end up with a lot of expanded tables and you collapsing them one by one.

Don’t do that

Even though that is good if you want to improve your chances of beating your kids in Fortnite – it probably won’t – so instead do one of the following

If you want to use your mouse

Click the show/hide pane in the header of the Fields panel

This will collapse all expanded tables in the field list at once – plus if you have used the search field – it will clear that as well.

But you want to do it using the keyboard use

ALT + SHIFT + 1

This will collapse all the expanded tables as well.

Here is a link to the documentation about short cut keys in Power BI desktop – run through them – there might be some that can save you a click or two

Keyboard shortcuts in Power BI Desktop – Power BI | Microsoft Docs

Use hidden measures and members from #PowerBI dataset in an Excel Pivot table

When you connect to a Power BI Dataset from Power BI desktop you might have noticed that you can see and use hidden measures and columns in the dataset.

But the hidden fields cannot be seen if you browse the dataset in Excel.

But that does not mean that you cannot use the fields in Excel – and here is how you can do it.

Using VBA

You can use VBA by creating a macro

The code will add the field AddressLine1 from the DImReseller dimension as a Rowfield if the active cell contains a pivotable.

Sub AddField()
    Dim pv As PivotTable
        Set pv = ActiveCell.PivotTable
        pv.CubeFields("[DimReseller].[AddressLine1]").Orientation = xlRowField
End Sub

If you want to add a measure/value to the pivotable you need to set change the Orientation property to xlDataFields

This means that we now have added two hidden fields from the dataset

Add hidden measures using OLAP Tools

You can also add hidden measures using the OLAP Tools and MDX Calculated Measure

Simply create a new calculated measure by referencing the hidden measure in the MDX

This will add a calculated Measure to the measure group you selected

And you can add that to your pivotable

Referencing hidden items using CUBE functions

Notice that you can also reference the hidden measures using CUBE functions

Simply specify the name of the measure as the member expression in this case as “[Measures].[Sales Profit]”

You can also refer to members from hidden fields using the CUBEMEMBER functions

Hope this can help you too.

Power On!

Spot the difference between Power BI Desktop and Power BI Desktop (Store Version) #PowerBI

On my computer I have 2 versions of Power BI Desktop installed – one from the Microsoft Store which is updated automatically and the downloaded version from downloads – and typically I have last month edition as my downloaded version.

But in my taskbar its impossible to tell the difference between the two.

Well we can solve that by changing the icon for the downloaded version – its not possible for the store version.

If you right click the icon in the taskbar and then right click the Power BI Desktop

You can select the properties for this App.

Now click the Change Icon

This will show you the current icon and now you can change this by clicking Browse – in my case I will select the icon for the PBIDocument

And click open – then icon will now be set to this

And when clicking OK

We will see the icon has changed for the Shortcut.

Notice that it will change immediately

But after a restart it will appear

Hope this can make your choice of Power BI Desktop versions easier for you as well.

Setup data driven report subscriptions for #PowerBI paginated reports with #PowerAutomate

This weekend I had the pleasure of presenting at SQL Saturday Copenhagen #963 with a session with the title “Power BI Paginated Reports – How to and why”.

I was asked if it was possible to create datadriven report subscriptions in paginated reports and I said “Not yet” – it turns out that is not exactly true.

With the new action “Export to File for Paginated Reports (preview)” – in Power Automate – link

It is possible !!!

And yes, for now we need the reports to be stored in a workspace backed by premium capacity but soon it will be available here

How to set it up

In my workspace I have a simple report with 2 parameters – Year and Country

In order to get a report delivered to multiple recipients with different parameters I created an Excel Workbook in Onedrive for business with 3 different reports I want sent all with different parameters for County and Year.

You can store the data in other sources that you can connect Power Automate with – but for the example I just used Excel file.

Next is to design the flow in Power Automate

In the example I choose the trigger “Manually trigger a flow” – but you can of course choose other triggers to activate this flow – for instance a Recurrence so you can execute the reports at a specified frequency.

Next step is to get the rows from the Excel file – So I use the List rows present in a table

This action will return the three rows and next is to use the Control “Apply to each” to loop through each row

For the output we choose the value from the “List Row…” step

Then we add the action “Export To File for Paginated Reports

This action will run a selected report from a specific workspace

We can choose between the different file formats that is possible for paginated reports – in this example I chose PDF.

Add the end of the Action window – we can specify the values for each of the parameters.

OBS – You have to type in the Parameter name manually and be aware that the name of the parameter doesn’t necessarily match the label shown

As in this case – the Year parameter is called “DateCalendarYear” – and that is the name you must use for “ParametersValues name”

We bind the value of the parameter to the value from the Excel file using the Dynamic content selector.

Next step is to add a step to send the report and bind the fields for the To field – add a subject and in the attachments section we give the attachment a name and then use the result of the “Export to File for paginated reports” as the attachment content

The flow is now ready for testing – Save you flow and hit the test button.

Accept the used connections and hit Run Flow and we can go to the flow run overview and follow the steps.

Hopefully you will see that all steps are successfully.

And we can check our mail box to see the report delivered

The recipients of the e-mail can be sent to any e-mail address.

Can we pay by the hour ?

In my case I used an Power BI Embedded A4 SKU as capacity – this can be turned on and off so you can expand the flow with actions that can start the capacity and run the subscriptions and then turn the capacity off again.

You only pay for the time the capacity is on – and the list price per hour is $8.

Hope this can help you and really looking forward to all exciting stuff that is coming around Power BI paginated reports.

#PowerBI – External Tool – Open Power BI Report Builder – part 1

As you may have noticed I have blogged twice about the new awesome feature in Power BI Desktop where we can build our own external tool buttons in Power BI Desktop.

Here is a link to the previous posts.

  • Analyze in Excel (link)
  • Open in Tableau (link)

One perhaps forgotten member in Power BI is the Power BI Report Builder – aka – Paginated reports – and even though it requires a premium capacity or Power BI embedded A-SKU to publish/share reports – I thought it might be interesting to see if we could link the local pbix file to the Report Builder.

I will write a part 2 where the external tool will support if the desktop file is connected to an Azure Analysis Server or Power BI Dataset as well.

You can download the free Power BI Report Builder from here.

How to build the external tool

The tool has two files

  • A powershell file – ConnectToPowerBIReportBuilder.ps1
    Must be stored in C:\temp
  • A external tool file – OpenInPowerBIReportBuilder.pbitool.json
    Must be stored in C:\Program Files (x86)\Common Files\Microsoft Shared\Power BI Desktop\External Tools

OBS – in order to run a powershell script on your pc you need to have to set the execution policy – https://go.microsoft.com/fwlink/?linkid=135170

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

The powershell

The report builder uses the rdl – file format which is an xml file.

So the PowerShell script is simply to create an rdl file and make the connection dynamic using the parameters about server and database name that the External tool provides as arguments when the button is clicked in Power BI Desktop.

This is a small part of the xml that creates the rdl file and it could be expanded to create datasets and insert visualizations.

        $tdsXml = "<?xml version=""1.0""?>
<Report MustUnderstand=""df"" xmlns=""http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition"" xmlns:rd=""http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"" xmlns:df=""http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition/defaultfontfamily"">
  <rd:ReportUnitType>Mm</rd:ReportUnitType>
  <rd:ReportID>cd1b0079-99d9-4e99-9f5e-6fda21f9f09d</rd:ReportID>
  <df:DefaultFontFamily>Segoe UI</df:DefaultFontFamily>
  <AutoRefresh>0</AutoRefresh>
  <DataSources>
    <DataSource Name=""PowerBI"">
      <rd:SecurityType>Integrated</rd:SecurityType>
      <ConnectionProperties>
        <DataProvider>OLEDB-MD</DataProvider>
        <ConnectString>Data Source=$port;Initial Catalog=$database</ConnectString>
        <IntegratedSecurity>true</IntegratedSecurity>
      </ConnectionProperties>
      <rd:DataSourceID>8c230b7c-799c-4007-af55-6bc26eca797c</rd:DataSourceID>
    </DataSource>
  </DataSources>
  <ReportSections>
    <ReportSection>

What happens

After you have placed the OpenInPowerBIReportBuilder.pbitool.json in the correct folder and you have restarted Power BI Desktop

the toolbar button

You will see a new button in the ribbon.

When you have a pbix file open containing a datamodel and click the button a desktoptobuilder.rdl file will be generated in C:\temp

And Power BI Report Builder will open – in the xml I have also inserted text in the report header showing the local host port.

In the data Sources you will find

A data source called PowerBI which is connect to your running instance of tabular model

And then we can create a dataset that uses the data source

And use the designer to create the DAX query

And use this to in a chart

Obs – remember to save the file with another name – and to update the connection information after you close and reopen the desktop file.

In my next post I will demonstrate how we can do the same if the pbix is connected to Azure Analysis Services or a Power BI Dataset.

Stay tuned 🙂

PS – This one is for #paginatedreportbear

Paginated Report Bear (@PaginatedBear) | Twitter

Connect your #PowerBI desktop model to #Tableau Desktop via External Tools in PowerBI

I recently created an external tool to PowerBI desktop that connects your Power BI desktop model to Excel (https://eriksvensen.wordpress.com/2020/07/27/powerbi-external-tool-to-connect-excel-to-the-current-pbix-file/) and then I thought – could we also have a need for an external tool that could open the desktop model in Tableau desktop.

So, I downloaded a trial version of the Tableau Desktop to see what is possible.

And sure, enough Tableau can connect to Microsoft Analysis Services and therefor also the localhost port that Power BI Desktop uses.

We can also save a data source as a local data source file in Tableau

Which gives us a file with a tds extension (Tableau Data Source)

When opening the file in Notepad we can see the connection string and some extra data about metadata-records.

It turns out that the tds file does not need all the meta data record information – so I cleaned the tds file to contain

Opening this file from the explorer will open a new Tableau Desktop file with the connection to the specified model/database/server.

The external tool

Knowing this I could create an external tool the same way as my Excel connector.

First create a PowerShell

OBS – in order to run a powershell script on your pc you need to have to set the execution policy – https://go.microsoft.com/fwlink/?linkid=135170

The PowerShell script

Function ET-TableauDesktopODCConnection
{  

	[CmdletBinding()]
    param
    (
        [Parameter(Mandatory = $false)]        
		[string]
        $port,
        [Parameter(Mandatory = $false)]        
		[string]
        $database,
        [Parameter(Mandatory = $false)]        
		[string]
        $path	
    )
    
        $tdsXml = "<?xml version='1.0' encoding='utf-8' ?>
<datasource formatted-name='LocalPowerBIDesktopFile' inline='true' source-platform='win' version='18.1' xmlns:user='http://www.tableausoftware.com/xml/user'>
  <document-format-change-manifest>
    <_.fcp.SchemaViewerObjectModel.true...SchemaViewerObjectModel />
  </document-format-change-manifest>
  <connection authentication='sspi' class='msolap' convert-to-extract-prompted='no' dbname='$database' filename='' server='$port' tablename='Model'>
</connection>
</datasource>"   
                
        #the location of the odc file to be opened
        $tdsFile = "$path\tableauconnector.tds"

        $tdsXml | Out-File $tdsFile -Force	

        Invoke-Item $tdsFile

}

ET-TableauDesktopODCConnection -port $args[0] -database $args[1] -path "C:\temp"

The script simply creates a tableauconnectort.tds file and stores it in C:\temp – and the xml content in the file is dynamically referenced as arg(0) and arg(1) when the external tool is called from Power BI Desktop.

Save the script in C:\temp and call it ConnectToTableau.ps1.

The OpenInTableau.pbitool.json file

Next step was to create a pbitool.json file and store it in C:\Program Files (x86)\Common Files\Microsoft Shared\Power BI Desktop\External Tools

{
  "version": "1.0",
  "name": "Open In Tableau",
  "description": "Open connection to desktop model in Tableau ",
  "path": "C:/Windows/System32/WindowsPowerShell/v1.0/powershell.exe",
  "arguments": "C:/temp/ConnectToTableau.ps1 \"%server%\" \"%database%\"",
  "iconData": "data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAJAAAACQCAYAAADnRuK4AAAABmJLR0QA/wD/AP+gvaeTAAADRklEQVR4nO3dv27TUBiH4WPEitSRS+iCurO0GzdRiS5sXRhAXZhYEAxd2LoUiZtgaxb2iqWXwFiJCzgsqPRPrMb5Jc1x/TxbqgSi5O2xE3+uSwGAUeo2/QRac3R8cla6bvfqB7XOPr19s7e5Z9S2J5t+AoybgIgIiIiAiAiIiICICIiIgIgIiIiAiEziUMbR8cnZovetXbfTlbJ1dbuUy67W80UfP7XDHk83/QQexPVjW/fd9e7trSGPnxqbMCICItLEJqyeljrv593BivbRap0tfNdwH2hVDj58mfuanH5819R+axMBrduQHdvb80BdredT2zEewiaMiICICIiIgIgIiIiAiAiIiICICIiIgIhM4lDGEA5bDGMFIiIgIgIiIiAiAiISTbf1TRK2ZmWTjQvomyRszaomG61ARAREREBEBEREQESaOMdo7eeFjdBYzguzAhEREBHjHP/8fv/i3i8An3/+1dTmowVWICICIiIgIgIiIiAiAiIiICICIiIgIgIiIiAiSx8Lc3Xjcdk/nJ2VWv+/X103+/51dy/9d61ARAREpIlxjilPHvZpbfKwjxWIiICICIiIgIgIiEgTn8KGWmQAfiz/79gH9a1ARG7UP5arG29qBVqHZAXaP5ydDbj7Tqn16v0qXXdZSln4/eo77HFzE+bqxuNy/djW8MdulVLi98smjIiAiNzchI3w6saT1nULv18l3AfqfQrLPnCT80B2ooczD0STRvlF4jp+a/11juVYgYgIiIiAiAiIiICINPEp7Of29txPQC8vLib7qefZq29zX5M/P1439ZpYgYgIiMjSmzCnMY/LKg5bzGMFIiIgIgIiIiAiAiIiICICIiIgIgIiIiAiAiLSxDhHCwzML8cKRERARKJlu2+SsDUPOdnYN0nYmlVNNlqBiAiIiICICIiIgIg08eWZ88Lucl4YkyAgIgIiIiAiAiJinOOWdf0108fKCkREQEQERERARAREREBEBEREQEQERERARCZxKGPw1Y1v3R7y+Kkd9mgioLVPHjZwdeOhWps87GMTRkRARJrYhK1dA1c3fqxGsZ19SOaBhrEJIyIgIgIiIiAiAiIiICICIiIgIgIiIiAAAAAYjb8VJdQbiRXyOAAAAABJRU5ErkJggg=="
}

Test it

Now restart your Power BI desktop and the external tool should be visible in the ribbon

Then open a pbix file with a model and hit the button.

A PowerShell screen will shortly be visible and then Tableau opens the tds file and now we have a new tableau book with a connection to active power bi desktop datamodel.

And we can start to do visualizations that are not yet supported in Power BI –

How can you try it

You can download the files needed from my github repository – link

Feedback

Let me know what you think and if possible share some of the viz that you make.

#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.