M Is For Data Monkey A Guide To The M Language In Excel Power Query

Getting Your Head Around M in Power Query

Power Query is built on a language called M. It lives in the Advanced Editor behind every transformation you click through the ribbon. Most people never look at it because the UI does the heavy lifting for them. That works until the UI stops working, which is sooner than you think. The language has grown out of proportion with how much attention it gets. It's not Visual Basic, it's not SQL, and it's not DAX. It uses a syntax that feels like a cross between Fand something made up internally at Microsoft. The main thing you need to know upfront is that every M query returns a table or a list, and everything between steps is just a function pipeline. I remember trying to import a CSV from a legacy ERP system where the header row shifted depending on how many columns had null values. The file had 14 columns one month and 12 the next because two fields were conditionally populated. Power Query's "use first row as headers" would break half the columns on the short months. I wrote a custom M step that scanned ahead past the first three rows, found the row where the count of non-empty values peaked, and promoted that as the header row. It looked like this:

let Source = Csv.Document(File.Contents("C:\data\legacy_export.csv"), [Delimiter=",", Columns=15, Encoding=1252]), PromoteRow = List.Max(List.Range(Source, 0, 3), each List.Count(List.RemoveNulls(_))),

Headers = Table.PromoteHeaders(Table.FromRows({PromoteRow}), [PromoteUseHeaders=true]), in Headers

The trick wasn't complex. It was just realizing that M lets you manipulate the raw row lists before the table structure locks in. One thing beginners consistently miss is that the query editor doesn't preserve your manually typed M code when you go back and click buttons afterward. I spent two mornings reworking a custom column formula because I added a "filtered rows" step from the UI after editing the Advanced Editor. Power Query rewrote the entire source step and dropped my inline function. The workaround is straightforward: finish all manual editing last, or keep a separate snippet file you paste from. I keep a text document with my common patterns—error handling, conditional promotion, custom type conversions—because rewriting them from memory wastes more time than you'd expect. Another counter-intuitive detail is how M handles null versus empty string. They are not interchangeable, and confusing them is the single most common source of bugs in production queries. A null means the value is absent. An empty string is a value you chose to represent as blank. If you're merging two tables and one uses null for missing data while the other uses "", the merge will fail to match rows even though they look identical to a human. The fix is usually a single Replace Values step converting null to "" or vice versa, but you have to do it consistently across both sources before the merge happens.

Performance is another area where the tool lies to you. The visual steps in Power Query give you the impression that everything is lazy-evaluated and efficient. It mostly is, but there are patterns that force early materialization and destroy performance on larger datasets. Loading the entire source into memory before filtering is one of them. If you pull a 500,000-row Excel file and then filter down to 2,000 rows, M still read all 500,000. The engine doesn't optimize that away automatically. The fix is to apply filters as close to the source as possible, which means doing it in the connection step rather than after the data is already in memory. For SQL sources, Power Query usually translates your steps into a WHERE clause, but for file-based sources like CSV and Excel, the filtering happens in-memory after the full load. Error handling in M is also underused. Most people let a bad row crash their entire query. The Error.Record approach lets you catch issues and route them to a log table instead. I set up a pattern where problematic rows get captured with their error details and the query continues rather than stopping dead. It looks like this: try your_expression otherwise #error

That construct alone saved me from rebuilding an entire ETL pipeline after a supplier started sending dates in a different format on rare rows. The downsides of M are real enough that you should know them before you commit to it. First, debugging is painful. The error messages are vague, often pointing you at a step number with no clear explanation of what went wrong. Second, M has no native support for looping. If you need iteration, you're writing recursive functions or restructuring your logic, which is harder than it sounds. Third, the documentation is incomplete. Microsoft covers the basics but skips a lot of the edge cases that actually matter in production work. You end up relying on community forums and trial-and-error more than official guidance. If your workflow involves simple transformations on structured files, Power Query with basic M is fine. For complex data engineering pipelines, you're better off using Python or SQL. M was designed for business analysts who don't want to write code, not for people building production-grade ETL. That distinction matters more than the tool itself.

The language will keep evolving. New functions get added periodically, and some of the older quirks get smoothed over. But the core behavior—the pipeline model, the type system, the way it handles nulls—hasn't changed significantly since it launched. Learning it properly means understanding those fundamentals rather than memorizing syntax.