📘 SERIES · CHAPTER 71📗 EXCEL · POWER QUERY

Excel Power Query for Data Analysts — Data Transformation, Automation & M Language

Power Query is the most underused tool in Excel for data analysts. It replaces hundreds of VLOOKUP formulas with repeatable, click-once-run-forever data transformation workflows. This chapter teaches everything from connecting your first data source through writing M language functions — the skills that make analysts 3× faster at data preparation.

⏱ 22 min read📅 September 2026✍ EVIKA ACADEMY, Noida

What Is Power Query and Why Every Analyst Needs It

Power Query (also called Get & Transform in Excel) is a built-in ETL (Extract, Transform, Load) tool that connects to data sources, cleans and reshapes data, and loads it into Excel — all without formulas or macros. Every step you take is recorded as a query that reruns automatically when you refresh. Change the source file? One click and your entire analysis updates.

🔄
Repeatable
Build the transformation once. Refresh with one click every time the source data updates.
🧹
No formula mess
Replace 200-row helper columns of VLOOKUP + TEXT + TRIM with a clean, documented query.
🔗
Multi-source
Combine data from CSV, Excel, SQL, SharePoint, web pages and APIs into a single clean table.
📖
Auditable
Every transformation step is listed. Anyone can open the query and see exactly what was done.
Where to find it: Excel → Data tab → Get & Transform Data → Get Data. Or: Data tab → From Table/Range to start from an existing Excel table. Power Query is available in Excel 2016 and later (and built into Power BI).

Connecting to Data Sources

Power Query can connect to dozens of source types. These are the ones analysts use most often:

SourcePathKey option to set
CSV / Text fileGet Data → From File → From Text/CSVSet delimiter, check data types in preview before loading
Excel workbookGet Data → From File → From Excel WorkbookChoose the specific sheet or named table to import
Folder (multiple files)Get Data → From File → From FolderCombine and Transform — loads all files in a folder as one table
SQL Server / DatabaseGet Data → From Database → From SQL ServerUse Import mode for speed; DirectQuery for always-live data
SharePoint listGet Data → From Online Services → From SharePoint ListPaste the site URL; authenticate with your Office 365 account
Web page / APIGet Data → From Other Sources → From WebPaste URL; for JSON APIs choose Table or Record from the navigator
Current Excel tableSelect table → Data tab → From Table/RangeFastest path — table must have headers and be formatted as an Excel Table

The 12 Most-Used Power Query Transformations

These are the transformations that appear in almost every analyst's Power Query workflow. Each is a one-click or two-click operation in the GUI — no code required.

01
Remove rows with errors / blanks
Home → Remove Rows → Remove Blank Rows or Remove Errors
Removes rows where any cell is null or has an error. Use "Remove Other Rows" to keep only non-blank rows in a specific column.
02
Change data type
Click column header → Data Type dropdown (top-left of header)
Set Text, Whole Number, Decimal, Date, Date/Time, True/False. Always fix data types before any calculation — numbers stored as text will not SUM.
03
Rename columns
Double-click column header
Rename to clean, consistent names before loading. Column names set here become your field names in pivot tables and Power BI.
04
Remove columns
Right-click column header → Remove OR right-click → Remove Other Columns
"Remove Other Columns" is safer — keeps only what you explicitly want rather than removing what you can see now.
05
Filter rows
Click dropdown arrow in column header → choose filter condition
Equivalent to Excel AutoFilter but applied at load time. Combine filters across multiple columns. Results update on refresh.
06
Split column by delimiter
Transform → Split Column → By Delimiter
Splits "First Last" into two columns, or "2026-09-25" into date parts. Choose Split: at each occurrence or at left/right-most.
07
Trim and clean text
Transform → Format → Trim (removes leading/trailing spaces) or Clean (removes non-printable characters)
Run both on every text column from external sources. Hidden spaces are the #1 cause of broken VLOOKUPs and GROUP BY mismatches.
08
Replace values
Transform → Replace Values
Find "N/A" and replace with null; fix category name inconsistencies ("Bangalore" vs "Bengaluru"); standardise date formats.
09
Add a custom column
Add Column → Custom Column → write M expression
Calculate new fields: profit = [Revenue] - [Cost], full name = [First] & " " & [Last], age bucket = if [Age] < 30 then "Junior" else "Senior".
10
Group By (aggregate)
Transform → Group By
Equivalent to a pivot table. Group by one or more columns, then choose aggregate: Sum, Count, Average, Min, Max, or All Rows.
11
Pivot / Unpivot column
Transform → Pivot Column or Unpivot Columns
Unpivot: converts wide data (Jan, Feb, Mar as columns) to tall data (Month, Value). Essential before loading into pivot tables or Power BI.
12
Use First Row as Headers
Home → Use First Row as Headers
When headers arrive as the first data row (common with CSV exports and database dumps). Run this before setting data types.

Merge and Append — Combining Multiple Tables

Two operations replace most multi-table complexity: Merge (like a SQL JOIN) and Append (like a SQL UNION ALL). Understanding when to use each is one of the most valuable Power Query skills.

🔗 Merge Queries
Like a SQL JOIN. Combines columns from two tables based on a matching key.
When to use: Adding lookup columns — e.g. joining a Sales table with a Product master to bring in Category.
How: Home → Merge Queries → choose second table → select matching column(s) → choose join type (Left, Inner, Full)
After merging: Click the expand icon (⇔) on the new column to choose which columns to bring in.
📋 Append Queries
Like a SQL UNION ALL. Stacks rows from multiple tables with the same structure.
When to use: Combining monthly files — Jan.csv + Feb.csv + Mar.csv into one table. Or stacking sales data from multiple regions.
How: Home → Append Queries → choose Two Tables or Three or More → select tables
Pro tip: Use "From Folder" source instead of Append when you have many files — it auto-appends all files in the folder on refresh.
Column name matching in Append: Power Query matches columns by name, not position. If one file has "Revenue" and another has "revenue" (lowercase), they will appear as two separate columns after the append. Standardise column names before appending — use a rename step in each table query first.

M Language — Writing Power Query Code

Every step you take in the Power Query GUI generates M language code behind the scenes. You can view and edit it in the Advanced Editor. You do not need to write M from scratch for most tasks, but knowing the basics unlocks transformations the GUI cannot do.

M LANGUAGE — ANATOMY OF A POWER QUERY QUERY
let
Source = Excel.Workbook(File.Contents("C:\Data\sales.xlsx"), true),
Sheet1_Table = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
PromotedHeaders = Table.PromoteHeaders(Sheet1_Table, [PromoteAllScalars=true]),
ChangedTypes = Table.TransformColumnTypes(PromotedHeaders, {{"Date", type date}, {"Revenue", type number}}),
FilteredRows = Table.SelectRows(ChangedTypes, each [Revenue] > 0),
AddedProfit = Table.AddColumn(FilteredRows, "Profit", each [Revenue] - [Cost], type number)
in
AddedProfit

10 Useful M Functions for Analysts

FunctionWhat it doesExample
Text.Trim([col])Remove leading/trailing whitespaceText.Trim([Customer Name])
Text.Upper([col])Convert text to uppercaseText.Upper([City])
Text.Contains([col], "x")Returns true/false if text contains substringText.Contains([Product], "Pro")
Date.Year([col])Extract year from a dateDate.Year([Order Date])
Date.Month([col])Extract month number (1–12)Date.Month([Invoice Date])
Number.Round([col], 2)Round to N decimal placesNumber.Round([Rate], 2)
if [col] > 100 then "High" else "Low"Conditional logicif [Sales] > 50000 then "Gold" else "Silver"
List.Sum([col])Sum a listRarely used in Add Column — use Group By instead
Table.RowCount(tableName)Count rows in a tableTable.RowCount(Source)
Duration.TotalDays([end] - [start])Days between two datesDuration.TotalDays([Delivery] - [Order Date])

Automating Refresh — Making Your Reports Self-Updating

The biggest productivity gain from Power Query is that you build the transformation once and it runs automatically every time you refresh. Here is how to set up different refresh scenarios:

Scenario: Daily sales report from a CSV that is re-exported each day
Keep the CSV filename and folder path consistent. Power Query connects to the path, not the file contents. When the file is replaced with today's data, one click on Refresh All updates the entire report.
How: Data tab → Refresh All. Or set a keyboard shortcut: Alt + F5.
Scenario: Monthly files in a folder (Jan.csv, Feb.csv, ...)
Use the From Folder connector. Power Query combines all files matching the folder pattern. When you add March.csv to the folder, it appears automatically in the next refresh.
How: Get Data → From File → From Folder → navigate to the folder → Combine → Combine & Transform.
Scenario: Live database connection (SQL Server, MySQL)
Use Import mode for scheduled refresh (fast, cached). Use DirectQuery for always-live data — every pivot table interaction queries the database directly. Import is usually the right choice for analysis.
How: Get Data → From Database → enter server/database → choose Import or DirectQuery.
Scenario: Scheduled refresh in Excel Online / SharePoint
Save your Excel workbook to OneDrive or SharePoint. Use Power Automate to trigger a refresh at a set time each day. This requires Power BI for full scheduling — Excel Online supports basic manual cloud refresh.
How: Power BI: publish the workbook → Dataset settings → Scheduled Refresh → set time and frequency.

6 Power Query Mistakes That Break Reports

✗
Hardcoding file paths
Fix: If you write C:\Users\John\Desktop\sales.xlsx and share the file with anyone else, the query breaks instantly. Use a parameter for the file path, or store files in a shared SharePoint folder from day one.
✗
Not setting data types after connecting
Fix: Power Query guesses data types on connection — it gets dates, numbers, and text wrong at least 30% of the time. Always open the Applied Steps pane and verify the "Changed Type" step immediately after connecting.
✗
Relying on column position instead of column name
Fix: If you use Table.RemoveColumns with a column index (0, 1, 2), adding a new column to the source breaks the query. Always reference by column name.
✗
Loading every column from the source
Fix: Loading 80 columns when you need 12 makes the file large and the refresh slow. Add a "Remove Other Columns" step early to keep only the columns your analysis needs.
✗
Too many queries, no structure
Fix: Create staging queries (one per source table, transformations only) and output queries (merges, final calculations). Prefix names: "stg_Sales", "stg_Products", "out_Dashboard". Future-you will thank you.
✗
Not using parameters for dynamic inputs
Fix: If your report needs to filter by month or region, a hardcoded filter breaks every month. Use Power Query Parameters (Home → Manage Parameters) so filters can be changed without editing the query.

Master Excel & Power Query at EVIKA ACADEMY

Our Excel course in Noida Sector 51 covers Power Query in depth — connecting data sources, cleaning transforms, M language, merge and append, and building self-updating dashboards. Online & offline classes available.

📱 Book Free Demo Class →

Frequently Asked Questions

What is Power Query in Excel?

Power Query is a built-in data transformation tool in Excel that connects to data sources — CSV, Excel, databases, SharePoint, web APIs — and cleans, reshapes, and loads data automatically. Every transformation step is recorded and replays on refresh, so you build the pipeline once and it runs with one click each time your source data updates.

Do I need to know coding to use Power Query?

No. Most Power Query tasks are done through a point-and-click GUI. The tool records each action as M language code behind the scenes. You only need to write M code for advanced transformations the GUI cannot handle, such as complex conditional logic or dynamic file paths.

What is the difference between Power Query Merge and Append?

Merge combines columns from two tables based on a matching key — like a SQL JOIN. Use it when adding lookup columns from a second table. Append stacks rows from multiple tables with the same structure — like a SQL UNION ALL. Use it when combining the same type of data from multiple files or sheets.

How is Power Query different from VLOOKUP?

VLOOKUP is a formula that recalculates in the worksheet — slow on large datasets and breaks when columns shift. Power Query Merge runs at load time, handles millions of rows efficiently, supports full join types, and the transformation is documented as steps. For lookups on more than a few thousand rows, Merge in Power Query is the better choice.

Where can I learn Power Query in Noida?

EVIKA ACADEMY in Noida Sector 51 teaches Power Query as part of its Advanced Excel and Data Analytics courses. Topics include data source connections, core transformations, Merge/Append, M language basics, and self-refreshing dashboards. Online and offline classes are available. WhatsApp +91-8081035456 to book a free demo.

🎓 Free Demo Class — Online & Offline · Noida Sector 51