Cleaning Data with Copilot and Power Query

Messy data is the number one reason dashboards go wrong. A total looks off, a chart shows "United States" and "USA" as two separate regions, or a date filter refuses to work because the dates are secretly text. Power Query is Power BI's built-in cleaning workshop, and with AI helping you, cleaning goes from tedious clicking to a guided conversation.

In this lesson you will learn the most common data problems, how to clean them in Power Query, and exactly how to use AI, whether Copilot inside Power BI or a free assistant in a tab, to plan and speed up the cleanup. This is where the course gets hands-on.

What You'll Learn

• The most common data-quality problems and why they break dashboards
• How Power Query records your cleaning as repeatable steps
• How to use AI to produce a cleaning plan and Power Query steps
• A repeatable audit-then-clean workflow you can reuse forever

Why Power Query Matters

Power Query is where you shape raw data before it reaches your charts. Its superpower is that every change you make, removing a column, fixing text, changing a data type, is recorded as a step in a list on the right (the "Applied Steps" pane). When your source file updates next month, you click Refresh and every step re-runs automatically. You clean once, and it stays clean. AI helps you decide which steps to add and, with Copilot, can even add them for you.

Start With an Audit, Not a Fix

The biggest beginner mistake is fixing the first typo you see instead of understanding the whole mess first. Always audit first. Paste a sample of your data into a free assistant:

"Here are the column names and 15 sample rows from my Power BI dataset: [paste columns and rows] Act as a data-quality auditor. List every problem you see, grouped by column, and rank them by how badly they would break a dashboard. Then give me a step-by-step cleaning plan in the order I should do it."

You will get a prioritized checklist, for example: fix the Region inconsistencies, convert OrderDate from text to date, remove the 3 duplicate rows, fill or flag the blank Revenue cells. Now you clean with a map instead of guessing.

If you have Copilot in Power Query, you can instead ask it directly: "Describe the data quality issues in this query and suggest transformation steps." It reads your actual data and proposes steps you can apply with a click.

The Common Problems and Their Fixes

Wrong data types. If a column icon shows "ABC" but the values are dates or numbers, filters and math will fail. In Power Query, click the icon and choose the correct type (Date, Whole Number, Decimal). If a conversion errors, ask AI: "My OrderDate column is text like '2026-03-01' and '01/03/2026'. How do I convert it to a proper date in Power Query when the formats are mixed?"

Inconsistent text. "USA", "U.S.A.", and "United States" should be one value. Select the column, use Replace Values, or right-click for Transform options. For many variations, ask AI: "Give me a list of Replace Values pairs to standardize these region names to a single label each: [paste the messy values]." Paste its mapping and apply each replacement.

Extra spaces and casing. Leading spaces and random capitalization create fake duplicates. Use Transform > Format > Trim and Clean, and Capitalize Each Word or lowercase as needed.

Duplicate rows. Select the columns that define a unique record, then Remove Rows > Remove Duplicates. Ask AI which columns should define uniqueness if you are unsure: "In a sales table with OrderID, Date, Product, and Amount, which column or columns identify a truly duplicate row?"

Blank or missing values. Decide per column: fill with a default, replace with zero, or leave blank and handle it in a measure later. Ask AI: "8% of my Revenue column is blank. What are my options in Power Query and when should I choose each?"

Unneeded columns. Right-click and Remove columns you will never use. Fewer columns means a faster, clearer model.

Using Copilot to Do the Work

If you have Copilot in Power Query, you can go further than suggestions. Try:

"Trim and clean all text columns, convert OrderDate to a date type, standardize the Region column so 'USA' and 'United States' both become 'United States', and remove exact duplicate rows."

Copilot adds the corresponding steps to your Applied Steps list. Review each one, it labels them clearly, and delete any you disagree with. You stay in control; Copilot just does the typing.

A Reusable Workflow

Make this your routine for every new dataset:

1. Load with Get data, then Transform data.
2. Audit by pasting a sample into AI and asking for a ranked problem list and cleaning plan.
3. Fix types first (dates, numbers), because later steps depend on them.
4. Standardize text (trim, clean, replace variations).
5. Handle duplicates and blanks based on your AI-informed decision.
6. Remove junk columns.
7. Review the Applied Steps so you understand and trust each one.
8. Close & Apply to load the clean data into your model.

Because Power Query remembers every step, this whole cleanup re-runs automatically whenever the data refreshes. You built a cleaning machine, not a one-time fix.

Verify Before You Trust

After cleaning, sanity-check: does the row count make sense? Did removing duplicates delete more rows than expected? Do the totals look right? If anything is surprising, undo the suspicious step (just delete it from Applied Steps) and investigate. You can even paste your before-and-after row counts into AI and ask, "I had 12,000 rows and now have 9,500 after removing duplicates. Is that plausible, and how do I check I didn't delete real data?"

Key Takeaways

• Power Query records every cleaning action as a repeatable step, so you clean once and it re-runs on refresh.
• Always audit first: paste a data sample into AI for a ranked problem list and an ordered cleaning plan.
• Fix data types before anything else, then standardize text, handle duplicates and blanks, and remove junk columns.
• Copilot in Power Query can add cleaning steps for you from a plain-English request; free assistants give you the plan and the exact replacements.
• Verify row counts and totals after cleaning; delete any step you do not trust and investigate.