Skip to content
Tutorials Center
My learning

Topics

Lesson 69 of 93 ยท Data Cleaning and Import

Power Query: Get and Transform

Learn Power Query in Excel: queries, applied steps, merge vs append, unpivot, refresh and privacy levels, with pandas melt, merge and concat stand-ins.

Cleaning data by hand works once. The second month, when a new export arrives with the same problems, you do it all again. Power Query (called Get & Transform on the Data tab) fixes this. You load data, apply cleaning steps with the mouse, and Excel records every step. Next month you press Refresh and the whole recipe runs again on the new data.

Power Query is built into Excel 2016 and later (the Windows version is the most complete; Excel for Mac supports it with some limits) and into Excel for Microsoft 365. It also powers Power BI. Excel cannot run on this site, so the steps below are described in text. The live examples use pandas (melt, merge, concat) as stand-ins. They do the same reshaping jobs and make the ideas visible, but Power Query's engine and its language, M, are different. The next lesson, Power Query M Language Basics, shows what the recorded steps look like as code.

What a query is

Source File, folder, web, database.
Applied steps A recorded list of changes.
Load To a table or the data model.
Refresh Replay all steps on new data.
1 / 2

The workflow in one picture

flowchart LR
    A["Source<br/>CSV, folder, web"] --> B["Power Query Editor<br/>applied steps"]
    B --> C["Load to<br/>table or data model"]
    C --> D["Refresh<br/>replays the steps"]
    D --> B

Data flows through the recorded steps; Refresh repeats the whole path on fresh source data.

Your first query

  1. Click inside a table (or choose Data > Get Data and pick a source such as From Text/CSV, From Workbook or From Folder; see Importing Data from CSV, Text and the Web).
  2. For data already in the workbook, select a cell in an Excel Table and click Data > From Table/Range. Excel creates a table if needed.
  3. The Power Query Editor opens in a new window. The data preview is in the middle, the Query Settings pane with Applied Steps is on the right and the Queries list is on the left. (If the right pane is hidden, turn it on in View > Query Settings.)
  4. Make your changes, then Home > Close & Load. The result lands in a new worksheet table. Close & Load To... lets you choose a connection only, a PivotTable or the Data Model instead.

Applied steps: the recipe

Every click in the editor adds a named entry under Applied Steps: Source, Promoted Headers, Changed Type, Removed Columns, Filtered Rows, and so on. Rules to know:

  • Steps run top to bottom. Select any step to see the data as it was at that point.
  • You can rename, reorder, edit (gear icon) and delete steps. Deleting an early step may break later ones that refer to its columns.
  • The source data is never modified. The query reads and produces a new table.
  • Removing a column and later adding it back usually means the later steps need repair; give columns the right name early.
  • Prefer filtering by values you choose over removing the first N rows by position if the layout may change.

Common one-click tools, all in Home and Transform:

Task Where
Use the first row as headers Transform > Use First Row as Headers
Change a column's type Click the type icon in the column header
Trim, clean, upper/lower case Transform > Format
Split a column Transform > Split Column (by delimiter or position)
Remove blank rows, duplicates, errors Home > Remove Rows
Replace values Transform > Replace Values
Fill down missing labels Transform > Fill > Down
Add a calculated column Add Column > Custom Column

Change the type deliberately. Power Query often adds an automatic Changed Type step; for code columns like ZIP codes make sure the type is Text, not Whole Number, or leading zeros vanish.

Append: stacking tables

Append Queries puts the rows of one table under another. Use it when you have the same columns for different periods, such as Jan, Feb and Mar sales tables. Choose Home > Append Queries, pick the tables, and columns are matched by name, not position. A column missing from one table is filled with null there. If you have a whole folder of files with the same layout, Get Data > From Folder > Combine & Transform appends them all, and new files are included on the next refresh.

pandas uses pd.concat for the same job.

Python Append: stack monthly tables (pd.concat)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Merge: joining tables

Merge Queries is a lookup that returns many columns at once, like VLOOKUP or XLOOKUP without formulas (compare VLOOKUP and Its Limits). Choose Home > Merge Queries, select the matching column in each table and pick a Join Kind:

  • Left Outer (all from the first, matching from the second): the usual choice for looking up details.
  • Inner (only matching rows).
  • Right Outer, Full Outer, and the "anti" kinds: Left Anti shows rows with no match, useful for finding missing keys.

The merge adds a column of nested tables; click the expand icon at its header and tick the columns you need. Join columns must have the same data type: text "101" does not match number 101, and a trailing space breaks a match, just like in a lookup formula. The comparison is exact and case sensitive in the merge dialog.

Python Merge: Left Outer, Inner and Left Anti (pd.merge)

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Unpivot: columns into rows

Wide reports, with one column per month, are easy to read but hard to analyze: a PivotTable or SUMIFS wants one Month column and one Amount column. Unpivot turns the wide layout into the tall one. Select the columns that identify each row (such as Product) and choose Transform > Unpivot Columns > Unpivot Other Columns. The other columns become Attribute and Value pairs; rename them to Month and Amount. "Unpivot Other Columns" is the safe choice, because new month columns added later are unpivoted automatically on refresh. The reverse, Pivot Column, creates columns from the values in one column.

pandas calls it melt.

Python Unpivot with melt, and pivot back

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Loading and refreshing

After Close & Load the output is an ordinary table connected to the query. Right-click it and choose Refresh, use Data > Refresh All, or open Data > Queries & Connections to manage everything. In Properties you can set Refresh every N minutes, Refresh data when opening the file and whether a query refreshes under Refresh All. Notes:

  • Do not type inside a loaded table. Your edits (including formulas inside its columns) are lost or misplaced on refresh. Put calculations in neighbouring columns or another sheet.
  • Column widths and formats can reset unless you tick Preserve column sort/filter/layout and Adjust column width options in the query's properties.
  • A connection only query loads nothing to a sheet. Use it for intermediate steps that other queries refer to. This keeps big workbooks smaller and tidy.
  • Queries can reference other queries (right-click > Reference) so that cleaning is done once and reused.
  • Name each query clearly (Sales_Clean) and keep the original file path in a parameter (see the next lesson).

External data, SQL and file sources

Power Query is not limited to files you open by hand. The same Get Data window connects to databases and whole folders, and the connection can be managed after you load it. This section covers the parts that matter in everyday work. Excel and Power Query cannot run on this site, so the steps are read-only, and the TryIt is a JavaScript stand-in of the "combine a folder" step.

Databases, ODBC and SQL statements

Data > Get Data > From Database offers SQL Server, Access, MySQL, PostgreSQL, Oracle and others; From Other Sources > From ODBC or From OLE DB reaches anything with a driver (for example a local SQLite file through the SQLite ODBC driver, once installed and configured as a DSN in the Windows ODBC Data Source Administrator).

Most connectors let you open Advanced options and type a SQL statement:

SQL
SELECT OrderID, CustomerID, Amount, OrderDate
FROM Orders
WHERE OrderDate >= '2025-01-01'
ORDER BY OrderDate;

Putting the filter in SQL makes the database do the work so only the needed rows travel into Excel. If you choose a table through the Navigator instead, Power Query often folds your later filters back into SQL automatically (check by right-clicking a step: View Native Query is available when folding works). Plain SQL typed in the box disables folding for later steps, so put the heavy filtering inside the SQL itself. Credentials are asked once per source and stored per user, not in the workbook; Data > Get Data > Data Source Settings lets you clear or change them. Never save a password inside a connection string you share.

Older workbooks use Data > Connections (the Connections pane) instead: Workbook Connections lists each connection, with Properties to refresh on open, refresh every N minutes, or enable background refresh. Query connections created by Power Query appear in Queries & Connections on the right of the sheet.

Making a file path a cell value

Hard-coded paths break when a workbook moves to another computer or folder. Keep the path in a worksheet cell and let the query read it:

  1. Put the path in a cell and make it an Excel Table or a named cell (for example FolderPath).
  2. Data > From Table/Range on that cell, then right-click the value > Drill Down, and rename the query FolderPath.
  3. In the data query's Source step, replace the quoted path text with FolderPath in the formula bar or Advanced Editor: Folder.Files(FolderPath).

Now one cell controls the source folder. For relative paths (the data folder sits next to the workbook), compute the folder with a worksheet formula such as =LEFT(CELL("filename"), FIND("[", CELL("filename")) - 1) & "data\", which returns the workbook's own folder (the file must be saved) and read it into the query the same way (Power Query M Language Basics).

Combine all files in a folder

Data > Get Data > From File > From Folder lists every file in a folder. Click Combine > Combine & Transform Data; Power Query picks a sample file, builds a function that transforms one file, and applies it to all of them, adding a column with the Source.Name of each file. New files dropped into the folder appear on the next Refresh, so monthly CSV exports need no manual copying. Make sure all files share the same columns; filter the folder list by Extension first (for example .csv) so stray files such as ~$ temp files do not break the combine.

JS What Combine Files does: one transform, applied to every file

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Query as the source of a PivotTable, and common text and date changes

Load a query as Only Create Connection and then Insert > PivotTable > From Data Model (or choose "Add this data to the Data Model" when loading) to build pivots on the result without keeping a copy of the data in a sheet. Everything refreshes together with Data > Refresh All. Typical small transformations at the end of a query:

  • Text: Transform > Format > Trim, Clean, Capitalize Each Word, Replace Values for typos; split columns by delimiter.
  • Dates: Transform > Date > Year, Month Name, Day of Week; change the column type with the correct locale (Change Type > Using Locale) when day and month order differ, for example 31/12/2025.
  • Types: set the correct data type for every column as the last step; a wrong type is the most common cause of refresh errors ("DataFormat.Error: We couldn't convert to Number").

Privacy levels and security

When one query combines data from different sources (a local file and a web page, say), Power Query asks you to set a Privacy Level for each: Private, Organizational or Public. This stops data from a private source from being sent to a public one as part of a request. If you see a Formula.Firewall error, it comes from this protection. Setting the correct levels, or restructuring so each query uses one source, fixes it. The Ignore privacy levels option exists in File > Options > Query Options > Privacy, but use it only with data you trust.

First-time use of a data source also asks you to choose credentials (Anonymous, Windows, Basic, Organizational account). Saved credentials are managed in Data > Get Data > Data Source Settings. A shared query that points at a private location will fail for people without access. Be careful with the file path of the source: if a colleague stores the file in a different folder, point the query to the new location (the Source step or a parameter).

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. What do the Applied Steps in the Power Query Editor represent?

    Each action is stored as a step. Refresh runs them again from the top, so new data gets the same treatment.
  2. 2. Which operation stacks the rows of two tables with the same columns?

    Append puts one table below another and matches columns by name. Merge joins tables side by side on a key.
  3. 3. You need to find orders that have no matching product. Which join kind helps most?

    Left Anti returns rows from the first table that have no match in the second, so it exposes missing keys.
  4. 4. Why choose Unpivot Other Columns rather than unpivoting the month columns you see?

    Unpivot Other Columns keeps the identifying columns fixed and treats everything else as values, so extra columns in future data are handled.
  5. 5. What happens to cells you edit by hand inside a loaded query table?

    The loaded table is output. Refresh rewrites it, so keep manual work outside the table.

Summary

  • Power Query records cleaning steps (Applied Steps) that Refresh replays on new data; the source is never changed.
  • Append stacks tables by column name; Merge joins on a key with Left Outer, Inner and Anti kinds; Unpivot Other Columns makes wide data tall.
  • Set column types deliberately, especially Text for codes, and make join columns the same type.
  • Loaded tables are output: do not type inside them; use connection-only queries for intermediate steps.
  • Privacy levels and credentials protect data when combining sources.

Next: Power Query M Language Basics.