none
Excel 2016 Power Query: Reference Queries performance

    Question

  • I have large csv file using desktop Excel 2016 powerquery.  I created first query as a connection, and 9 other reference queries to save results to tables using "Close and Load".  Each query filters data by different criteria and "Group By".  The "Close and Load" is taking too long to finish.  I would think that the first query would take time, and the reference query would be fast because they are just filtering/grouping data.  This posting tells me otherwise:

    http://community.powerbi.com/t5/Desktop/How-to-Improve-Query-Reference-performance-for-large-tables/td-p/23901

    Currently I can avoid using "close and load" by manually "copy entire table" in the query editor, close the editor, and paste into sheet, but that defeats purpose of automation.  It seems there is no VBA code to do this. 

    How can I improve the speed, such as to avoid using "close and load" by grabbing the query results via other means using VBA (not manual).  


    Thursday, March 22, 2018 5:03 PM

Answers

All replies

  • As per forum thread that you've posted there are a few things you can do.

    If you know how to apply Table.Buffer, then you can introduce an additional step to temporarily bring the csv contents into Power Query so it wouldn't have to wait on streaming and applying various transformations.

    If the CSV is very large. Try importing it verbatim into the DataModel (Power Pivot). This way the entire dataset will live in memory. and will take up very little space. Then you can use Power Query to query this data and apply all the transformations you need at far greater speeds.

    Thursday, March 22, 2018 6:52 PM
  • Thanks, it appears that my first query was already loaded as a connection and also to DataModel given I can see it in PowerPivot.  Currently my other queries are referencing the first query and using "close and load" to sheet, which is very slow (I am not sure why if data already lives in memory).   Sample reference query code (assuming "test" is my first query):

        ActiveWorkbook.Queries.Add Name:="test (2)", _
            Formula:= _
            "let Source = #""test"","  _  

    Do you mean I don't have to use reference queries and that there is a faster method to access the DataModel in PowerPivot using VBA.  If that is the case, can you provide some sample VBA code to transform the first query in powerpivot?

    Also I searched internet and could not find sample VBA code for table.buffer to import CSV file.  This is the only code I found which is not related to CSV. 

    https://msdn.microsoft.com/en-us/library/mt260741.aspx?f=255&MSPPError=-2147217396

    Can you provide sample code for CSV import?


    • Edited by kkaax Thursday, March 22, 2018 10:16 PM
    Thursday, March 22, 2018 10:13 PM
  • Hi kkaax. If you let the Close and Load complete, how long does it take?

    Copying and pasting from the Query Editor isn't likely to work, as the tables it displays often only contains a subset of the total data.

    Ehren

    Monday, April 2, 2018 10:23 PM
    Owner