📌 Stage 1 · Connect · Power Query · Types and locale · 35–45 min

Why 04/08/2026 is a lie until you pick a locale

Learning goals

  • Explain why every CSV value arrives as text and who assigns the real types
  • Convert a day-first date column with Changed Type with Locale instead of the default
  • Spot the silent-wrong-date failure mode and the error-out failure mode
  • Verify that all 13 invoices landed on their true dates in the Invoice Facts output

Concepts

Everything arrives as text

A CSV file is just characters. When Power Query imports one, every column is text until a step says otherwise -- that is why the Billing Export sheet shows '15/07/2026' and '$2,030.00' as strings. Power Query marks each column with a type icon in its header (ABC for text, 123 for whole number, a calendar for date). Clicking the icon and choosing Date is the fastest way to convert.

There is a trap on that path. The dropdown's plain 'Date' option parses with your machine's locale -- on a US install, month-first. Ledgerly's server is configured for the United Kingdom, so its dates are day-first: 04/08/2026 means August 4, and 15/07/2026 means July 15.

💡 Tips:

  • Turn off type-detection on load while you are learning: Query Options > Data Load > 'Never detect column types and headers for unstructured sources'. Undetected types are obvious text; silently detected types are the dangerous ones.

Changed Type with Locale

The correct click is the type icon's dropdown > Using Locale... > Data type Date, Locale English (United Kingdom). That writes Table.TransformColumnTypes with 'en-GB' as the third argument -- exactly what the Invoice Facts script does for both the Invoice Date and Paid Date columns.

Two failure modes tell you which locale you actually need. Day-first values with a day above 12 (31/08/2026, 15/07/2026) throw Error under the US locale -- loud, ugly, and useful. The killers are the values that fit both readings: 04/08/2026 parses happily as April 8 and quietly corrupts every period total downstream. Northgate's rule: one bad locale guess turned 'invoiced in August' into a wrong number that still looked plausible.

=DAY('Invoice Facts'!$C5)

Proves the parse landed where you think: INV-2401's invoice date is July 8, 2026, so DAY returns 8 and MONTH returns 7. If a US-locale parse had been applied to the day-first text 08/07/2026, this cell would show 7 -- the wrong month entirely.

=COUNTA('Invoice Facts'!$B$5:$B$1004)

Thirteen invoices made it through typing. If your own query returns fewer rows than the source, check for Error cells: Power Query drops nothing on its own, but a later 'Remove Errors' step (lesson 4) will.

Type once, at the end

Keep the type steps at the end of each query, after the cleaning steps. Text operations come first -- trim, replace, split -- because 'MS ' with a trailing space and 'MS' are different values to a merge key, but identical after Text.Trim. Type the column once it is clean.

The Invoice Facts output carries three typed columns from Ledgerly: Invoice Date and Paid Date as en-GB dates, and Hours, Rate, and Paid Amount as en-US numbers. The Amount text ($2,030.00) needs its dollar sign and commas stripped before it can become a number -- that is lesson 4's Replace Values work, repeated inside the Invoice Facts script.

💡 Tips:

  • Amounts with currency symbols are text everywhere in the world. Strip the symbol and the thousands separator in the query, never with Find and Replace on the sheet.
  • Keep one locale per column. If a single column genuinely mixes day-first and month-first rows, no locale can save you -- fix the exporting system instead.

Practice

The exercise opens on the raw Billing Export sheet (BEFORE). The AFTER state is Invoice Facts, where the same thirteen invoices carry real dates. Nothing is saved in the browser.

  1. Read the raw dates — Column C holds Invoice Date as day-first text: 08/07/2026, 15/07/2026, 22/07/2026, 30/07/2026, 04/08/2026, and so on through 02/09/2026. Column I (Paid Date) uses the same convention, with blanks where an invoice is still open.
  2. Sort the ambiguity — Cover column C and try to say which rows are July and which are August. Rows with days above 12 (15/07, 22/07, 30/07, 31/08) are unambiguous; 04/08/2026, 07/08/2026, and 08/07/2026 could be read either way. That ambiguity is why the locale must be a deliberate choice, not a default.
  3. Verify the AFTER state — Open Invoice Facts and check column C: INV-2401 is Jul 8, 2026; INV-2405 is Aug 4, 2026 (not April 8); INV-2412 is Aug 31, 2026; INV-2413 is Sep 2, 2026. Click any date cell and read the formula bar -- it is a real date, displayed as mmm d, yyyy.
  4. Count the period — Eight of the thirteen invoices are dated inside August 2026 (INV-2405 through INV-2412). INV-2413, dated Sep 2, 2026, sits in the ledger but outside the reporting window -- lesson 10's SUMIFS will exclude it automatically.

Online exercise

The spreadsheet below is editable — follow the practice steps right on this page. Steps that need ribbon menus should be done in your own Excel with the downloadable practice file.

表格加载中…

Checklist

Work through each item; when every box passes, this lesson is done:

  • ☐ Converted (or planned) both Ledgerly date columns with Changed Type with Locale, English (United Kingdom)
  • ☐ Verified INV-2405 landed on Aug 4, 2026 and INV-2412 on Aug 31, 2026 in the Invoice Facts output
  • ☐ Confirmed 13 invoices survived typing, 8 of them dated inside August 2026
  • ☐ Verified the baseline: 13 rows staged and typed, the invoice total across all of them being 24,805.00 before any period filtering