cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ClemFandango
Helper I
Helper I

How to export a PowerBI dataset into excel

Good morning all,

 

I am hoping one of you great people can help or point me in the right direction.

 

I have a dataset that consists of 4 queries. One of the queries is called "test" and is a heavily manipulated version of the other 3 queries. How do i go about exporting the final heavily manipulated version of "test" into Excel?

 

All of the methods that I have tried so far, dont appear to export the data as per the final stage of Power Query:-

Using Get Data inside Excel - this imports the queries, but you have to recreate the column order

Analyze in Excel - this is similar as you have to recreate the data from a pivot

 

Ideally I would like to publish the the Dataset in PowerBI Online and have a scheduled refresh. I am hoping that multiple users can access the dataset online and just export the data into Excel?

 

Exporting into Excel doesnt seem as intuitive as excpected. Am i going about this wrong way? Do I need to create a dataflow?

 

Any help greatly appreciated,

 

CF

1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

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)

View solution in original post

2 REPLIES 2
ClemFandango
Helper I
Helper I

Great stuff - thank you for the info

Vijay_A_Verma
Super User
Super User

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)

Helpful resources

Announcements
Vote for T-Shirt Design

Power BI T-Shirt Design Challenge 2023

Vote for your favorite t-shirt design now through March 28.

March 2023 Update3

Power BI March 2023 Update

Find out more about the March 2023 update.

March Events 2023A

March 2023 Events

Find out more about the online and in person events happening in March!

Top Solution Authors
Top Kudoed Authors