Export from Power Query directly to CSV

[haralamby]

Do you know how to export data from power query directly to a csv file? Exporting it to a table does not work....I guess my file is too big.

Any workaround will do. So far I love Power Query but can't use it at all if I can't get the data back out to upload somewhere else.

Thank you in advance!!!!

 

[Matt Allington]

Load the data to the data model, not excel. Then use dax studio to extract the table into a csv

read my article here

Getting Started with DAX Studio - Excelerator BI

 

[haralamby]

Load the data to the data model, not excel. Then use dax studio to extract the table into a csv

read my article here

Getting Started with DAX Studio - Excelerator BI

Thank you so much for you advice!!!!!!

I have an old version of Power Query ...I think 2010/13 which does not have the option to save to data model. I tried updating one of my other computers to 2016 and now I have the option but now I have a new problem that I can't open my old files in the 2016 version. I did a lot of work to flatten multiple tables (7 of them) into one. Do I have to do it again from scratch

. Is there not a way to open the old files in the new updated version??

 

[Matt Allington]

If it is an old version of power query, just download a new one and install it. But are you using Excel 2010? If so, this option doesn't exist at all. You need to create the query, don't load to excel and just make the connection. Then go I to power pivot and select existing connections and find the query there. Just go through the wizard to import the data

 

[haralamby]

Yes I am using Excel 2010. I tried that as well (making the connection and then trying to use power pivot wizard) but both ways end up in my computer crashing.

I think I am stuck and probably forced to build the power query all over again from the beginning on an updated excel version.

I will take any other ideas as this is obviously my least preferred option.

Also how confident are you that it will not crash with the new version when I try and extract the query into a CSV??

 

[Matt Allington]

when you say "power pivot wizard", are you starting with "Existing Connections"? That is how you should do it.

How confident am I it wont crash? Given it has crashed in there scenario, I am not confident at all. Your software is 6 years old and superseded by 2 complete version releases. Time to upgrade (although I realise that may not be in your control).

 

[haralamby]

The only way I know to how to get to existing connections is to click on the power pivot window (sometimes it will crash in this first step ) => Design => existing connections.

Is there another way?

 

[Matt Allington]

click on the power pivot window => Design => existing connections.

That is correct. Best options are
1. get more PC memory - I consider 8GB to be the minimum if you use Power Pivot
2. get the 64 bit version of Office (this can be difficult in some companies)
3. upgrade to Excel 2016

Better still, do all of the above

You could try to do it in Power BI Desktop, but that may not meet your needs