This reader is doing something smart: they refuse to accept text masquerading as a date. That instinct alone puts them ahead of most spreadsheet users. But their frustration with nested M functions is a symptom of a deeper problem, Power Query's syntax is powerful, but it punishes anyone who tries to compress logic into a single line without understanding how the language handles types. The real issue here isn't complexity; it's that the reader is fighting M's type system instead of working with it.
The error they hit is predictable. In M, `Text.Start` and `Text.End` return text, and adding `+ 2000` to text produces an error because M does not coerce types automatically the way Excel does. Multiplying a text string by 1 in Excel works because Excel assumes you want a number. M assumes nothing, it demands explicit conversion. The fix is straightforward: wrap each text result in `Number.FromText()` before performing arithmetic. A single step like `= #date(Number.FromText(Text.Start([yearmonth],2)) + 2000, Number.FromText(Text.End([yearmonth],2)), 1)` will work. But that syntax is still clunky. The more elegant approach is to use `Date.FromText` with a custom format, which handles the entire transformation in one readable line: `= Date.FromText([yearmonth], [Format="yyMM"])`. That is the solution the reader should adopt, and it's the one that scales.
What this story reveals is a gap between what spreadsheet users expect and what Power Query delivers. Excel's forgiving nature lets people build habits that break in M. The reader's multi-column approach works, but it is tedious and brittle. It creates four intermediate columns for a single transformation, which clutters the query and slows maintenance. The column-from-example feature is a crutch, fine for quick fixes, unreliable for production data. The real lesson is that M rewards precision. Invest ten minutes learning `Number.FromText` or `Date.FromText`, and you eliminate entire classes of errors. That is not a knock on the reader; it is an observation about how tools shape our workflows.
We believe this reader already has the right mindset, they want clean, typed data. That puts them ahead of the majority. The next step is to stop treating Power Query like Excel with different menus. Learn to think in terms of types and explicit conversions. The payoff is immediate: no more broken dates, no more guessing whether a column will load correctly. For anyone parsing monthly reports, that is not a nice-to-have. It is the difference between a process that runs reliably and one that requires hand-holding every cycle. The solution exists. It just requires one small shift in how you approach the syntax.