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

Temporary Post Used For Theme Detection (5370b9e5-d36e-4a2c-bc2a-c360d9de9d19 – 3bfe001a-32de-4114-a6b4-4005b770f6d7)

This is a temporary post that was not deleted. Please delete this manually. (9073f7e3-480e-4759-a16d-9df35731e0b0 – 3bfe001a-32de-4114-a6b4-4005b770f6d7)

QuickTip – Split a delimited text into a Table in DAX

Today I was challenged with a problem and did not find an easy solution to it online, so I decided to write this quick post on it.

The challenge was to create a measure in Table1 that would sum on Table2 all the Rows whose first character was a match with any of the categories on a text separated by “|”:

2018-08-27_19-30-51

The problem is that the “Categories” column is a text separated by “|” and there’s no built in DAX function to convert a delimited text into a table.

The solution is to use PATH + GENERATESERIES + PATHITEM + GENERATE:

2018-08-27_19-33-49.png

 

Hope it helps.

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:

2018-07-16_1-07-29

2018-07-16_1-10-12

2018-07-16_1-10-59

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 powerbi.com
New-PBIDataSet -dataSet $dataSet -groupId "workspace id"

Let me know your opinion!