Analyse in Excel + Power BI Desktop

Connecting Excel do your Power BI Desktop database has many useful applications, for example you can use Excel to do some quick and advanced testing on the values of your Power BI model.

Power BI Desktop has an Analysis Services Instance behind the scenes that you can indeed connect and build Pivot Tables with Excel, the problem is discovering the TCP port of that instance.

A simple solution would be to install DAXStudio, connect to PBIDesktop and then inspect the port on the bottom of the window:

Which is fine if you do it a couple of times, but if you want to be constantly connecting to Power BI Desktop a better alternative would be to:

  1. Install the PowerShell module “PowerBIPS.Tools” that is available on the PowerShell Gallery
  2. Register a context menu entry that will save on the folder an ODC connection file to every Power BI Desktop file opened

Demo.gif

 

 

 

Advertisements

5 thoughts on “Analyse in Excel + Power BI Desktop

  1. sam says:

    Hi Rui – I installed the PBIPS tools.
    I also downloaded the reg file and merged it with the registry

    However when i have a Power BI Desktop file open and then right click on a folder in the windows explores and click on the Export PBI desktop odc – I get a message that says

    “This file does not have an app associated with it…..Create an association in the default app settings page”

    What am i doing wrong ?

    • Hi Sam, you have Excel installed right? Does the normal “analyse in Excel” from Power BI work? You can also try to do “open with” on the odc file and select Excel app.

      Thanks

      • sam says:

        Yes I do O365 Pro Plus Version 1903 Build 11425.20202 Monthly channel (Targeted)
        The problem is ODC files are not getting generated in the target folder.
        I am having a pbix file open in Power BI desktop

  2. Hi Sam, can you confirm that the PowerBIPS.Tools powershell is installed? Please try to run this cmdlet in a powershell window: “Get-PBIDesktopTCPPort” and see if it returns the PowerBI Desktop windows that are opened.

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