Excel Power Query and Pivot Tables
Excel Power Query records repeatable steps for importing and reshaping data. PivotTables then group and aggregate the prepared rows, either from a worksheet table or from related tables in the Excel Data Model.
itData engineering and analytics | OpenSkills.info
Course pathWalk it in order
Look it upDip in anytime
Go furtherLeaves this page
Don't Panic
Don't Panic - Excel Power Query and Pivot Tables
Excel Power Query and PivotTables form a preparation-and-analysis pipeline inside a workbook. Power Query connects to a source, applies an ordered set of transformations, and loads a result. A PivotTable groups that result into an interactive summary. The boundary matters: Power Query changes the structure delivered to Excel; a PivotTable changes the view of that delivered data without rewriting the source or the query.
The end-to-end flow has five parts. A source supplies records. A query stores connection details and Applied Steps. A load destination writes a worksheet table, loads tables into the Data Model, or stays connection-only. An optional Data Model holds relationships. A PivotTable places fields into rows, columns, values, and filters. Refresh runs the query again and replaces the previous loaded result. Dependent PivotTables then need their own refresh so they are not left on a stale cache.
Every query is also M. The graphical editor writes routine steps; the formula bar and Advanced Editor expose cases the interface cannot express well. Query folding may push work to a structured source. A non-foldable step earlier in the list can force later work into the mashup engine and change refresh cost without an obvious change to the business logic.
Keep shaping in the query and analysis in the PivotTable. Manual edits inside a loaded query table are not transformation logic and disappear on refresh. Prefer parameters for volatile paths and filters. Use the Data Model when the analysis needs related tables; use a worksheet load when the intermediate table must stay visible and simple.
Read the Intro for the pipeline and M boundary. Use the Cheatsheet when you need the step and destination map. Landscape places Power Query among related preparation tools; Updates tracks Microsoft 365 Current Channel notes where Excel and Power Query changes ship.
Where this skill leads
Relevant careers
See how this topic contributes to broader role-level skill maps.
Sources
- https://support.microsoft.com/en-US/Excel/how-power-query-and-power-pivot-work-together
Supports
- Power Query connect, transform, combine, and load phases
- Worksheet and Data Model load destinations
- Power Query, Power Pivot, PivotTable, PivotChart, and Power BI roles
- https://support.microsoft.com/en-US/Excel/create-load-or-edit-a-query-in-excel-power-query
Supports
- Power Query Editor and Excel worksheet as distinct environments
- Worksheet and Data Model load commands
- Data Model query editing and refresh behavior
- https://support.microsoft.com/en-us/excel/manage-queries-power-query
Supports
- Query and connection responsibilities
- Duplicate, reference, merge, append, and connection-only query behavior
- https://support.microsoft.com/en-us/excel/append-queries-power-query
Supports
- Append row stacking and column-name alignment
- Null values for unmatched columns
- Privacy-level considerations when combining sources
- https://support.microsoft.com/en-us/excel/overview-of-pivottables-and-pivotcharts
Supports
- PivotTable grouping, aggregation, filtering, layout, and exploration
- Worksheet source structure and refresh behavior
- PivotTable cache sharing and PivotChart coupling
- https://support.microsoft.com/en-us/excel/create-a-data-model-in-excel
Supports
- Data Model as related tables inside an Excel workbook
- Power Query imports and PivotTable Field List integration
- Unique identifiers and relationship creation
- https://support.microsoft.com/en-us/office/use-multiple-tables-to-create-a-pivottable-in-excel-b5e3ff48-2921-4e29-be15-511e09b5cf2d
Supports
- PivotTables built from fields across related model tables
- Adding workbook tables to the Data Model
- Relationship requirement for fields from separate tables
- https://support.microsoft.com/en-us/excel/create-a-relationship-between-tables-in-excel
Supports
- Matching and compatible key columns
- Relationships as an alternative to duplicating lookup data
- Direct many-to-many circular dependency risk
- https://support.microsoft.com/en-US/Excel/handling-data-source-errors-power-query
Supports
- Source availability, credential, privacy, schema, and type failures
- Cell-level errors and column-name dependencies
- Early filtering and first-failing-step diagnosis
- https://support.microsoft.com/en-us/excel/refresh-an-external-data-connection-in-excel
Supports
- Preview cache versus loaded destination refresh
- Refresh All and connection refresh commands
- Connection information and external-data security
- https://learn.microsoft.com/en-us/power-query/query-folding-basics
Supports
- M evaluation path from source to destination
- Full, partial, and no query-folding outcomes
- Connector and transformation dependence of folding
- https://learn.microsoft.com/en-us/powerquery-m/
Supports
- M as Power Query's functional and case-sensitive language
- Language concepts, specification, type system, and function reference
- https://www.microsoft.com/en-us/microsoft-365/blog/2015/09/10/integrating-power-query-technology-in-excel-2016/
Supports
- Power Query as an add-in for Excel 2010 and 2013
- Native Get and Transform integration in Excel 2016
- Repeatable import and transformation refresh
- https://github.com/sindresorhus/awesome
Supports
- Required Awesome discovery starting point
- Discovery route into data and analytics lists
- https://github.com/pawl/awesome-etl
Supports
- Discovery of Alteryx, Apache NiFi, SQL Server Integration Services, and pandas
- Curated visual, cloud, and code-first ETL ecosystem categories
- https://help.alteryx.com/current/en/designer/get-started.html
Supports
- Alteryx Designer workflow concepts, interface, samples, and activation
- Learner destination for the Awesome Links entry
- https://nifi.apache.org/nifi-docs/user-guide.html
Supports
- NiFi visual dataflow construction, processors, queues, scheduling, and monitoring
- Data provenance and operated flow behavior
- https://learn.microsoft.com/en-us/sql/integration-services/ssis-how-to-create-an-etl-package
Supports
- Graphical SSIS package construction
- Extraction, transformation, fact-table loading, logging, and error flow
- https://pandas.pydata.org/docs/getting_started/index.html
Supports
- pandas tabular structures, selection, reshaping, combination, and summaries
- Learner destination for the code-first Awesome Links entry
- https://www.microsoft.com/en-us/microsoft-365/excel
Supports
- Microsoft Excel official product destination
- Excel as the host for this course's query-to-PivotTable pipeline
- https://powerbi.microsoft.com/
Supports
- Microsoft Power BI official product destination
- Power Query and model path beyond an Excel workbook
- https://help.tableau.com/current/prep/en-us/prep_clean.htm
Supports
- Tableau Prep cleaning, shaping, filtering, splitting, grouping, pivoting, and scripting
- Tableau Prep role as a visual data-preparation alternative
- https://www.alteryx.com/platform/analytics-workspace/desktop-workspace
Supports
- Alteryx Designer visual canvas, drag-and-drop preparation, blending, and analysis
- Alteryx Designer official product destination
- https://www.knime.com/knime-analytics-platform
Supports
- KNIME Analytics Platform open-source status and free availability
- Visual access, blending, transformation, analysis, and reusable workflows
- https://www.qlik.com/us/products/qlik-data-preparation
Supports
- Qlik visual data preparation and transformation in Qlik Cloud Analytics
- Qlik product destination for governed preparation
- https://help.qlik.com/en-US/sense/May2025/Subsystems/Hub/Content/Sense_Hub/Visualizations/PivotTable/pivot-table.htm
Supports
- Qlik pivot-table dimensions, measures, rearrangement, and hierarchical analysis