9.2: Power Query
- Page ID
- 150656
This page is a draft and is under active development.
\( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)
\( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)
\( \newcommand{\dsum}{\displaystyle\sum\limits} \)
\( \newcommand{\dint}{\displaystyle\int\limits} \)
\( \newcommand{\dlim}{\displaystyle\lim\limits} \)
\( \newcommand{\id}{\mathrm{id}}\) \( \newcommand{\Span}{\mathrm{span}}\)
( \newcommand{\kernel}{\mathrm{null}\,}\) \( \newcommand{\range}{\mathrm{range}\,}\)
\( \newcommand{\RealPart}{\mathrm{Re}}\) \( \newcommand{\ImaginaryPart}{\mathrm{Im}}\)
\( \newcommand{\Argument}{\mathrm{Arg}}\) \( \newcommand{\norm}[1]{\| #1 \|}\)
\( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\)
\( \newcommand{\Span}{\mathrm{span}}\)
\( \newcommand{\id}{\mathrm{id}}\)
\( \newcommand{\Span}{\mathrm{span}}\)
\( \newcommand{\kernel}{\mathrm{null}\,}\)
\( \newcommand{\range}{\mathrm{range}\,}\)
\( \newcommand{\RealPart}{\mathrm{Re}}\)
\( \newcommand{\ImaginaryPart}{\mathrm{Im}}\)
\( \newcommand{\Argument}{\mathrm{Arg}}\)
\( \newcommand{\norm}[1]{\| #1 \|}\)
\( \newcommand{\inner}[2]{\langle #1, #2 \rangle}\)
\( \newcommand{\Span}{\mathrm{span}}\) \( \newcommand{\AA}{\unicode[.8,0]{x212B}}\)
\( \newcommand{\vectorA}[1]{\vec{#1}} % arrow\)
\( \newcommand{\vectorAt}[1]{\vec{\text{#1}}} % arrow\)
\( \newcommand{\vectorB}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)
\( \newcommand{\vectorC}[1]{\textbf{#1}} \)
\( \newcommand{\vectorD}[1]{\overrightarrow{#1}} \)
\( \newcommand{\vectorDt}[1]{\overrightarrow{\text{#1}}} \)
\( \newcommand{\vectE}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash{\mathbf {#1}}}} \)
\( \newcommand{\vecs}[1]{\overset { \scriptstyle \rightharpoonup} {\mathbf{#1}} } \)
\(\newcommand{\longvect}{\overrightarrow}\)
\( \newcommand{\vecd}[1]{\overset{-\!-\!\rightharpoonup}{\vphantom{a}\smash {#1}}} \)
\(\newcommand{\avec}{\mathbf a}\) \(\newcommand{\bvec}{\mathbf b}\) \(\newcommand{\cvec}{\mathbf c}\) \(\newcommand{\dvec}{\mathbf d}\) \(\newcommand{\dtil}{\widetilde{\mathbf d}}\) \(\newcommand{\evec}{\mathbf e}\) \(\newcommand{\fvec}{\mathbf f}\) \(\newcommand{\nvec}{\mathbf n}\) \(\newcommand{\pvec}{\mathbf p}\) \(\newcommand{\qvec}{\mathbf q}\) \(\newcommand{\svec}{\mathbf s}\) \(\newcommand{\tvec}{\mathbf t}\) \(\newcommand{\uvec}{\mathbf u}\) \(\newcommand{\vvec}{\mathbf v}\) \(\newcommand{\wvec}{\mathbf w}\) \(\newcommand{\xvec}{\mathbf x}\) \(\newcommand{\yvec}{\mathbf y}\) \(\newcommand{\zvec}{\mathbf z}\) \(\newcommand{\rvec}{\mathbf r}\) \(\newcommand{\mvec}{\mathbf m}\) \(\newcommand{\zerovec}{\mathbf 0}\) \(\newcommand{\onevec}{\mathbf 1}\) \(\newcommand{\real}{\mathbb R}\) \(\newcommand{\twovec}[2]{\left[\begin{array}{r}#1 \\ #2 \end{array}\right]}\) \(\newcommand{\ctwovec}[2]{\left[\begin{array}{c}#1 \\ #2 \end{array}\right]}\) \(\newcommand{\threevec}[3]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \end{array}\right]}\) \(\newcommand{\cthreevec}[3]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \end{array}\right]}\) \(\newcommand{\fourvec}[4]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \\ #4 \end{array}\right]}\) \(\newcommand{\cfourvec}[4]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \\ #4 \end{array}\right]}\) \(\newcommand{\fivevec}[5]{\left[\begin{array}{r}#1 \\ #2 \\ #3 \\ #4 \\ #5 \\ \end{array}\right]}\) \(\newcommand{\cfivevec}[5]{\left[\begin{array}{c}#1 \\ #2 \\ #3 \\ #4 \\ #5 \\ \end{array}\right]}\) \(\newcommand{\mattwo}[4]{\left[\begin{array}{rr}#1 \amp #2 \\ #3 \amp #4 \\ \end{array}\right]}\) \(\newcommand{\laspan}[1]{\text{Span}\{#1\}}\) \(\newcommand{\bcal}{\cal B}\) \(\newcommand{\ccal}{\cal C}\) \(\newcommand{\scal}{\cal S}\) \(\newcommand{\wcal}{\cal W}\) \(\newcommand{\ecal}{\cal E}\) \(\newcommand{\coords}[2]{\left\{#1\right\}_{#2}}\) \(\newcommand{\gray}[1]{\color{gray}{#1}}\) \(\newcommand{\lgray}[1]{\color{lightgray}{#1}}\) \(\newcommand{\rank}{\operatorname{rank}}\) \(\newcommand{\row}{\text{Row}}\) \(\newcommand{\col}{\text{Col}}\) \(\renewcommand{\row}{\text{Row}}\) \(\newcommand{\nul}{\text{Nul}}\) \(\newcommand{\var}{\text{Var}}\) \(\newcommand{\corr}{\text{corr}}\) \(\newcommand{\len}[1]{\left|#1\right|}\) \(\newcommand{\bbar}{\overline{\bvec}}\) \(\newcommand{\bhat}{\widehat{\bvec}}\) \(\newcommand{\bperp}{\bvec^\perp}\) \(\newcommand{\xhat}{\widehat{\xvec}}\) \(\newcommand{\vhat}{\widehat{\vvec}}\) \(\newcommand{\uhat}{\widehat{\uvec}}\) \(\newcommand{\what}{\widehat{\wvec}}\) \(\newcommand{\Sighat}{\widehat{\Sigma}}\) \(\newcommand{\lt}{<}\) \(\newcommand{\gt}{>}\) \(\newcommand{\amp}{&}\) \(\definecolor{fillinmathshade}{gray}{0.9}\)By the end of this section, you will be able to:
- Explain the purpose and advantages of using Power Query in Excel.
- Import data from multiple sources into Excel using Power Query.
- Apply basic data cleaning and transformation steps.
- Combine (append or merge) multiple datasets.
- Load transformed data back into Excel for analysis.
Introduction to Power Query
Modern organizations generate and collect data from an ever-growing number of sources: point-of-sale systems, customer databases, online forms, surveys, and cloud services, to name a few. Managing all of this information can be challenging because raw data often arrives inconsistent, incomplete, or unstructured. Cleaning, reshaping, and combining datasets manually with formulas can quickly become time-consuming and prone to human error.
Power Query was designed to solve this problem. It is an advanced data-connection and transformation tool built directly into Excel that allows you to import, clean, combine, and automate the preparation of data — all through a simple, step-by-step interface. Instead of writing formulas or code, users perform actions such as filtering rows, renaming columns, or merging tables, and Power Query automatically records each step as part of a repeatable process.
When you use Power Query, every cleaning or transformation step is saved in an ordered list called Applied Steps, visible on the right-hand side of the Power Query Editor. This list acts like a “recipe” for preparing your data: you can review, reorder, or remove any step at any time. The next time new data is added or a file is updated, clicking Refresh automatically re-applies the same steps, ensuring that your data is always consistent and up to date.
From a business perspective, Power Query plays a vital role in data automation and integrity. For example:
- A marketing analyst can combine weekly ad performance reports exported from different platforms into one clean master sheet.
- A finance team can import monthly sales ledgers, remove duplicate transactions, and standardize region names before analysis.
- A human-resources department can merge data from multiple branches into one report to track staffing trends.
Each of these tasks would require dozens of manual edits without Power Query. With it, data preparation becomes fast, consistent, and repeatable, freeing time for deeper analysis and decision-making.
Power Query is accessed from the Data tab in Excel, under the Get & Transform Data group. It supports a wide range of sources, including Excel tables, text or CSV files, databases, and online feeds. The cleaned data can then be loaded back into Excel as a table or connected directly to dashboards in tools such as Power BI or Tableau.
In this section, you will explore how to:
- Import data from different sources.
- Clean and transform data using built-in tools.
- Combine multiple datasets with Append and Merge functions.
- Load the final, prepared data back into Excel for reporting and analysis.
By the end of this section, you will understand how Power Query transforms Excel from a simple spreadsheet into a powerful data management and automation tool, giving professionals more control and reliability over the data they depend on.
Accessing Power Query
Power Query is located under the Data tab in Excel within the Get & Transform Data group. You can use it to import data from various sources such as:
- Excel tables or ranges
- CSV or text files
- Databases (SQL, Access)
- Online or cloud data (SharePoint, Web URLs, etc.)
Figure 12.8
Importing Data into Power Query
Let’s walk through an example using a simple sales dataset stored in another worksheet or CSV file.
- Click the Data tab.
- Choose Get Data → From File → From Workbook (or From Text/CSV).
- Navigate to your file and select Import.
- The Navigator window will appear, displaying available tables or sheets.
- Click Transform Data to open the Power Query Editor (Figure 12.9).
Figure 12.9
Cleaning and Transforming Data
Inside the Power Query Editor, you’ll find a ribbon similar to Excel’s, with options to filter, rename, split, or merge columns. Each transformation you make is recorded as a “step” in the Applied Steps panel, which you can edit or remove at any time.
Common transformations include:
- Remove Columns – delete unnecessary data.
- Rename Columns – make headers clearer.
- Split Columns – separate first and last names or extract data from delimiters.
- Change Data Types – convert text to numbers, dates, etc.
- Filter Rows – keep only records that meet specific criteria.
Figure 12.10
Each time you make a change, Power Query stores the step automatically. When the data is refreshed, all steps are re-applied in the same order, ensuring data consistency without repeated manual work.
Combining Data with Append and Merge
Power Query can also combine multiple datasets:
- Append Queries: Stack data from similar tables (e.g., January, February, and March sales files).
- Merge Queries: Join tables using a shared column, similar to a VLOOKUP or database join.
To combine data:
- Open the Power Query Editor.
- Go to the Home tab and choose either Append Queries or Merge Queries.
- Select your tables and the columns to match.
- Review the preview, then click OK.
Figure 12.11
Figure 12.12
Loading Transformed Data
Once you finish cleaning or combining data, click Close & Load in the Home tab of the Power Query Editor.
Your cleaned dataset will appear as a new worksheet or Excel table.
If the source data changes, simply right-click anywhere in the table and select Refresh — Power Query will re-import and reapply all the steps you created (Figure 12.13).
Figure 12.13
Summary
Power Query is one of Excel’s most powerful tools for transforming raw data into organized, ready-to-analyze information. It allows users to connect to multiple data sources, clean and reshape data, and automate the preparation process — all without writing formulas or code.
In business environments, Power Query streamlines repetitive and error-prone data tasks. Analysts can import monthly sales reports, standardize column names, remove duplicates, and merge data from multiple files in just a few clicks. Once set up, these steps become part of a repeatable workflow: when new data is added or updated, refreshing the query instantly applies all the same transformations.
By learning Power Query, you gain the ability to:
- Connect to data from files, databases, and online sources.
- Clean and reformat messy or inconsistent information.
- Combine multiple datasets using append and merge operations.
- Automate routine reporting tasks for greater efficiency and accuracy.
In real-world applications, businesses use Power Query to prepare data for financial reporting, marketing dashboards, inventory tracking, and customer analysis. It eliminates the need for manual cleanup, reduces human error, and ensures that data-driven decisions are based on accurate and consistent information.
Ultimately, Power Query empowers professionals to spend less time preparing data and more time interpreting insights — a critical skill for anyone working with analytics, reporting, or business intelligence.