Best practice - Multiple queries vs one 'landed' query/use of references

[vpschroeder]

Hello Power Forum

I have typically been creating one 'master' query and then referencing that query in the hopes of obtaining the fastest query performance within my Excel workbook queries.
However i came across this (quite dated) article by Ken Puls written a few years back, and now im wondering if am not in fact following best practice

https://powerpivotpro.com/2017/07/power-query-refresh-speeds-suck/

Would appreciate if anyone could recommend an article or describe what is the recommended practice for creation of multiple queries to the same data source.

VPS

 

[Ken Puls]

Apologies, as I realize that this is a month old, but since it was my article you're referring to...

I prefer to stage my queries and set them up in the following structure:

1. Raw Data query (connects to and normalizes the data - showing me all available columns)
2. Staging queries (to reduce and transform the data as needed)
3. Data Model queries (to merge queries and load to data model)

My partner, on the other hand, prefers to do everything all in one query. Which is a "Best" practice? Totally depends on your opinion. As far as speed though, I don't believe you'll find much of a difference, as the Power Query engine evaluates the queries prior to execution and attempts to send the most efficient it can.

Now when you are re-using the a table in multiple places... that gets trickier. Depending on the Excel version, the product may not cache child queries, and may re-evaluate them. If you're on 2016, don't set up multiple layers, as the performance can suffer. But once you are on 365, those nodes do get cached. I honestly can't remember if 2019 got that fix... I think so, but if in doubt, built it in a "flat" version, save it, test the speed, then split the queries (right click a step -> Extract Previous) and test the time again. That will tell you.

 

[vpschroeder]

Response to Ken:

How fortunate I am that it was you that responded!
We are on office 365. Coincidentally, i believe my latest designs are structured exactly as you suggest.
Thanks again for your detailed response.