← Previously: Power Query from Scratch, Part 3: The transforms that actually matter (16 Jul) → Next: Power Query from Scratch, Part 5: Parameters, functions and a model that refreshes itself (30 Jul)
What this post covers: the moment the ribbon runs out and you type a custom column formula by hand, what that formula actually is (your first line of M), and the three ways it breaks for every beginner - a capital letter, a missing else, and null.
The chase list, and the button that doesn't exist
Three parts into this series you have imported, combined, unpivoted, split and grouped, and you have not typed a single formula. Every transform so far has been a button. This is the part where that stops, because it always stops, and it usually stops on something that looks insultingly small.
The scenario: an aged receivables export from your finance system. One row per customer, a Balance column, a Days Overdue column. You need a chase list: flag every customer where the balance is over 500 and the invoice is more than 30 days overdue. Both conditions, together. This is not an exotic request; finance asks for this list every month.
Power Query has a button that looks like it should do this. Add Column > Conditional Column gives you a friendly dialog: if this column, is greater than, this value, then output this. You can even stack Else If clauses. What you cannot do is say "and". Each rule tests exactly one column against exactly one thing. The dialog for "flag balances over 500" exists. The dialog for "flag balances over 500 where the invoice is also a month overdue" does not.
You can fake it - one conditional column for the first test, a second conditional column that tests the flag plus the other condition, then delete the helper. It works the way standing on a chair that is standing on a table works. The moment a third condition arrives, or the output needs a calculation instead of fixed text, the whole arrangement folds.
This is the moment the UI runs out. It is also the moment Power Query stops pretending it isn't a programming tool.
Your first custom column
Add Column > Custom Column. The dialog is three things: a name box, a list of your columns on the right, and a large, blank, slightly threatening formula box. Here is what goes in it:
if [Balance] > 500 and [Days Overdue] > 30 then "Chase" else "Wait"Double-click column names in the list to insert them, or type them. Reading it piece by piece:
[Balance]is a column reference. When the query runs, it means "this row's Balance". Spaces are fine inside the brackets -[Days Overdue]needs no escaping, no underscores, no apology.>andanddo what they say.andis the actual word, lowercase. Same fororandnot."Chase"is text, in double quotes. Double quotes only - single quotes are a syntax error in M, which surprises everyone arriving from another language.if ... then ... else ...is the whole decision. All lowercase, and the else is not optional. More on both shortly, because they will bite you.
Under the formula box the dialog reports "No syntax errors have been detected." with a reassuring tick. Click OK. A new Chase column appears and every row says Chase or Wait. You have now written a line of M, the language Power Query runs on, and nothing exploded.
Two small notes before moving on. Check the new column's header icon: if it shows ABC123, Power Query hasn't typed it, so set it to Text yourself, the same as any other column. And the name M is short for mashup, which tells you roughly how much marketing was in the room when the language got named.
You've been writing M since Part 1
Here is the part I wish someone had shown me in my first week instead of my first year. With the Chase step selected, look at the formula bar above the data. (No formula bar? View tab, tick Formula Bar.) It reads:
= Table.AddColumn(#"Changed Type", "Chase", each if [Balance] > 500 and [Days Overdue] > 30 then "Chase" else "Wait")Your formula is in there, wrapped in a function called Table.AddColumn. Now click through the earlier steps in this query, or any query from the last three parts. Table.UnpivotOtherColumns. Table.SplitColumn. Table.Group. Every button you have ever clicked wrote a line like this. The UI is a code generator. You have been writing M since Part 1; someone else was doing the typing.
Two pieces of the generated line are worth decoding now. #"Changed Type" is the name of the previous step - step names with spaces get the #"..." wrapper, which is why the formula bar and the Applied Steps list always agree with each other. And each means "run this once per row". That is the working mental model for now. (Technically each is shorthand for a small function that receives the row, which is exactly the kind of thing Part 5 exists to explain. "Once per row" will carry you until then.)
If you are feeling brave: View > Advanced Editor shows the whole query as one block of M, every step chained together, each one feeding the next - the same pipeline you have been reading in Applied Steps all along. Don't edit anything. Close it. The point of looking today is that it stops being scary before the day you need it.
The three ways it breaks first
Everyone's first custom column works. It is the second one that produces the error, and it is nearly always one of these three.
A capital letter. M is case-sensitive, everywhere. The keywords are lowercase: if, then, else, and. Type If and the dialog's reassuring tick turns into a syntax complaint. Functions have exact casing too: it is Text.From, and typing text.from earns M's signature rejection - "The name 'text.from' wasn't recognized. Make sure it's spelled correctly." Excel doesn't care about case and DAX mostly forgives it; M cares completely. When a formula fails and you cannot see why, check the capitals first.
A missing else. Excel lets you write an IF with two arguments and quietly returns FALSE when the test fails. M's grammar has no such mercy: every if needs its else written out. Leave it off and the dialog stops you with "Token Else expected", which is parser for "finish the sentence". Annoying the first time, better in the long run - every branch of your logic ends up on the page, where the next person to read it (you, in November) can see what happens when the test fails.
null. Next month's file arrives, you hit refresh, and a handful of rows in the Chase column now say Error. Click one and Power Query tells you: "We cannot convert the value null to type Logical." Somewhere in the new data is a customer with a null Balance - a credit hold, or an account opened this week with nothing invoiced yet. And null is not zero. null > 500 does not evaluate to false; it evaluates to null, which means "unknown". An if needs true or false, and you have handed it a shrug.
The guard:
if [Balance] <> null and [Days Overdue] <> null
and [Balance] > 500 and [Days Overdue] > 30
then "Chase" else "Wait"Why this works: the equality operators = and <> are the one place M treats null calmly, so [Balance] <> null returns a clean true or false. And and evaluates left to right and stops at the first false, so a null balance fails the first test and never reaches the comparison that would have errored. The technical name is short-circuiting; the practical name is a bouncer at the door.
One habit to go with it: error cells don't poison the rest of the column. Power Query keeps them per cell, so right-click the column header and Replace Errors when you want them gone, or Home > Keep Rows > Keep Errors when you want every failing row in one place to see what they have in common.
Frequently Asked Questions
Do I need to learn M to use Power Query?
No. The UI genuinely covers most day-to-day reshaping, and the first three parts of this series never touched a formula. The custom column is the first place where typing beats clicking, and reading the generated step in the formula bar will teach you more M, faster, than any course - you already know what each step does, so the code arrives with subtitles.
Is M the same language as DAX?
No, and mixing them up is the classic Power BI beginner wall. M runs in Power Query and shapes data before it loads: filtering, combining, custom columns like this one. DAX runs after load, in measures and calculated columns on the model. Different grammar, different rules - M is case-sensitive and DAX largely isn't. If you are in the Power Query editor, you are writing M.
Why does my custom column show Error on some rows?
Nearly always null. Ordering comparisons like > and < return null when either side is null, and an if cannot act on null - that is the "We cannot convert the value null to type Logical" error. Test each column with <> null before comparing it, and put those tests first, so the and stops before the comparison that would fail.
Is M case-sensitive?
Yes, completely. Keywords are lowercase (if, then, else, and, or), functions have exact casing (Text.From, not text.from), and your own column and step names must match their casing too. If M insists a name wasn't recognized while you are looking straight at it, the casing is almost always the problem.
Will a custom column update when the source data refreshes?
Yes. A custom column is an applied step like any other, so it re-runs against every row on every refresh, including rows that did not exist when you wrote it. That is the whole reason to build the logic in Power Query instead of typing formulas next to a pasted table in Excel.
Can I edit a custom column after I've clicked OK?
Yes. Click the gear icon next to the step in Applied Steps and the dialog reopens with your formula in it, or edit the step directly in the formula bar. Nothing about a custom column is final; it is one step in the pipeline like everything else.
Where the series goes from here
One part left, and it is the payoff. Power Query from Scratch, Part 5: Parameters, functions and a model that refreshes itself takes the formula bar you have now met and turns the whole series into something reusable: parameters for the values that change every month, functions for the logic you would otherwise copy between queries, and a refresh that takes one click instead of a rebuild. The M you met today is what makes all of it possible.
Part 5 lands 30 Jul.