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
Intro
Excel Power Query and Pivot Tables
Excel Power Query and PivotTables form a preparation-and-analysis pipeline inside a workbook. Power Query connects to source data, applies an ordered set of transformations, and loads the result. A PivotTable groups that result into an interactive summary.
The key boundary is between shaping rows and analyzing groups. 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
The workflow has five parts:
- Source: A worksheet table, text file, folder, database, web source, or another supported connector supplies records.
- Query: Power Query stores connection details and a sequence of transformation steps.
- Load destination: The query writes a table to a worksheet, loads tables into the Data Model, or remains connection-only for use by another query.
- Model: The optional Excel Data Model holds multiple tables and relationships between matching keys.
- PivotTable: Fields become rows, columns, values, and filters. Excel aggregates the values for each intersection of row and column groups.
Refresh runs the query again against its source. The output replaces the previous loaded result, and dependent PivotTables can then display the updated rows. Refresh does not replay manual edits made inside a loaded query table as transformation logic.
Power Query as a transformation recipe
Power Query Editor shows a data preview and an Applied Steps list. Each action adds a step. Common steps select columns, filter rows, assign data types, split values, replace values, group rows, pivot or unpivot columns, and combine tables.
Continue the course
This section is part of the paid course.
See pricing to subscribe, or log in if you already have access.
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