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





Create a Power BI Push DataSet using Power BI Desktop w/ PowerBIPS.Tools

I have already covered how to create an API Enabled (Push DataSet) Power BI Dataset here and here.

The datasets are created using our open-source powershell module PowerBIPS and you need to write some code like the following:

$dataSetSchema = @{name = $datasetName
; tables = @(
    @{name = "Counters"
	; columns = @( 
		    @{ name = "ComputerName"; dataType = "String"; isHidden = "true"  }
		    , @{ name = "TimeStamp"; dataType = "DateTime"  }	
			, @{ name = "CounterSet"; dataType = "String"  }
		    , @{ name = "CounterName"; dataType = "String"  }
		    , @{ name = "CounterValue"; dataType = "Double"  }
	@{name = "Computers"
	; columns = @( 
		    @{ name = "ComputerName"; dataType = "String"  }
		    , @{ name = "Domain"; dataType = "string"  }	
			, @{ name = "Manufacturer"; dataType = "string"  }		        		        			
    ;measures = @(
        @{name="Average CPU"; expression="CALCULATE(AVERAGE('Counters'[CounterValue]) / 100, FILTER('Counters', 'Counters'[CounterSet] = ""Processor(_Total)"" && 'Counters'[CounterName] = ""% Processor Time""))"; formatString="0.00%"}
; relationships = @(
        name = [guid]::NewGuid().ToString()
        ; fromTable = "Computers"
        ; fromColumn = "ComputerName"
        ; toTable = "Counters"
        ; toColumn = "ComputerName"
        ; crossFilteringBehavior = "oneDirection"      


$dataSetSchema = New-PBIDataSet -authToken $authToken -dataSet $dataSetSchema -Verbose	

It’s very straightfoward, but if you need to create complex measures and relationships it’s easy to commit some mistakes if you dont have the help of Power BI Desktop.

To make this task easier we added a new cmdlet to our PowerBIPS.Tools module: Get-PBIDataSetFromPBIDesktop

You just need to create a Power BI model as usual, use “Enter Data” option to create your tables (w/ sample data) and then create the relationships, measures, custom columns, write your DAX w/ intellisense:




And in the end use the Power BI Desktop model to create a Power BI Push DataSet like this:

# Get a PBIDataSet schema from PBIDesktop
Get-PBIDataSetFromPBIDesktop -pbiDesktopWindowName "*WindowName*" -datasetName "PushDataSet"

# Create the REST API dataset on
New-PBIDataSet -dataSet $dataSet -groupId "workspace id"

Let me know your opinion!