Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
idategto11
Frequent Visitor

How to export some huge query results in excel

Hello,

I've created a complex query from 7 different sources. (excel files, database connections, folder with csv files)
This query contains a lot of appends, merges, and transformations on all the sources.

The final results I wanted and I have is a table containing approximately 90 000 rows and 25 columns.

I would like to share this data table with others without creating a visual dashboard or a pivot table (too much data to perform a pivot table on my computer).

How can I simply export in excel the results of my query ?

Thanks a lot for your responses

Best regards,

Christophe

1 ACCEPTED SOLUTION

Hello, 

 

If you want to copy the table to an excel, why not trying to apply the filters that you want in the query editor, and then on the data view, copy the table? I've tried copy pasting a table with 116k rows and 58 columns, works.

2017-06-27 14_42_48-.png

View solution in original post

14 REPLIES 14
dkay84_PowerBI
Microsoft Employee
Microsoft Employee

Maybe someone else mentioned this as I didn't read through every post, but while you can easily "copy table" and paste into excel, perhaps consider using an R script as the last step in your query to write the table to CSV if you want to do this on a regular basis. 

idategto11
Frequent Visitor

Hello,

I've created a complex query from 7 different sources. (excel files, database connections, folder with csv files)

This query contains a lot of appends, merges, and transformations on all the sources.

 

The final results I wanted and I have is a table containing approximately 90 000 rows and 25 columns.

 

I would like to share this data table with others without creating a visual dashboard or a pivot table (too much data to perform a pivot table on my computer).

 

How can I simply export in excel the results of my query ?

 

Thanks a lot for your responses

 

Best regards, 

Christophe

Yes you can do this. 

 

From powerbi.com, read my article here. https://exceleratorbi.com.au/extract-tabular-data-power-bi-service-excel/

 

If you want to do it from the desktop, use the same approach and combine it with what you learn at my other article here https://exceleratorbi.com.au/excel-workbook-connection-local-power-bi-desktop/



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

Hello,

Thanks a lot for the support ! Your solution looks great.
But, I don't understand how can I combine your two methods.

 

When I import PowerQuery instance on an excel sheet, it imports a lot of tables as a power pivot. I want to import data only in a simple table. (as you do in https://exceleratorbi.com.au/excel-workbook-connection-local-power-bi-desktop/)

 

Can you explain me how can I import query results in tabular data from PowerBI Desktop ?

Thanks a lot

masplin
Impactful Individual
Impactful Individual

So I think these are steps

 

1. Create Desktop model

2. Publish to Power Bi Service

3. Go to power BI service and on the dataset click on 3 little dots and "Analyze in Excel"

4. Create any old pivot table

5. Double click one of the cells which luanches a new page with a table

6. Follow Chris webbs advice using the evalaute summarixze type clause I gave you.

 

I'm going to try it as also a solution for me.

 

The daft thing is there is no "Analze in Excel" directly from the desktop. You have to publish it to the online service first!

 

Mike

Hello,

I would like to do that without publishing my data model on Power Bi Service

How can i do that?

Thanks



@idategto11 wrote:


I would like to do that without publishing my data model on Power Bi Service

How can i do that?



by using the Local Host Workbook that I posted earlier.  This workbook will connect Excel to a running version of Power BI Desktop on your PC.  From there you can create a pivot table in Excel and follow the process to extract the data



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
masplin
Impactful Individual
Impactful Individual

Oddly no you have to publish. Makes no sense i know!!!!  You can extract maybe using DAX studio but then you end up with a csv

Hello, 

 

If you want to copy the table to an excel, why not trying to apply the filters that you want in the query editor, and then on the data view, copy the table? I've tried copy pasting a table with 116k rows and 58 columns, works.

2017-06-27 14_42_48-.png

You're the best !

It's working, thanks a lot

 

The best should be to export the query in excel in order to be able to update data in excel and not on PowerBI Desktop

masplin
Impactful Individual
Impactful Individual

Which version is working using Analyze in excel?

Ah I've been looking for an answer to this all over!!!

 

If you use analyze in Excel are you just getting a snap shot so you would have to repeat the process to get fresh data?

 

I really need a solution that is like the old dax query table in a powerpivot model, but cant find anything.

 

Thnaks

Mike


@masplin wrote:


If you use analyze in Excel are you just getting a snap shot so you would have to repeat the process to get fresh data?

 


 

Yes an no.  You are getting a snapshot that persists, but to refresh it simply right click the table in Excel and click "refresh"



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
masplin
Impactful Individual
Impactful Individual

Sadl;y there seems no tool to do this in power BI.  Are you working form Power Bi Desktop or in Powerpivot?

 

If you are in powerpivot or using DAX Studio you can use a statement like this to draw a table even querying across related tables.  TYuo can add filter conditions and order conditions.

 

evaluate calculatetable( summarize( vehicles, vehicles[VRM],customer[mobile phone no],customer[e-mail],vehicles[mot due date],vehicles[make],vehicles[model],vehicles[date of 1st registration],customer[last centre],vehicles[next service date], vehicles[last visit date], vehicles[service due],vehicles[customer 1y active], customer[full name], customer[address],customer[address 2],customer[city],customer[post code],customer[customer group],customer[Plat MOT Avail], customer[Plat XS Avail],customer [MOT reminder platinum], customer[XS reminder platinum],customer[salutation],customer[first name],customer[surname] ), FILTER( vehicles, vehicles[mot rem output]="yes" ) )

ORDER BY vehicles[MOT Due Date], vehicles[VRM]

 

I have not used this in DAX Studio but beleieve it works if you link up Dax studio to your Power BI model. 

 

If oyu have a single table I think you can just slect the whole thing in table view in Desktop and cut and pate to excel. Hopefully this link covers all options

 

exporting-data-from-power-bi-desktop-to-excel-and-csv-part-1-copy-paste-and-dax-studio-methods/

 

Mike

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors