How to export a PowerBI dataset into excel

[ClemFandango]

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

[ClemFandango]

Great stuff - thank you for the info

[Vijay_A_Verma · Accepted solution]

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)