These four are the bread-and-butter column cleaners. Each solves a specific, recurring mess.
Split Column is for when one column holds two facts and you need them apart. "Pune, Maharashtra" becomes City and State, "2025-03-14T09:30" becomes a date and a time, and a full name becomes first and last. Select the column, then Transform ▸ Split Column ▸ By Delimiter (also on Home). Choose the delimiter, which can be a comma, a space, a custom character or a non-printing one. Then choose where to split: left-most, right-most, or each occurrence. Power Query adds one column per piece, and it also inserts a fresh Changed Type that you should review. Split Column ▸ By Number of Characters and ▸ By Positions handle fixed-width exports.
Merge Columns does the opposite and glues several columns into one. You might build a key like Region|Product, or reassemble Day/Month/Year. Select two or more columns in the order you want them joined, then Transform ▸ Merge Columns, pick a separator, and name the result. Use a visible separator like | or - when you build keys, just as you did for concatenated lookup keys when you learned lookups, so "12"+"3" can't collide with "1"+"23". Don't confuse this with Merge Queries, which joins whole tables. It's the same word for a different job, and you'll meet it in append vs merge in Power Query.
Trim and Clean kill invisible characters. Exported data is full of leading and trailing spaces, like "Delhi ", and of non-printing control characters such as tabs and line breaks. They make "Delhi " ≠ "Delhi", and they break every join and Group By without a sound. Select the column or columns, then run Transform ▸ Format ▸ Trim, which removes leading and trailing whitespace, and Transform ▸ Format ▸ Clean, which strips non-printing control characters. Run both on any text column you'll match, group or join on. One subtlety catches people out. PQ's Trim removes only leading and trailing spaces. It leaves alone the doubled internal spaces that the worksheet TRIM collapses, and for those you use Replace Values.
Advertisement
How does fill down work in Power Query?
Fill Down / Fill Up fills the gaps in reports where a label appears once at the top of a group and the cells below it are blank. That's the classic "merged-cell" look, and it's also the state you land in after some unpivots. Transform ▸ Fill ▸ Down copies each non-null value downward into the nulls beneath it until the next non-null, and Fill ▸ Up does the reverse. The fast path is a right-click on the column ▸ Fill ▸ Down. Fill Down is what turns a human-formatted report, where "North" is written once above ten blank cells, back into a proper table with "North" on all ten rows.