Search code examples
excelcsvconnectionexportpowerquery

Export Data Connection Query from Excel to CSV (>2M rows)

I have a spreadsheet that is using power query in a data connection to execute modelling on CSV files that have more than 2M rows. This works fine and I can run analysis on this data as needed.

However, now I want to export the resulting data after all the manipulations have been made. I can't see a way to export the data query result without outputing to a sheet... which I can't... because the row count is >1M.

  1. Can I export the data without outputting to a sheet/multiple sheets?
  2. If so, what tool would I need (if any) and what are the general steps?

I've been trying to work this out on my own for months but I have to recognise my limitations and ask for help now. Many thanks in advance. DD

Solution

  • As David mentioned above, Dax Studio will output the results of the data model. Steps as follows:

    1. Download and install the Dax Studio plugin for excel
    2. Make sure the connection you are trying to extract is in the data model.
    3. Launch Dax Studio.
    4. !!!Dax Studio will only be able to read your data if you launch it from the Add-ins tab of Excel. This is the only data source type where access to the data is dependent on how Dax Studio is launched. Again, YOU MUST LAUNCH THIS FROM THE ADDINS MENU OF THE EXCEL DOCUMENT CONTAINING YOUR QUERY/CONNECTION.
    5. A dialogue box will appear. It should show data source "Power Pivot Model" as the radio selection. If it is greyed out, it means that you have not added your query/connection to the data model.
    6. Select "Connect". You will now see all the tables in your data model on the left hand side and may now extract to csv.