30.6 Data Cleaning and Add Column

Q56. What is your usual order of data-cleaning steps in Power Query?

Profile (column quality/distribution/profile on the entire data set) → shape (remove title/footer rows, promote headers) → text hygiene (Clean, Trim, case, standard spellings) → data types (Using Locale for Indian dates) → blanks and errors → duplicates → validity flags → restructure (split, unpivot) → combine (append/merge) → document steps. Trimming before removing duplicates matters, because "Pune " and "Pune" are different values.

Q57. How do you remove duplicates by a key, keeping the latest record?

Sort by the update timestamp descending, wrap it in Table.Buffer so the sort order is kept, then select the key columns (e.g. Order ID + Product ID) › Remove Duplicates. Power Query keeps the first row it meets for each key.

Q58. What is the difference between Trim and Clean?

Trim removes leading and trailing whitespace (not spaces inside the text). Clean removes non-printable control characters such as line feeds. Use both, then Replace Values or M to collapse double inner spaces.

Q59. Power Query shows "pune" and "Pune" separately, but the report shows one value. Why?

Power Query is case-sensitive, while the VertiPaq model compares text case-insensitively and keeps one spelling (whichever it met first). Fix case in Power Query, e.g. Capitalize Each Word.

Q60. How do you convert Indian dd-mm-yyyy dates correctly?

Right-click the column › Change Type › Using Locale › Date, English (India). Or set the file's locale in Options › Current File › Regional Settings. In M: Table.TransformColumnTypes(..., {{"Order Date", type date}}, "en-IN").

Q61. How do you turn "₹1,250" into a number?

Remove the ₹ symbol, "Rs.", commas and spaces (Replace Values, or Text.Remove(_, {"₹", ",", " "})), then change the type to Fixed Decimal Number. Removing all commas handles lakh-style grouping such as 2,40,000.

Q62. How do you standardise many spelling variants of a city?

Create a mapping table (Raw City → Clean City), merge it with a Left Outer join, expand Clean City and use it when it is not null ([Clean City] ?? [City]). It is easier to maintain than many Replace Values steps. Fuzzy matching is an option, but its results must be reviewed.

Q63. What is the difference between Remove Errors, Replace Errors and try … otherwise?

Remove Errors deletes rows with errors in the selected columns; Replace Errors substitutes a value such as null; try expr otherwise value handles errors inside a formula, and try alone returns a record with HasError and Error details for logging.

Q64. How would you handle outliers such as delivery time 0 or 500 minutes?

Agree business rules first, then add a Data Quality flag with a conditional column (e.g. ≤ 0 = invalid, > 180 = too long). Show the issues on a data-quality page and exclude flagged rows in measures or filters, rather than silently deleting them.

Q65. Explain Left Anti and Right Anti joins with an example.

Left Anti returns rows of the first table with no match in the second, e.g. order lines whose Product ID is missing from Product (bad codes). Right Anti returns rows of the second table with no match, e.g. products never sold or dark stores without orders.

Q66. Split into columns or split into rows – when do you use each?

Use columns when every value has the same fixed number of parts (e.g. Store ID BLK-PUN-KOT-01). Use rows when the number of parts varies, e.g. a list of items in one cell, because split into columns fixes the number of columns when the step is created.

Q67. What is the difference between the Transform tab and the Add Column tab?

Transform commands change the selected column in place; Add Column commands create a new column and keep the original. Many commands (Format, Extract, Date, Standard) exist on both tabs.

Q68. What does Invoke Custom Function do?

It calls a Power Query function once for each row, passing column values as arguments and returning the result as a new column. It is used for reusable business rules (e.g. fnDeliveryBand) and for looping over pages or files.

Q69. Why is Number.Round(12.5) equal to 12 in Power Query?

The default rounding mode is round half to even (banker's rounding). Use Number.Round(x, 0, RoundingMode.AwayFromZero) for school-style rounding.

Q70. Should a new column be created in Power Query or DAX?

As far upstream as possible: in the source or Power Query for row-level columns (better compression, simpler model). Use DAX calculated columns when you need model features such as RELATED or values from other tables, and measures for aggregations.

Q71. What are implicit and explicit measures, and why prefer explicit ones?

Implicit measures are created automatically when you drag a numeric column into a visual (e.g. Sum of Amount). Explicit measures are written in DAX. Explicit measures are reusable, named and formatted once, and are required for some features. Set Summarization to Don't summarize for IDs and years.