cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Responsive Resident
Responsive Resident

Re: How to export some huge query results in excel

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
Highlighted
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

Highlighted
Post Prodigy
Post Prodigy

Re: How to export some huge query results in excel

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

Highlighted

Re: How to export some huge query results in excel

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 a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Highlighted
Post Prodigy
Post Prodigy

Re: How to export some huge query results 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

Highlighted

Re: How to export some huge query results in excel


@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 a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Highlighted
Frequent Visitor

Re: How to export some huge query results in excel

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

Highlighted
Post Prodigy
Post Prodigy

Re: How to export some huge query results in excel

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

Highlighted
Frequent Visitor

Re: How to export some huge query results in excel

Hello,

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

How can i do that?

Thanks


Highlighted
Responsive Resident
Responsive Resident

Re: How to export some huge query results in excel

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

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors