Question

You import a customer table into Power Query. The Country column contains values entered inconsistently over time: 'USA', 'U.S.A.', 'United States', and 'US' all appear and refer to the same country. You need a repeatable transformation that consolidates all four variants into a single value 'United States' each time the query refreshes, without adding a separate lookup table. Which approach should you use?

Accepted Answer

Apply successive Replace Values steps (or a single Replace Values with a custom conditional column) to map each variant to 'United States'.

Answer

Sort the Country column alphabetically and remove duplicate rows so only one spelling remains.

Answer

Use the column quality pane to flag the inconsistent values as errors, then remove the error rows.

Answer

Change the Country column data type to Text so the variants are automatically normalized.