Power Query mistakes show up as gateway timeouts and VertiPaq bloat. The diagnostic is blunt: right-click a step → View Native Query. Grayed out means mashup is doing work SQL could have done.
Patterns that keep folding
- Filter and select columns early against SQL/warehouse sources; avoid Table.Buffer unless you can prove the need.
- Expand nested records late and only for required columns — Expand all attributes multiplies columns and breaks folding.
- Set types explicitly once (Transform → Detect still leaves Any on dirty Excel); untyped Any columns compress poorly and slow refresh.
Staging over copy-paste
Create a staging query (Enable load off) for the heavy extract; reference it for dimensions/facts. Duplicating the same SQL extract into five queries means five refresh hits. Parameterise server/database names — hard-coded Desktop paths break on the On-premises Data Gateway with path-not-found style Failures.
For Excel sources, prefer a single curated table/sheet; merging twenty personal workbooks in M is how month-end refresh exceeds the SLA.
Adoption without a second mashup estate
The same Power Query patterns fail at company scale when every analyst keeps a private mashup. Publish a staging template (PBIX or dataflow) with approved connectors only: SQL, warehouse, SharePoint folder. Train View Native Query as a definition of done. Ban personal C: and OneDrive paths in anything with scheduled refresh; require a gateway-visible UNC or SharePoint site.
When multiple datasets repeat the same M, move staging to a dataflow so refresh cost is paid once. Document the owner of that dataflow and which reports break if its Status = Failed. After 30 days, audit mashup errors (Expression.Error, DataSource.NotFound). Those spikes are almost always path and credential hygiene.