// Excel: import monthly-sales.csv, name the query SalesInput. // Add a blank query, open Advanced Editor and paste this code. // SalesInput must have headers order_id, order_date, region, amount. // This deliberately stops on duplicate IDs, invalid dates or amounts. let Source = SalesInput, Columns = Table.SelectColumns(Source, {"order_id", "order_date", "region", "amount"}), Typed = Table.TransformColumnTypes(Columns, {{"order_id", type text}, {"order_date", type date}, {"region", type text}, {"amount", type number}}, "en-US"), Clean = Table.TransformColumns(Typed, {{"order_id", each if _ = null then "" else Text.Trim(_), type text}, {"region", each if _ = null then "" else Text.Trim(_), type text}}), NoErrors = if Table.RowCount(Table.SelectRowsWithErrors(Clean)) > 0 then error "Invalid input types" else Clean, Invalid = Table.SelectRows(NoErrors, each [order_id] = "" or [region] = "" or [order_date] = null or [amount] = null or [amount] < 0 or Number.Round([amount], 2) <> [amount]), Checked = if Table.RowCount(NoErrors) = 0 or Table.RowCount(Invalid) > 0 then error "Empty input or invalid order" else NoErrors, Unique = if Table.RowCount(Table.Distinct(Checked, {"order_id"})) <> Table.RowCount(Checked) then error "Duplicate order ID" else Checked, Grouped = Table.Group(Unique, {"region"}, {{"total_amount", each List.Sum([amount]), type number}}), Sorted = Table.Sort(Grouped, {{"region", Order.Ascending}}) in Sorted