PowerQuery Export

0
votes

I need to export a query from PowerQuery to a *.csv file so that another program can use it, but the 2 column data-set has about 5 million rows. Is there a procedure either within PQ/Excel or another of the standard MS Office suite which would allow me to simply save the table as a ''*.csv``? I tried using VBA, but it seems to only export the standard excel-sized file (~1 million rows). I could be doing it "wrong", but the VBA documentation I've found so far has not illuminated the correct procedure.

Edit: I've tried a variety of the snippets I found when searching for "export powerquery|worksheet with VBA", but this is the most recent:

Sub savesheet2()
Application.ScreenUpdating = False
ActiveSheet.Select
ActiveSheet.Copy
ActiveSheet.SaveAs Filename:="Device_Letter:\File_Location\name.csv", FileFormat:=6
Application.ScreenUpdating = True
ActiveWorkbook.Close
End Sub
1
excelpowerquery
Use VBA to save the worksheet – Ricardo Diaz
@RicardoDiaz Sounds plausible. I'm not that good with VBA. What would I search for to find a procedure for that? – user121330
Export worksheet with VBA – Ricardo Diaz
daxstudio.org and biinsight.com/… – Storax
@RicardoDiaz It appears that when I export a 'worksheet', I get a 'worksheet' sized file which is has much fewer rows. Perhaps you know of another set of search terms, or ... much better ... how to actually do it? – user121330

1 Answers

0
votes

At this time, it appears PowerQuery doesn't have the capacity to write its output to file unless it fits within the parameters of Excel. Depending on what your needs are, PowerShell (the Windows terminal) is a remarkably powerful tool for working with large *.csv files. For this project, I pruned and performed the simpler calculations with PowerShell.

The wise internet would have you believe that BI or VBA can help output PowerQuery results that are too large for Excel. If that is possible, I strongly encourage you to post actual VBA code or the BI procedure. Otherwise, if you're looking to manipulate large *.csv files, see how much you can get PowerShell to do before trying to explore those other rabbit holes.