Power query editor; renaming and re ordering of columns, finding anomalies

When working with data in Power BI, the Power Query Editor is where you clean and prepare your data before building reports.

Think of it like preparing ingredients before cooking

If the data is messy, your analysis will be wrong.

1️⃣ What is Power Query Editor?

Power Query Editor is the data transformation tool inside Power BI.

To open it:

1. Open Power BI Desktop
2. Click Home → Transform Data
3. Power Query Editor opens

Here you can:

• Rename columns
• Reorder columns
• Remove errors
• Detect anomalies
• Clean and transform data

2️⃣ Renaming Columns

🔹 Why Renaming is Important

Raw data often comes like this:

| CustNm | OrdDt     | Amt  |
|--------|----------|------|
| John   | 1/2/2023 | 200  |
| Jane   | 1/3/2023 | 350  |

These names are unclear.

After renaming:

| Customer Name | Order Date | Amount |
|--------------|------------|--------|
| John         | 1/2/2023   | 200    |
| Jane         | 1/3/2023   | 350    |

Now the data is:

• Easier to understand
• Easier to analyze
• More professional

🔹 How to Rename Columns (Beginner Method)

1. Right-click the column header
2. Click Rename
3. Type new name
4. Press Enter

Power BI automatically creates M code in the background.

🔹 M Code for Renaming Columns

= Table.RenameColumns(Source, {
    {"CustNm", "Customer Name"},
    {"OrdDt", "Order Date"},
    {"Amt", "Amount"}
})

Explanation:

• "CustNm" is the old name
• "Customer Name" is the new name

🔹 Best Practices for Naming Columns

✅ Use clear names

✅ Avoid special characters (#, %, @)

✅ Keep names consistent

✅ Avoid very short unclear abbreviations

3️⃣ Reordering Columns

🔹 Why Reorder Columns?

Sometimes data loads like this:

| Amount | Order Date | Customer Name | OrderID |
|--------|------------|--------------|--------|

But logically, it should be:

| OrderID | Customer Name | Order Date | Amount |
|--------|--------------|------------|--------|

Reordering:

• Improves readability
• Makes reports cleaner
• Organizes data logically

🔹 How to Reorder (Beginner Method)

Method 1:

• Click column header
• Drag and drop

Method 2:

• Right-click column
• Select Move → Left / Right / To Beginning / To End

🔹 M Code for Reordering

= Table.ReorderColumns(Source,
    {"OrderID", "Customer Name", "Order Date", "Amount"}
)

Important:

You must list ALL columns in the exact order you want.

4️⃣ Finding Anomalies (Very Important in Data Analysis)

🔍 What is an Anomaly?

An anomaly is:

• A strange value
• A suspicious number
• A data error
• Something outside the normal pattern

Example:

| OrderID | Amount |
|--------|--------|
| 1001   | 200    |
| 1002   | 250    |
| 1003   | 99999  |
| 1004   | -50    |

Possible problems:

• 99999 may be incorrect
• -50 may not make sense (negative sales)

5️⃣ How to Find Anomalies in Power Query Editor

✅ Method 1: Sort the Column

Click Amount → Sort Descending

This helps you quickly see:

• Very large values
• Very small values
• Negative numbers

✅ Method 2: Use Column Statistics

Go to:

View → Column Distribution

You will see:

• Minimum value
• Maximum value
• Average
• Distinct count

If max value is extremely high → investigate.

✅ Method 3: Filter Specific Values

Find Negative Values

= Table.SelectRows(Source, each [Amount] < 0)

Find Extremely Large Values

= Table.SelectRows(Source, each [Amount] > 10000)

✅ Method 4: Find Null (Missing) Values

Example data:

| OrderID | Amount |
|--------|--------|
| 1001   | 200    |
| 1002   |        |
| 1003   | 350    |

M Code:

= Table.SelectRows(Source, each [Amount] is null)

✅ Method 5: Detect Errors

If a number column contains text:

| Amount |
|--------|
| 200    |
| three hundred |
| 400    |

Power BI shows an error icon.

To filter rows with errors:

= Table.SelectRowsWithErrors(Source, {"Amount"})

6️⃣ Complete Practical Example

Raw Data:

| OrderID | CustNm | OrdDt     | Amt   |
|--------|--------|----------|-------|
| 1001   | john   | 1/2/23   | 200   |
| 1002   | jane   | 1/3/23   | -100  |
| 1003   | JOHN   | 1/4/23   | 50000 |
| 1004   | alice  |          | 300   |

Cleaning Steps:

1. Rename columns
2. Change data types
3. Reorder columns
4. Detect anomalies

Full M Code Example

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

    // Rename columns
    RenamedColumns = Table.RenameColumns(Source,{
        {"CustNm","Customer Name"},
        {"OrdDt","Order Date"},
        {"Amt","Amount"}
    }),

    // Change data types
    ChangedType = Table.TransformColumnTypes(RenamedColumns,{
        {"Order Date", type date},
        {"Amount", type number}
    }),

    // Reorder columns
    ReorderedColumns = Table.ReorderColumns(ChangedType,{
        "OrderID","Customer Name","Order Date","Amount"
    }),

    // Find negative values (anomalies)
    NegativeValues = Table.SelectRows(ReorderedColumns,
        each [Amount] < 0
    ),

    // Find extremely large values
    LargeValues = Table.SelectRows(ReorderedColumns,
        each [Amount] > 10000
    )

in
    ReorderedColumns

7️⃣ Cleaned Result Example

| OrderID | Customer Name | Order Date | Amount |
|--------|--------------|------------|--------|
| 1001   | John         | 2023-01-02 | 200    |
| 1004   | Alice        | 2023-01-05 | 300    |

8️⃣ Summary Table

| Task | Purpose | Why Important |
|------|---------|--------------|
| Rename Columns | Improve clarity | Easier understanding |
| Reorder Columns | Organize structure | Better readability |
| Sort Values | Identify extremes | Detect anomalies |
| Filter Values | Isolate unusual data | Prevent wrong analysis |
| Check Errors | Detect wrong data types | Improve data quality |

COMPILATION OF ALL CODE BLOCKS (Single Combined Script)