Power Query is based on an ETL (Extract, Transform, Load) engine integrated into Excel that generates M code with each manipulation in the editor. Understanding this mechanism radically changes the approach to data cleaning and consolidation, far beyond the simple “drag-and-drop” described by most tutorials.
M Language and Applied Steps in Power Query: What the Editor Hides
Each transformation performed in the Power Query editor produces a line of M code recorded as a step applied in the side panel. The graphical interface facilitates common operations, but it hides the underlying functional logic.
The M language operates through lazy evaluation: a step is only calculated if a subsequent step requires it. We recommend regularly checking the formula bar to see what the editor generates, as some transformations insert implicit type conversions that burden processing on large files.
The order of steps has a direct impact on performance. Filtering rows before merging two queries significantly reduces the volume of data in memory. Conversely, placing a sort before a filter forces the engine to order rows that will then be deleted. We can also learn everything about Power Query with Excel by examining the structure of these applied steps rather than sticking to the visual interface.
Privacy Levels and Data Connection Management
Privacy levels are the most common source of errors in companies when multiple sources are combined in Power Query. Three levels exist: Public, Organizational, Private. They determine whether the engine accepts cross-referencing data from different sources.
When two sources have incompatible levels, Power Query blocks the query with a “Formula.Firewall” message. The temptation is to check “Ignore privacy levels” in the options. In a personal environment, this is inconsequential. In a professional context, this option disables the barrier that prevents the accidental sending of sensitive data to an external source.
Microsoft has strengthened its recent documentation on this point. We observe that companies deploying Power Query on shared workstations benefit from defining privacy levels at the data source level, not at the workbook level, to prevent a user from changing a setting without understanding its implications.
Power Query Connectors: Monitoring Deprecations
Power Query offers connectors to dozens of sources (SQL databases, CSV files, web APIs, cloud services). This catalog evolves. Microsoft has, for example, deprecated the QuickBooks connector, recommending migrating existing flows to other methods before complete removal.
Regularly checking the list of active connectors prevents discovering one day that an automated query no longer works. A deprecated connector may continue to function for a few months, then cease without warning during an Excel update.
Stability of the Power Query Editor Depending on the Version of Excel
Tutorials assume a stable and responsive Power Query editor. The reality on the ground is more nuanced. Recent user feedback reports editor freezes (“Not Responding”) on certain versions or beta channels of Excel.
Several factors influence stability:
- The Office update channel (Current Channel, Monthly Enterprise, Semi-Annual) determines the version of the embedded Power Query engine. Fast channels receive new features but also potential regressions.
- The volume of data loaded in the editor’s preview affects memory. Working with a limited preview (the first few hundred rows) and only loading the complete dataset at the final load improves fluidity.
- Nested queries with multiple levels of references between tables increase the complexity of the dependency graph, which slows down evaluation in the editor.
We recommend locking the version of Excel on a stable channel in a production context and testing updates on a pilot workstation before deployment.
Web Extraction with Power Query: Table Detection and Limits
Importing from a web page remains one of the most underutilized functions of Power Query. The web connector automatically detects tabular structures (HTML table tags) and offers to load them as separate queries.
Recent developments have improved assisted table detection, even on pages with partially irregular markup. However, data generated dynamically by JavaScript is not captured by the native web connector, which only works with the static DOM of the page.
Structuring Transformations for Automatic Refresh
One of the major gains of Power Query is the ability to refresh all queries in a workbook with one click. For this refresh to work without manual intervention, each query must point to an accessible source (valid network path, active URL, connected database).
A classic pitfall: building a query pointing to a file on the local desktop, then sharing the workbook. The query fails because the path does not exist on the recipient’s machine. Storing source files on a shared network location or a synchronized folder resolves this issue from the design stage.
Power Query on Excel now covers a scope that goes beyond simple column cleaning. Managing privacy levels, tracking deprecated connectors, and choosing the update channel are operational parameters that condition the reliability of data flows in the long term.