Introduction
If you repeat the same task every month, this article is for you. Open a CSV, delete unneeded columns, fix formats, append two tables, rebuild a pivot... Doing this by hand every time is not only a waste of time but a breeding ground for mistakes.
**Power Query** is a data transformation tool built into Excel (and Power BI). Once you "record" a transformation process, the next month you simply swap in the source file and apply the same cleaning automatically with a single **Refresh** button. That works because every step you created with clicks is re-executed.
In this article, we cover the concept of Power Query (ETL) and its core transformations, combining folders and merging queries, parameters, a taste of the M language, and the limits of refresh automation along with real-world pitfalls, step by step.
> Power Query works most completely in desktop Excel on Windows, while the Mac and web versions have some limited functionality. The menu location is the "Get & Transform Data" group on the "Data" tab.
1. What Is Power Query — ETL with Clicks
Power Query is a classic **ETL** (Extract, Transform, Load) tool.
- **Extract**: Bring in data from files, folders, the web, and databases.
- **Transform**: Clean columns, change formats, split/merge, pivot, and more.
- **Load**: Export the cleaned result to a worksheet or the data model.
The key point is that **every operation is recorded as a step**. In the "Applied Steps" pane on the right, each transformation stacks up in order, and these steps are re-executed from top to bottom on every refresh.
Source data ──► [Step 1: Remove columns] ──► [Step 2: Change type]
──► [Step 3: Split] ──► [Step 4: Filter] ──► Result table
Do it by hand once, and that procedure is saved permanently. Swap in next month's data and the same procedure runs exactly as before.
2. Importing Data
From "Get Data" on the "Data" tab, you can choose from a variety of sources.
| Source | Use |
| --- | --- |
| File (workbook/CSV/text) | Cleaning a single file |
| Folder | Combining multiple files of the same format |
| Web | Web page tables / API responses |
| Database (SQL Server, etc.) | Direct connection to large datasets |
When you choose to import, a preview window appears, and pressing "Transform Data" takes you into the Power Query editor. From here, all the processing begins.
3. Core Transformations — Cleaning with Just Clicks
Here are the transformations you will use most often in the Power Query editor. All are available through menu clicks, with M code generated automatically behind the scenes.
Changing Types
Explicitly assign the data type (text, integer, date, etc.) for each column. If the type is inferred incorrectly, later calculations go astray, so this is the first step to check.
Splitting and Merging Columns
- **Split**: Divide one column into several based on a delimiter (comma, space, etc.) or a character count.
- **Merge**: Combine several columns into one with a delimiter.
Splitting one "Name" column into "Last name" and "First name," or merging "State/Province" and "District" to build an address, takes only a few clicks.
Pivot and Unpivot
- **Pivot**: Spread row values into column headers.
- **Unpivot**: Fold several columns into two "attribute-value" columns.
**Unpivot** in particular is used very often in practice. It turns a table where months are laid out horizontally, like "January, February, March...," into the analysis-friendly vertical form of "month, value."
[ Pivot form ] [ Unpivot form ]
Dept Jan Feb Dept Month Value
Sales 100 120 ──► Sales Jan 100
Dev 80 90 Sales Feb 120
Dev Jan 80
Dev Feb 90
Grouping
Group by a key column to aggregate sums, averages, counts, and so on. It is similar to a PivotTable, but the difference is that it becomes a step in the transformation pipeline and is included in the refresh.
Conditional Columns
Fill a new column's values according to rules. For example, classify sales as "Excellent" if they are at or above a certain threshold and "Average" if below. You define the rules in a dialog box without complex nested IFs.
4. Combining Multiple Files from a Folder
Power Query's real power comes from **combining folders**. If files of the same format pile up in one folder each month, you can combine the entire folder at once.
1. Specify the folder path with "Get Data → From Folder"
2. A list of all files in the folder is displayed
3. Pressing "Combine & Transform" generates transformation rules based on a sample file
4. The same transformation is applied automatically to all files and merged into a single table
Just drop next month's file into the folder and refresh, and the new file is included automatically. The chore of "copying and pasting monthly files by hand" disappears entirely.
5. Merging and Appending Queries — Joins
Append
**Stack tables of the same structure vertically.** Combine the Q1 and Q2 tables to build a first-half table, for instance. It is the same concept as SQL's UNION.
Merge
**Connect two tables horizontally** based on a key column. This corresponds to SQL's JOIN, like attaching a customer table to an order table to bring in customer names. You can also choose the type of join.
| Join type | Result |
| --- | --- |
| Left outer | All of the left + matching right |
| Inner | Only rows that match on both sides |
| Full outer | All of both sides |
Work that you used to pull value by value with VLOOKUP/XLOOKUP can be handled reliably and quickly with a single merge.
6. Parameters — Flexible Queries
With parameters, you can change values like paths, dates, and thresholds from outside the query. If you factor out the file path as a parameter, you can reuse the same query in a different environment just by changing the path. If you make the threshold a parameter, you adjust the "reference amount" in one place and it applies to the entire filter.
Parameters are defined under "Home → Manage Parameters," and in the transformation steps you specify a reference to the parameter instead of the value.
7. A Taste of the M Language
Every step you create with clicks is actually saved as **M language** code. Open the "Advanced Editor" to see the full code, which you can also edit directly. Here is a simple example.
let
Source = Csv.Document(File.Contents("C:\data\sales.csv")),
Promoted = Table.PromoteHeaders(Source),
ChangedType = Table.TransformColumnTypes(Promoted, {{"Sales", Int64.Type}}),
Filtered = Table.SelectRows(ChangedType, each [Sales] > 1000)
in
Filtered
Each line corresponds to one "Applied Step." Within the `let ... in` structure, each step takes the previous result as input and defines the next transformation. Clicks are enough most of the time, but when you need iterative logic or dynamic processing, handling M code directly becomes far more powerful.
8. Refresh Automation and Its Limits
Automatic Refresh Options
In the query properties, you can set the following.
- Refresh when the file opens
- Background refresh at a set interval (in minutes)
- "Refresh All" (Ctrl + Alt + F5) to update all queries at once
This way, you only update the source, and the report stays current when you open the file.
Limits
- **Fully unattended automation is hard**: A refresh in desktop Excel ultimately requires someone to open the file or a macro/scheduler to run. For truly server-side automatic updates, the Power BI service or a cloud environment is a better fit.
- **Performance**: When there are many steps or the data is large, the refresh slows down. It is best to remove unnecessary columns early and move filters to earlier steps.
- **Credentials**: Database and web sources may require authentication on every refresh.
9. Relationship with Dynamic Arrays and Python in Excel
Power Query is strong at the "import and clean the data" stage. The **calculation and summarization** after importing is lighter and more immediate with dynamic array functions (`FILTER`, `GROUPBY`, etc.). The two are not in competition but divide the roles.
- Power Query: Cleaning, integration, format standardization (heavy ETL)
- Dynamic arrays: Fast calculation and summarization on the sheet
- Python in Excel: Take data cleaned by Power Query and run advanced statistics, machine learning, and visualization
A typical flow is "build a clean table with Power Query → summarize with dynamic arrays or analyze with Python."
10. Real-World Example — Automating a Monthly Report
Suppose monthly per-branch sales CSVs pile up in a single folder.
1. Import from the folder and combine all branch files
2. Standardize date and amount formats with Change Type
3. Remove unnecessary columns and filter out blank rows
4. Classify the sales grade with a conditional column
5. Merge with the customer master table to add regional information
6. Load the result to a worksheet
7. Build a summary dashboard with dynamic arrays/pivots
8. Set "Refresh on open" in the query properties
The next month, simply dropping the new CSV into the folder and opening the file refreshes every report. The pipeline you built once does the work for you each month.
11. Common Pitfalls — Data Types and Locale
- **Do not trust automatic type inference**: Power Query sometimes infers a type from only the first few rows and then breaks further down. Place an explicit Change Type step.
- **Locale (region) issues**: The day/month order in dates and the decimal/thousands separators differ by region. You must specify the source's region with "Change Type Using Locale" so that January 3rd does not turn into March 1st.
- **Dependence on column-name changes**: If the source's column names change, later steps break. When possible, rename to stable names right after promoting headers.
- **Hidden whitespace and special characters**: Invisible whitespace can throw off merges and filters. Apply the "Clean" and "Trim" transformations early.
- **No relative paths**: If a file/folder path is hard-coded as an absolute path, it breaks on another PC. Factoring the path out into a parameter improves portability.
Closing
The value of Power Query lies in "build it once and the repetition disappears." Record the cleaning process you used to do by hand as steps, and each month a single refresh gives you the same result. Mistakes decrease, the procedure is documented, and handoffs become easier.
Start with cleaning a single CSV at first. Once you are comfortable, expand into combining folders and merging, and finally, once you touch parameters and M code, most repetitive reports can be finished in one click. Divide the roles — cleaning with Power Query, summarizing with dynamic arrays, analysis with Python — and Excel becomes a powerful data workbench.
References
- [About Power Query in Excel (Microsoft Support)](https://support.microsoft.com/en-us/office/about-power-query-in-excel-7104fbee-9e62-4cb9-a02e-5bfb1a6c536a)
- [Getting started with Power Query (Microsoft Learn)](https://learn.microsoft.com/en-us/power-query/power-query-what-is-power-query)
- [Import data from a folder (Microsoft Learn)](https://learn.microsoft.com/en-us/power-query/import-data-folder)
- [Merge queries (Microsoft Support)](https://support.microsoft.com/en-us/office/merge-queries-power-query-fd157620-5470-4c0f-b132-7ca2616d17f9)
- [Append queries (Microsoft Support)](https://support.microsoft.com/en-us/office/append-queries-power-query-e42ca582-4f62-4a43-b37f-99e2b2a4ba8e)
- [Pivot and unpivot columns (Microsoft Learn)](https://learn.microsoft.com/en-us/power-query/unpivot-column)
- [M language specification (Microsoft Learn)](https://learn.microsoft.com/en-us/powerquery-m/)
- [Python in Excel (Microsoft Support)](https://support.microsoft.com/en-us/office/get-started-with-python-in-excel-a33fbcbe-065b-41d3-82cf-23d05397f53d)
현재 단락 (1/98)
If you repeat the same task every month, this article is for you. Open a CSV, delete unneeded column...