Window – Disk Space not correct, how to fix it

January 26, 2020

Some times you can see a strange effect where the Free Disk Space is not correct ?!

As shown below the “D” drive has only 18 Gb free disk space …

image

While when I calculate the occupied disk space by files it was only about 100 Gb, leaving 400 Gb free ?

Even if you use the file explorer you will not see where the missing data storage is located.

SOLUTON :

Go to the relevant Drive and right click.

Choose Properties and go to Shadow Copies / Settings

image

Deselect No Limit and set Use Limit

image

Go and check again the free disk space you will see the freed-up space Smile

Enjoy !


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

image

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

image

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

image

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 ?

image

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.

image

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.

image

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

image

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

image

Bonus :

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

image

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