Level 4 · Band orders by size and add the month

Nicole doesn’t want 34 numbers; she wants three groups and a calendar. That is two new columns, one built from a rule and one taken from the date.

Change the date first

Change type: order_date to date

Date functions need a date. In Power BI, click the ABC icon on the header and pick Date. If the text is in another format than your locale expects (03/04/2025 from a European system), use Change Type → Using Locale and pick the source’s locale, so March and April don’t swap.

A conditional column

In Power BI: Add Column → Conditional Column. The dialog builds the rule in rows:

	Column name	Operator	Value	Output
If	subtotal	is greater than or equal to	1000	Large
Else If	subtotal	is greater than or equal to	250	Medium
Else				Small

Name it size_band at the top. Power Query turns the dialog into one M expression, which is what you write here:

Add column: size_band = if [subtotal] >= 1000 then "Large" else if [subtotal] >= 250 then "Medium" else "Small"

The tests run from the top and stop at the first one that is true, so order matters: put the biggest threshold first. The M step it records:

Table.AddColumn(#"Changed Type", "size_band",
    each if [subtotal] >= 1000 then "Large" else if [subtotal] >= 250 then "Medium" else "Small")

When the rule needs more than one column (and, or) or arithmetic, use Add Column → Custom Column and type the expression yourself. M is case-sensitive: if, then, else are lower case, and text goes in double quotes.

Parts of a date

Select order_date, then Add Column → Date for a menu of pieces: Year, Month (with Month, Name of Month, Start of Month and more), Quarter, Week, Day (with Name of Day), Age. Name of Month records:

Add column: month = Date.MonthName([order_date])

Other useful ones: Date.Year([order_date]), Date.Month([order_date]) (1 to 12, the number you sort month names by), and Date.Day. Transform → Date offers the same pieces but replaces the column instead of adding one.

Power Query or DAX?

You could write the band as a DAX calculated column too. A rule that depends only on one row’s values, like this one, is usually better in Power Query: it’s computed once at refresh and compresses well. Keep DAX for logic that depends on filters, such as “Large compared with this customer’s average”.

What it shows

Eleven of the 34 orders are Large, and they bring in about 71% of the account’s money; one August order alone is over $10,500. November is the busiest month, with seven orders.

Common mistakes

• Thresholds in the wrong order. The first true test wins.
• Gaps or overlaps. > 250 in one test and < 250 in the next leaves exactly 250 in no band; >= then else covers every value once.
• Month names without month numbers. Sorted alphabetically, April comes first. In the model, sort the name by the number (Column tools → Sort by column).