The word Big Data is becoming a household name, with millions of data being generated on a daily basis. As a BI analyst, the need to export a large amount of data to other formats is required.
Have you ever wanted to import a data table into Excel from Power BI Desktop? In this blog, I will show you step-by-step how to Export data from Power BI Desktop as an Excel CSV file using the DAX Studio.
DAX Studio
DAX Studio is a tool for creating, running, and analyzing DAX queries in Power BI Designer, Power Pivot for Excel, and Analysis Services Tabular.
It has an Object Browser, integrated tracing, query execution breakdowns, formula and measure editing, syntax highlighting, formatting, and execution.
Export Power BI Table as CSV File
Exporting data to a CSV file in Power BI can be done easily provided the data table is not more than a certain amount of rows.
DEMERIT
- This method works well for small amounts of data. The copy process may take a long time if you need to copy more data than a few thousand rows.
- There is always a risk of missing data when exporting as a CSV file.
- If you noticed from the GIF the number of rows from Power BI Desktop was 999,999 but when exported to Excel it was truncated to 13,233.
Export Data from Power BI Desktop Using DAX Studio
It’s really simple to export data from Power BI Desktop to CSV using DAX Studio. One benefit of using DAX Studio is that it performs admirably no matter how much data you wish to export.
The Following Steps are Required in Exporting CSV File Using DAX Studio:
Step 1: Open both your desired Power BI desktop pbix file and DAX studio.
Note: For confidential issues, some parts of the image will be blurred out.
You will need to open both your desired Power BI Desktop pbix file and DAX Studio at the same time. This will allow for easy connection when you want to export.
Step 2: Data Source in DAX Studio
Connect your DAX studio to the PBI/SSDT model, your pbix file will appear.
Step 3: Export as CSV
- Click on the “Advanced Tab” in your DAX studio.
- Click “Export Data”, this will bring up the Export Data Wizard.
- Select the Data format you want to export, for use we be using the “CSV format”.
- Select the directory you want to save your file(CSV) output.
- CSV Delimiter: We be using the comma-separated value as the common separator for the file.
- Check the box of the data table you want to export as CSV.
- Click Export to export your data.
Note: Exporting the data as CSV will depend on the speed of your personal computer such as the RAM.
Table exported successfully with 99,938,618 rows, the file was also imported successfully in Power Query and loaded only as a connection.
Conclusion
In this tutorial blog, you learned how to export millions of rows of data using the DAX Studio as a CSV file. Other methods of Exporting data can be used but the DAX Studio provides you with a faster and more efficient way of exporting unlimited about of data.
Would recommend you try this out yourself and see the awesome power of DAX Studio.
