Project: monthly shipment refresh
Tessa Cole, the operations lead at fictional Harbor Supply, needs a monthly shipment report for the warehouse review. The two supplied CSV files contain inconsistent labels, closed batches, missing quantities, and numeric text that can fail conversion.
Process each file through the same cleanup function. Keep its month attached to every batch. An accepted batch must be open, have both quantities present, and satisfy 0 <= Damaged <= Units. Zero-quantity batches are valid. Unknown quantities require review and must never become zero by default.
Return one row per team and source month with Accepted batches and Usable units. Include only grouped usable totals of at least 10, in the requested sort order. These eight steps continue your Report query. All files are supplied locally; the exercise does not access a device folder, schedule refreshes, or measure database folding.
Monthly files are easy to append and easy to misreport. Harbor Supply's March and April extracts use different capitalization, and several quantities need review. You need a repeatable query that keeps those exceptions visible until you've applied a stated rule.
Start with a function that cleans one supplied file and adds its source month. Reuse it for both extracts, parse quantities with try ... otherwise, then separate conversion failures from numbers that violate the business rules. Zero is valid here. Closed batches and unresolved quantities stay outside the accepted report.
Your final output counts accepted batches and sums usable units by team and month. Apply the minimum to those grouped totals, so small batches can still contribute to a qualifying month. Each step starts from your previous query, and a hidden replacement extract checks whether the transformations still work when the rows change.
let
Normalize = (x) => Text.Upper(Text.Trim(x)),
CleanFile = (t, month) =>
Table.AddColumn(
Table.TransformColumns(t, {{"Team", Normalize, type text}, {"Status", Normalize, type text}}),
"Source month", each month, type text),
MarchClean = CleanFile(March, "March")
in
MarchClean
CleanFile applies the same text rule to a supplied table and attaches the month passed by its caller.