Power Query The Essentials

What Is Power Query?

Power Query is Microsofts dataconnectivity and datapreparation engine that lives inside Excel, Power BI Desktop, and several other Microsoft products. It allows you to discover, connect, combine, and refine data from a wide variety of sources without writing code. The result is a clean, structured set of tables ready for analysis, reporting, or further transformation.

While the user interface is based on a series of clicks and dialogs, Power Query records every step as M code (the language behind the engine). This means you can view, edit, and versioncontrol the underlying script if you need to.

Key Benefits

Selfservice data prep: Business analysts can shape data without involving IT.
Reusable pipelines: Once a query is built, it can be refreshed with new source data.
Wide source support: Databases, web services, files, cloud storage, and more.
Automation ready: Works nicely with Power Automate, Azure Data Factory, and other services.

Getting Started

Open Excel (or Power BI Desktop) and use the Data tab Get Data. From there you can select the connector you need for example From File From Workbook, From Database From SQL Server, or From Web.

After the connection dialog, the Power Query Editor appears. The main areas are:

Queries pane a list of all tables/queries in the current file.
Data preview shows the first few rows of the selected query.
Applied Steps a chronological list of transformations you have performed.
Formula bar optional view of the M code for the selected step.

Common Transformations

1. Removing Unwanted Columns

Select the columns you want to keep, rightclick and choose Remove Other Columns. The generated step looks like:

Table.SelectColumns(Source, {"OrderID","Customer","Date","Total"})

2. Filtering Rows

Use the filter icons on column headers or the Keep Rows menu. Example:

Table.SelectRows(PreviousStep, each [Status] = "Closed")

3. Changing Data Types

Power Query automatically detects types, but you can enforce a type with Table.TransformColumnTypes:

Table.TransformColumnTypes(PreviousStep, {{"Date", type date}, {"Total", type number}})

4. Merging (Joining) Queries

Choose Home Merge Queries to combine two tables on a common key. The step resembles:

Table.NestedJoin(Orders, "CustomerID", Customers, "ID", "CustomerData", JoinKind.LeftOuter)

5. Appending (Union) Queries

Append is useful when you have similar tables from different files. The M code uses Table.Combine:

Table.Combine({January, February, March})

6. Pivoting & Unpivoting

Transform a set of rows into columns (pivot) or reverse it (unpivot). Example of unpivoting:

Table.UnpivotOtherColumns(Source, {"ID","Name"}, "Attribute", "Value")

Advanced Topics

Parameterizing Queries

You can create a Parameter (Home Manage Parameters) and reference it in a source step. This is handy for dynamic file paths or server names. Example:

Source = Csv.Document(File.Contents(ParameterFilePath),[Delimiter=",", Columns=5, Encoding=1252])

Using Custom Functions

Define a function once and invoke it for each row, similar to a userdefined function in Excel. A simple function that adds tax might look like:

let    AddTax = (price as number) as number => price * 1.07in    AddTax

Apply it with Table.AddColumn:

Table.AddColumn(PreviousStep, "PriceWithTax", each AddTax([Price]))

Connecting to APIs

Power Query can call REST APIs with Web.Contents. For instance, pulling JSON data from a public endpoint:

let    Source = Web.Contents("https://api.example.com/data"),    Json = Json.Document(Source),    Table = Record.ToTable(Json)in    Table

Performance Tips

Filter early reduce the number of rows as soon as possible.
Avoid using Table.Buffer unless you need to force caching.
Prefer native queries for large databases (push filter logic to the source).
Combine steps that can be performed together, e.g., select columns and change types in one statement.

Publishing and Refreshing

When you finish shaping data in Power BI Desktop, click Close & Apply. The model stores the query definitions. In the Power BI Service you can schedule automatic refreshes (daily, hourly, etc.) as long as the data source credentials are provided.

In Excel, after loading a query to a worksheet or data model, you can refresh it manually (Data Refresh) or set it to refresh on open (Connection Properties Refresh control).

Version Control

Because each query is stored as M code, you can copy the code into a .txt file and place it under Git. In Power BI, the Export to .pbix file contains the full model, but for pure query versioning many teams keep a separate repository of M scripts.