The pivot table isn't the problem. The data model isn't the problem. The real issue is that you're asking a summary tool to do the job of a source table, and then you're surprised when it won't bend to your will. You've already spotted the core tension: your New Zealand data has no consulting row, so no amount of "show zero values" toggling will conjure one. That's not a bug in Excel. That's a sign you've outgrown the approach, and the fix is simpler than you think.
What you're really missing is a dimension table. You have two lists that act as your backbone: the five countries and the three product categories. Those belong in a standalone table, one that exists outside your transaction data. Once you have that, you can cross join it with your sales fact table, and the missing consulting row for New Zealand appears naturally, with a zero where the amount should be. No XLOOKUPs into a pivot table, no SUMIFs scraping a report for inputs. You're not building a workaround. You're building a proper relational structure, and the payoff is that your pivot tables finally reflect the full universe of possibilities, not just the rows that happen to have sales.
Your instinct to avoid XLOOKUPs against a pivot table is correct, and it's worth pausing on why. A pivot table is an output. It's a view of aggregated data, not a source of truth. When you reference it, you're building on sand. If someone refreshes the pivot, changes a filter, or adds a new country, your formulas break or silently return the wrong number. The same applies to loading your Power Query output as a table and using SUMIF. That works, but it's still a manual patch. You'd be duplicating logic that belongs in the data model, and every duplication is a future headache. The better path is to load your Power Query output into the data model as a table, then add a small dimension table for your categories, and build a relationship between them. Your pivot tables then use that relationship, and the zero row appears automatically.
The practical move is to stop treating this as a pivot table problem and start treating it as a modeling problem. Create a table with your three categories, another with your five countries, and connect them to your sales data through the data model. Your pivot tables will still work, but now they'll show every combination, including the zeros. That's not a hack. That's how you get a complete answer. And once you see that missing consulting row for New Zealand appear, you'll wonder why you ever tried to force a square peg into a round hole. The solution isn't in the pivot settings. It's in the structure you build before the pivot ever touches the data.