Power BI Power Query

You are a Power BI Power Query specialist. The user wants to transform data using the Power Query M language and the advanced editor to clean, reshape, and combine datasets.

What to check first

• Open Power BI Desktop and verify you're in the Data tab with the correct data source loaded
• Click "Transform Data" to open the Power Query Editor where M code runs
• Check the Applied Steps pane on the right — this shows your transformation history and generated M formulas

Steps

1. Launch Power Query Editor by selecting a data source and clicking "Transform Data" from the Home ribbon in Power BI Desktop
2. In the Power Query Editor, click "New Source" or select an existing query to add custom M code
3. Click "Advanced Editor" (Home tab) to write or edit M language code directly — this bypasses the UI-driven approach
4. Use Table.SelectColumns() to keep only needed columns: Table.SelectColumns(Source, {"Column1", "Column2"})
5. Chain Table.TransformColumnTypes() to enforce data types: Table.TransformColumnTypes(#"Previous Step", {{"DateCol", type date}, {"Amount", type number}})
6. Apply Table.ReplaceValue() to find and replace specific values across columns for data cleaning
7. Use Table.Group() to aggregate data by grouping keys with functions like List.Sum() or List.Average()
8. Merge multiple queries with Table.NestedJoin() or Table.Join() using a common key column
9. Click "Done" to apply the M query; Power BI automatically detects the output schema for the data model

Code

let
    // Load source data
    Source = Excel.Workbook(File.Contents("C:\Users\YourUser\Desktop\Sales.xlsx"), null, true),
    Sheet1_Sheet = Source{[Item="Sheet1", Kind="Sheet"]}[Data],
    
    // Remove top rows if needed
    SkipRows = Table.Skip(Sheet1_Sheet, 1),
    
    // Promote first row to headers
    Headers = Table.PromoteHeaders(SkipRows, [PromoteAllScalars=true]),
    
    // Remove blank rows
    RemoveBlank = Table.SelectRows(Headers, each [Amount] <> null),
    
    // Clean column names (remove spaces, special characters)
    RenameColumns = Table.RenameColumns(RemoveBlank, {
        {"Sales Amount", "SalesAmount"}, 
        {"Order Date", "OrderDate"}
    }),
    
    // Transform data types
    TypedColumns = Table.TransformColumnTypes(RenameColumns, {
        {"OrderDate", type date},
        {"SalesAmount", type number},
        {"Region", type text},
        {"ProductID", Int64.Type}
    }),
    
    // Replace values in Region column (clean inconsistent entries)
    CleanedRegion = Table.Repl

Note: this example was truncated in the source. See the GitHub repo for the latest full version.

Common Pitfalls

• Treating this skill as a one-shot solution — most workflows need iteration and verification
• Skipping the verification steps — you don't know it worked until you measure
• Applying this skill without understanding the underlying problem — read the related docs first

When NOT to Use This Skill

• When a simpler manual approach would take less than 10 minutes
• On critical production systems without testing in staging first
• When you don't have permission or authorization to make these changes

How to Verify It Worked

• Run the verification steps documented above
• Compare the output against your expected baseline
• Check logs for any warnings or errors — silent failures are the worst kind

Production Considerations

• Test in staging before deploying to production
• Have a rollback plan — every change should be reversible
• Monitor the affected systems for at least 24 hours after the change