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
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 …
Solution :
I created my own solution …
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
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