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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.