Step 1: Connect to the Source Instead of Copying It

Open Excel and go to the Data tab. Use Get Data to connect to the original source wherever possible.

Common business sources include:

• Excel workbooks
• CSV and text files
• Folders containing repeated files
• SQL databases
• SharePoint-hosted files
• Web-based structured data

Step 2: Inspect the Data Before Transforming It

Before making changes, understand the structure. Check whether each column means what you expect and whether the data type is appropriate.

Review:

• Column names
• Text, number and date types
• Blank values
• Error values
• Unexpected categories
• Duplicate keys

Step 3: Clean the Data with Recorded Steps

Use the Power Query Editor to turn manual cleaning into a documented sequence of transformations.

Typical steps include:

• Remove unnecessary columns
• Rename fields consistently
• Trim spaces from text
• Standardise upper or lower case where appropriate
• Replace known inconsistent values
• Filter invalid or irrelevant records
• Set correct data types

A simple M example might look like this:

let
    Source =
        Excel.CurrentWorkbook()
            {[Name="tblSales"]}[Content],

    ChangedTypes =
        Table.TransformColumnTypes(
            Source,
            {
                {"OrderDate", type date},
                {"CustomerID", type text},
                {"SalesValue", type number}
            }
        ),

    TrimmedCustomer =
        Table.TransformColumns(
            ChangedTypes,
            {
                {
                    "CustomerID",
                    Text.Trim,
                    type text
                }
            }
        ),

    ValidSales =
        Table.SelectRows(
            TrimmedCustomer,
            each [SalesValue] <> null
        )
in
    ValidSales

Step 4: Merge Lookup Data Instead of Using Repeated VLOOKUPs

Suppose your sales file contains a Customer ID but the customer region and account manager live in a separate table.

Use Merge Queries to join the two datasets.

Sales Data
CustomerID | OrderDate | SalesValue

Customer Master
CustomerID | CustomerName | Region | AccountManager

Merge using:
Sales[CustomerID]
=
CustomerMaster[CustomerID]

You can then expand only the fields needed for reporting.

Step 5: Combine Monthly Files Automatically

One of the most useful Power Query patterns is importing every compatible file from a folder.

Imagine a folder containing:

Sales_2026_01.xlsx
Sales_2026_02.xlsx
Sales_2026_03.xlsx
Sales_2026_04.xlsx

Connect to the folder rather than importing each workbook separately.

When next month's file arrives:

• Place it in the same controlled folder
• Keep the expected structure consistent
• Refresh the query
• Allow Power Query to combine it with the existing data

Step 6: Add Business-Ready Fields Carefully

Some transformations belong naturally in the preparation layer. For example, you may want to create a clean reporting category from a source field.

Examples include:

• Standardised customer groups
• Clean product categories
• Reporting status flags
• Valid / invalid record indicators
• Year-month helper columns

Example logic:

If Status = "C"
then "Closed"

else if Status = "O"
then "Open"

else
"Unknown"

Step 7: Refresh Instead of Rebuilding

Once the query has been tested, load the result into the destination required by the reporting process.

That could be:

• An Excel table
• A PivotTable
• The Excel Data Model
• A staging workbook
• A downstream Power BI process

On the next reporting cycle, update the source and refresh. Power Query repeats the recorded transformation sequence.