Level 3 · A total column, a month column, then one month

Most of the columns an analyst wants are not in the export: a line total, the month, a size band. In a worksheet you would write a formula and fill it down. In Power Query you add a custom column: one formula, written once, applied to every row, and replayed on every refresh.

A custom column, row by row

Add column: line_total = [quantity] * [unit_price]

Inside the formula, [quantity] means “this row’s quantity”. There are no cell addresses and no fill-down: Power Query evaluates the formula for each row. On order 9338, nine Northfield Rattan Sectionals at 229.00 give 2,061.00. unit_price here is the price paid per unit after any coupon, which is why some rows show 194.65 instead of 229.

In Excel: Add Column → Custom Column, type the name, double-click columns in the list to insert them, click OK. The formula language is M, which looks a little like Excel but is not the same:

You want	Excel cell	M custom column
a column of this row	C2	[quantity]
multiply	=C2*D2	[quantity] * [unit_price]
join text	=A2&" "&B2	[a] & " " & [b] (text only)
a condition	=IF(D2>=500,"big","small")	if [unit_price] >= 500 then "big" else "small"
the month	=MONTH(B2)	Date.Month([order_date])

M is case-sensitive: Date.Month works, date.month doesn’t, and [Quantity] is not [quantity].

The month from a date

Change type: order_date to date
Add column: month = Date.Month([order_date])

Date.Month returns a whole number from 1 to 12. In real Power Query it only works on a column whose type is Date: on text it returns an error. That is why the first step sets the type, even though 2025-04-02 already looks like a date. With the type set, the point-and-click route opens up: select order_date, Add Column → Date → Month → Month. The same menu has Year, Name of Month (Date.MonthName, giving April), Day of Week and Start of Month.

Filter to one month

Filter rows: month = 4

The source has 39 lines for April to June 2025. The filter keeps the 12 from April: 23 units and 6,669.60 of revenue. A quick sanity check before you send it: the dates in the result should all start with 2025-04, and the row count should match what you see when you filter the source by eye.

You could filter on the date itself instead (Date Filters → In the Previous → Month, or order_date between April 1 and April 30). Building the month column first has a bonus: the same column groups the rows by month in a PivotTable later.

The M behind the steps

#"Changed Type" = Table.TransformColumnTypes(Source, {{"order_date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "line_total", each [quantity] * [unit_price]),
#"Inserted Month" = Table.AddColumn(#"Added Custom", "month", each Date.Month([order_date]), Int64.Type),
#"Filtered Rows" = Table.SelectRows(#"Inserted Month", each [month] = 4)

each is M’s way of saying “for every row”.

Common mistakes

• Filtering on a name the column doesn’t hold. month = April returns no rows; the column holds 4. Use Date.MonthName if you want the word.
• Date.Month on a text column. Real Power Query returns an error on every row. Set the type to Date first.
• Filtering before building the column. Filter rows: month = 4 as the first step fails: there is no column month yet. Steps run top to bottom.
• Typing the column name without brackets. quantity * unit_price is not M; columns go in [ ].