locked
Best practices: when to load a query to a worksheet vs. keep it as connection only? RRS feed

  • Question

  • Sometimes I use Power Query to import data from another Excel file. Everything works fine if I keep the query as "connection only," but in order to improve clarity for other users, do you generally recommend loading the data to a table in a worksheet - that way people can see the data in a worksheet without having to open the Power Query pane? Would loading the data to a worksheet significantly slow performance? 

    Grateful for any advice!

    Monday, December 12, 2016 2:46 AM

Answers

  • Hi Brent. It really depends on what you're trying to do. Generally speaking, it's better performance-wise to make your queries "connection only" unless you need them to be loaded to the sheet (or data model). Doing so reduces the amount of data that has to be refreshed whenever you do a "Refresh All".

    Ehren

    Wednesday, December 14, 2016 12:29 AM
  • That's a giant "it depends".

    Generally I use pivot tables or power views to visualize the data, but for some reports people just want the "raw" data in a list format. For those, I load the data of my final query from all the merges/unions/what have you, to a worksheet.


    Ian

    Wednesday, December 14, 2016 8:51 PM

All replies

  • Hi Brent. It really depends on what you're trying to do. Generally speaking, it's better performance-wise to make your queries "connection only" unless you need them to be loaded to the sheet (or data model). Doing so reduces the amount of data that has to be refreshed whenever you do a "Refresh All".

    Ehren

    Wednesday, December 14, 2016 12:29 AM
  • That's a giant "it depends".

    Generally I use pivot tables or power views to visualize the data, but for some reports people just want the "raw" data in a list format. For those, I load the data of my final query from all the merges/unions/what have you, to a worksheet.


    Ian

    Wednesday, December 14, 2016 8:51 PM
  • Hi Ian. That makes sense, and seems like a fine way to provide your users with what they want (assuming the data isn't larger than the Excel canvas).

    Ehren

    Wednesday, December 14, 2016 8:55 PM