Forum Discussion
How to export a PowerBI dataset into excel
Once the results are committed to Power BI,
1. Right click on your table in Power BI Desktop - Copy table - Paste into Excel - Once data is into Excel, save as csv. (Larger the dataset, it will take more time and also you can't paste more than 1million rows of Excel sheet limit)
2. Create a table visualization and drag the required fields there. At the bottom of the table, you will see 3 dots clicking that will give the option to export as csv. But this is limited to only whatever fields you dragged to the table visualization. Hence, if you want to export entire table, you will need to drag all fields.
3. OR You can prepare any visual, publish it to Power BI service and Power BI service offers Export to csv.
Export the data that was used to create a visualization - https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-export-data?tabs=dashboard
4. OR DAX Studio which is free and open source offers the facility to export table data as csv
https://daxstudio.org/documentation/features/export-data/
Note - If you are invoking your PQ from Excel and final result is having less than 1 million rows limit of Excel, then you can get the result into Excel itself and then save as csv from Excel. If you are having more than 1 million rows, say 5 million rows.
- Insert an Index
- Create 5 reference queries
- In first reference query, apply the filter from 1 to 1 million, in second reference query from 100001 to 2 million and so on for all 5 reference queries.
- Close and Load To and choose as Connection only (Don't choose Close and Load otherwise all 5 queries and original query will try to load into same sheet). Now all queries will be loaded as Connection only.
- Once you are back into Excel, right click on a reference query and save to a table. Do it for all 5 reference queries into different sheets.
- Now copy the data into 5 different Excel workbooks.
- Save them as csv
- Use a tool such as Notepad++ to merge all these 5 csv files into. (Don't forget to remove headers from remaining 4 files)
Once the results are committed to Power BI,
1. Right click on your table in Power BI Desktop - Copy table - Paste into Excel - Once data is into Excel, save as csv. (Larger the dataset, it will take more time and also you can't paste more than 1million rows of Excel sheet limit)
2. Create a table visualization and drag the required fields there. At the bottom of the table, you will see 3 dots clicking that will give the option to export as csv. But this is limited to only whatever fields you dragged to the table visualization. Hence, if you want to export entire table, you will need to drag all fields.
3. OR You can prepare any visual, publish it to Power BI service and Power BI service offers Export to csv.
Export the data that was used to create a visualization - https://docs.microsoft.com/en-us/power-bi/visuals/power-bi-visualization-export-data?tabs=dashboard
4. OR DAX Studio which is free and open source offers the facility to export table data as csv
https://daxstudio.org/documentation/features/export-data/
Note - If you are invoking your PQ from Excel and final result is having less than 1 million rows limit of Excel, then you can get the result into Excel itself and then save as csv from Excel. If you are having more than 1 million rows, say 5 million rows.
- Insert an Index
- Create 5 reference queries
- In first reference query, apply the filter from 1 to 1 million, in second reference query from 100001 to 2 million and so on for all 5 reference queries.
- Close and Load To and choose as Connection only (Don't choose Close and Load otherwise all 5 queries and original query will try to load into same sheet). Now all queries will be loaded as Connection only.
- Once you are back into Excel, right click on a reference query and save to a table. Do it for all 5 reference queries into different sheets.
- Now copy the data into 5 different Excel workbooks.
- Save them as csv
- Use a tool such as Notepad++ to merge all these 5 csv files into. (Don't forget to remove headers from remaining 4 files)