none
Making data in the data model available to Power Query RRS feed

  • Question

  • As I understand it, there are two ways of persisting data from a Power Query in Excel:
    1) Loading the data into a worksheet as a table
    2) Loading the data into the data model

    I have a query against a database that takes around 10 minutes to run, and returns about 2 million rows. That is too large to fit into a worksheet, but I can load it into the data model. I save and close the workbook, and open it again later. The data is still available in the data model, as can be seen by opening Power Pivot. Now I want to transform the data, using Power Query. How can I make the data in the data model available to Power Query? I want to avoid having to rerun the query against the database.
    Monday, April 30, 2018 8:35 AM

Answers

  • do you load data to PowerPivot with PowerQuery or otherwise?
    to my knowledge PowerQuery cannot connect to PowerPivot tables. There some posts getting about getting data from PowerPivot to CSV with VBA and then loading the CSV to PowerQuery

    if you use PowerPivot to connect to database then I would rebuild the connection in PowerQuery and process it there
    Tuesday, May 1, 2018 1:41 PM
  • mattiash77,

    The first part of your original post is a true, but you've muddled up the order of operations in your last paragraph.

    You first import the data into Power Query, then do all the transformations you need, and then load to either Excel or the data model (or both) - not the other way around.

    Tuesday, May 1, 2018 5:46 PM

All replies

  • Power Query as such doesn't store any data, it's more a script describing how to extract and process the data. The outcome of that processing can be loaded to PowerPivot.

    So in your case I wouldn't load the raw data to PowerPivot at all. I would reference the 2mln row query and do all the processing needed and then only load the final outcome to PowerPivot

    • Proposed as answer by Colin Banfield Monday, April 30, 2018 3:40 PM
    • Unproposed as answer by matthiash77 Tuesday, May 1, 2018 1:20 PM
    Monday, April 30, 2018 12:31 PM
  • Just to be clear, when I say "stored in the data model", I mean the same as "stored in PowerPivot".

    I understand that Power Query doesn't store any data. My data is stored in the data model (PowerPivot). So my question is, how can Power Query access the data in the data model, without first having to store it in a worksheet (which is not possible anyway with this many rows)?

    Tuesday, May 1, 2018 1:19 PM
  • do you load data to PowerPivot with PowerQuery or otherwise?
    to my knowledge PowerQuery cannot connect to PowerPivot tables. There some posts getting about getting data from PowerPivot to CSV with VBA and then loading the CSV to PowerQuery

    if you use PowerPivot to connect to database then I would rebuild the connection in PowerQuery and process it there
    Tuesday, May 1, 2018 1:41 PM
  • mattiash77,

    The first part of your original post is a true, but you've muddled up the order of operations in your last paragraph.

    You first import the data into Power Query, then do all the transformations you need, and then load to either Excel or the data model (or both) - not the other way around.

    Tuesday, May 1, 2018 5:46 PM