Professionals build this habit before any other. The very first thing a query should do, right after the source and header promotion, is set every column's data type explicitly. Power Query auto-inserts a Changed Type step when you first connect, but it guesses from a sample of the top rows. A guess made from the first 1,000 rows can be wrong for row 40,000. Take control of it.
Why first, and why does it matter so much? A column's type governs everything downstream. It decides whether Group By can sum it and whether a date sorts chronologically or alphabetically. It decides whether a split produces numbers or text, and whether the final load puts a real number in the cell or a number-shaped string that SUM ignores. Set types late and every intermediate step has been reasoning about the wrong kind of thing. Set them first and the rest of the recipe stands on solid ground.
Advertisement
How do you change a column's data type?
There are three equivalent ways to do it. One is the little type icon to the left of a column header (ABC = text, 123 = whole number, a calendar = date, 1.2 = decimal, $ = currency). Click it and pick from the menu. Another is to select the column or columns and use Transform ▸ Data Type ▸ …. The third is to right-click the header ▸ Change Type ▸ …. To type several at once, select them first by Ctrl-clicking headers or Shift-clicking a range.
The locale trap is the reason "set types first" matters so much, and Change Type with Locale is how you get out of it. A date written 03/04/2025 is 3 April in India/UK (dd/mm/yyyy) and 4 March in the US (mm/dd/yyyy). Same text, two different days. A number written 1.234 is one-point-two-three-four in the US and one-thousand-two-hundred-thirty-four in Germany. Click plain "Date" and Power Query interprets the text using your machine's locale, which may not be the file's. So fix it deliberately. Right-click the column ▸ Change Type ▸ Using Locale… (or Transform ▸ Data Type ▸ Using Locale…), then choose the data type and the locale the source was written in, e.g. English (India) for a dd/mm/yyyy export. That gives you a Changed Type with Locale step that reads the text correctly whatever machine it refreshes on. You'll need it for the India and US files you meet in the worked examples.
What order should the steps run in?
So the habit always runs in the same order. Promote headers first. Set types next. Use the locale wherever dates or decimals are involved. Only then start transforming. A query that re-types columns in the middle, or leaves them as any/text, is a query waiting to surprise you.