There's a moment in every data workflow when the tool you're using stops being a spreadsheet and starts being a maze. The person who posted this question knows that feeling well. They're not asking for a magic button or a flashy feature. They're asking for a clean, repeatable way to isolate March revenue from a table cluttered with cancellations, tax lines, and discounts. That's not a trivial ask. It's the kind of problem that separates a one-off fix from a process you can trust next month and the month after.
The core issue here is straightforward: the table mixes reservation details with line-item charges, and some of those charges belong to reservations that were cancelled in March. The goal is to recognize revenue only for March, which means including cancelled reservations with revenue and excluding anything with a zero total. The user's instinct to use Power Query is correct. Duplicating the table, filtering for cancellation dates, and grouping by itinerary item ID is a solid start. But the merge step is where things get tangled. Merging the filtered cancelled rows back into the original table can duplicate totals or drag in irrelevant rows if the join keys aren't precise. The real fix is to filter first, then aggregate, then join only the necessary identifiers, not to merge entire tables and hope for the best.
What this person actually needs is a clear mental model: treat the cancellation date as a filter, not as a flag to carry through every row. Filter the table to rows where cancellation date is not blank and total is not zero. Group by itinerary item ID and sum the totals. Then, for the main revenue calculation, filter the original table to include only reservations with a check-in or check-out in March, and append the cancelled ones separately. That two-pass approach, one for active March stays, one for March cancellations, gives you a clean number without double counting. It's not glamorous, but it works. And it's the kind of clarity that turns a frustrating afternoon into a repeatable routine.
The broader lesson here is about control. Spreadsheets and billing exports are messy by nature. They're built for recording, not for reporting. When you're stuck in a merge or a filter, it's easy to lose sight of the revenue figure you actually need. But the answer isn't to throw up your hands or to manually scrub rows. It's to step back, define the logic in plain language first, and then translate that logic into the tool you're using. If you can say "I need all March cancellations with revenue, plus all March stays," you can build that. If you can't say it clearly, no formula will save you. So start there. Write the sentence. Then build the query. The spreadsheet will follow.