Power BI – Export Data Set to CSV file overcome +150000 record limit

January 3, 2020

Let’s say you have a Data Flow to Consolidate multiple Data Sources


And next created a PBI Desktop Data set based on these Data Flows.


Transformed it using a PBI Power Query and Published it in the PBI Service


And put in place a schedule to refresh the data periodically.

To access the data is either using PBI Desktop or use it in Excel (Pivot table)

Downside is that the export data option in PBI Desktop controls are limited to 150000 records ?


Or Analyze in Excel, you will have to download and .ODC file and open it…

But it will not download the complete data set in a tabular layout Sad smile

Alternatively you can use Dax Studio.

But the downside is that you need to have PBI Desktop open and Refresh the Data Set each time.


You can’t schedule a job to download the dataset or a subset using a DAX query.

This is no good solution … Sad smile

Solution :

I created my own solution … Smile

Which is an application to download all the data directly from the PBI Data Set without opening the PBI Desktop Client !

It is Not depending on the PBI Desktop

Including DAX Query options to filter the data.

And does NOT get cut off by the 150000 record limits.

Reuses the same ODC connection.


When first time run it will ask for authentication if it is not run on a prior installed PBI Client.


I have also Command Line version that is scheduled to Export all the data a CSV file, containing +/- 280.000 records


Bonus :

Other benefits are that all of the features of the PBI Services remain available like Power Query in Data Flows


And as well the Scheduling Engine of PBI Services

The data set contains as well all the custom built Measures when downloading !

No need for Power BI Premium Smile

Use cases are that I needed to extract the data to do an ETL upload in a Data Warehouse.

Or Ad-Hoc analysis on the full data set using other tools

Power BI – Report Builder using Multi Value Parameters in DAX

December 20, 2019

How to create a Multi Value Parameter Report in Power BI Report Builder

You can download PBI Report Builder here

This builder looks very familiar to the SSRS Report Builder you are used to from MS Reporting Services

Building a single value parameter in DAX is easy.

But creating a Multi Value Parameter requires quite some steps …


Solution :

These are the steps to follow :

1. Create a Data Set with the SLICER value in PBI Desktop

2. Copy the PBI Query

Run the Performance Analyzer in PBI Desktop


Start Recording and Refresh


Copy the Query to the Main Data Set


And apply the Filters in the next step …

3. Create new Parameter referencing to the Main Data Set Query Filter

To create a Parameter in the RBI Report Builder you first have to create a new Data Set


The same way go to the Performance Analyzer and copy the Query from the corresponding Slicer


And add it to the Filter Data Set and test the DAX.


3. Create a PBI Report Builder Report Parameter


Select the values from the Parameter Data Set created earlier


4. Add a Parameter in the Main Sales Data Set


 Add the @xxx Report Parameter variable in the DAX Query

Run the report and test the Report Parameters


Enjoy !!

Office365 – How to Block Self Service Purchase Apps

December 5, 2019

Microsoft is enabling all O365 user to use self service Apps.

This means everyone on your tenant can start purchasing certain apps by default …

If you want disable this behaviour than you need to use PowerShell.


1. Download and install the PS MSCommerce Module

Install-Module -Name MSCommerce 


2. Next import the module and connect

Import-Module -Name MSCommerce



3. Run these commands to see the status

Get-MSCommercePolicy -PolicyId AllowSelfServicePurchase 

Get-MSCommerceProductPolicies -PolicyId AllowSelfServicePurchase 


4. To disable 1 or all the products run this commands

Update-MSCommerceProductPolicy -PolicyId AllowSelfServicePurchase -ProductId CFQ7TTC0KP0P -Enabled $False  
Update-MSCommerceProductPolicy -PolicyId AllowSelfServicePurchase -ProductId CFQ7TTC0L3PB -Enabled $False  
Update-MSCommerceProductPolicy -PolicyId AllowSelfServicePurchase -ProductId CFQ7TTC0KP0N -Enabled $False 

5. To disable all in Batch run this command

Get-MSCommerceProductPolicies -PolicyId AllowSelfServicePurchase | `

Where { $_.PolicyValue -eq “Enabled”} | `

forEach { 

Update-MSCommerceProductPolicy -PolicyId AllowSelfServicePurchase -ProductId $_.ProductID -Enabled $false  }


You can check the status again :


Enjoy !

Power BI – Accessing SSRS Datasets using a Web Connector

November 10, 2019

Let’s say you have spend a lot of time to build SSRS reports, and you want to re-use these data sets in Power BI.

Well this is possible even if you are running the older version of SSRS.

Even if this report is running in Native or Integrated SharePoint Mode, or is a Parametrized report

The SSRS report server has natively a web service interface, exposing a SOAP and URL Interface.


Open Power BI and GET DATA  from the Web Connector


Fill in your report URL link and add the rs:Format=XML parameter to get an XML output from you report



If all goes well you will get the Report XML output and see the SSRS TABLIX and FIELDS Smile


Enjoy !!

PowerShell Core – Power BI Gateway Management CmdLets

November 6, 2019

Keep in mind that these cmdlets are PowerShell CORE only !

First you need to check the availability of the PS CORE version on your system.

I had  6.1 preview 3 installed


So I opened the Cmd line using Admin privileges

Next started PWSH.exe

Next I run this command :

Install-Module -Name DataGateway


Next Check the available cmdlets

Get-Command -Module DataGateway*


Next I ran this command

Import-Module DataGateway


But it says my version of PS Core needs to be minimum 6.2.2 Sad smile

So first get an upgrade from here :



After upgrading your can check the version using



Next run these commands again :

import-module DataGateway
import-module DataGateway.profile


No errors now Smile

Run this command to login :


It opens the browser and return this message



Next run this command to get your Cluster ID


Next check the Gateway Status like this



So far so good Smile 

Enjoy !!

PowerShell – Accessing Power BI Desktop Data Cube and more…

November 18, 2018

Recently I got interested in getting data out of a Power BI Data Model to push into an SQL database.

On my way I discovered a lot of interesting techniques and Tools for Power BI Desktop.

1. First of all you need to know that Power BI Desktop actually is a small Web Server running an SSAS Mini Cube.

2. Next you need to prepare PowerShell to access the PBI Cube.

Solution :

In order to start you first need to start PBI Desktop and open a PBIX file you created before.

Once started you need to find out on which Random Port is was started.

There are 4 way to get the job done.

1. Install Dax Studio which you need to have, no question about this !

    Even if you are not using it for getting the Random Port…

Look in the bottom right corner :


2. Command line tool NETSTAT

   Run NETSTAT –b command and look for Power BI


In this case it was Port 60520

3. Look for the msmdsrv.port.txt file.

Once PBI is started it will create some folders and files.

You can find those here :

%LocalAppData%\Microsoft\Power BI Desktop\AnalysisServicesWorkspaces\


If you have multiple PBIX files open you will see more Random created subfolders.

Go to the random created folder and next to the Data Subfolder.

Open the msmdsrv.port.txt file to get the Port number.


4. Using the Tabular Editor

But it needs some dependent MS AS AMO Libraries



Now that you know how to get access to the Web Server Port.

PowerShell 32 Bit

You can now test PowerShell connection to the Power BI Desktop you have open.

But you first need a Connection String to be used in PowerShell

FIRST USE the 32 Bit PowerShell if you are using a 32 Bit Office installation.

Later in this post I will explain how to get it running in a 64Bit PS even if you have a 32 Bit Office installed.

I suggest that you use EXCEL for this to copy and paste it Winking smile

Go to Data and choose From Analysis Services.


Follow these steps :





Copy it from here :


You can also get the Catalog GUID from the folder name where you could find the Port number.


Run this PowerShell Script, it uses a DMV Query to get hold of the PBI Cube Properties


$Port = "60520"
$Catalog = "cff5b9a8-2f87-416c-97fd-8eccd5962d51"

$Query = 'Select * from $SYSTEM.DISCOVER_PROPERTIES'

$connectionString = "Provider=MSOLAP;Integrated Security=SSPI;Persist Security Info=True;Data Source=localhost:$Port;Initial Catalog=$Catalog"
$connection = New-Object -TypeName System.Data.OleDb.OleDbConnection

$connection.ConnectionString = $connectionString
$command = $connection.CreateCommand()
$command.CommandText = $query
$adapter = New-Object -TypeName System.Data.OleDb.OleDbDataAdapter $command
$dataset = New-Object -TypeName System.Data.DataSet

$dataset.Tables[0] | Format-Table # export-csv $filename -notypeinformation


To list data from a Table use the EVALUATE command

$Query = 'EVALUATE Tickets'


PowerShell 64 Bit

If you are running a 32 Bit Office, you need to install the OLAP Drivers for 64 bit

(will be done when you install DAX Studio).


Or manually register the MSOLAP.DLL from the Power BI install folder.


Tools :

Power Pivot :

You can connect to a Power BI Desktop model using Power Pivot for example.



Dax Studio :

You can run commands directly against your Power BI Desktop.

Use DAX Commands


Or use a DMV Query to select the data from the PBI Meta Data or the Data Set.

// Select data using DMV queries


// Or select all DATA from the PBI Data Set using this command



For more info on DMV Queries see here

Power BI Helper :


More Tools you can find here

Enjoy !

PowerBI – Number and Currency Formatting

October 31, 2017

After looking for a solution why my Number Formatting did not want to work out…

After creating a new Measure it returned the classing US based formatting.


While I wanted to have the EU formatting in my table.

Power BI Desktop Client Regional Setting :

Even after changing the Power BI Desktop Client settings, it did not take effect Sad smile


Solutions :

Go to Control Panel –> Region –> FORMAT


If this format is not in line with your region it will overrule the Power BI Regional settings ?


So after applying the correct region format settings on OS level, everything appeared as expected Smile

PS : These figures are just demo based Winking smile