UI fix for Expand table behavior of dropping rows

[Kscott0126]

In general searching, it seems well‑documented that when performing a merge in Power Query and then expanding the resulting table, any rows that have no match in the merged table get dropped during the expand step. This is “working as intended” according to the current design, but for new users — or users who don’t know to look for this — the behavior is unexpected and can lead to silent data loss.

There is no warning in the UI that expanding a merged table may remove rows, and the only way to preserve them is to write custom M code in the Advanced Editor to replace empty tables with nulls before expanding.

A simple, backward‑compatible improvement would solve this problem and make the behavior clear to users:

Add a third option to the expand menu: “Row‑Preserving Expand.”

As shown above today the UI offers:

• Expand

• Aggregate

The proposal is to add:

• Row‑Preserving Expand

This option would behave like a true left join from the user’s perspective: If the nested table is empty, the expanded columns would be filled with nulls instead of dropping the row. This is already achievable with custom M code, but should be a built‑in, discoverable option.

This would:

• Prevent silent data loss

• Make the expand behavior transparent

• Align the UI with the merge dialog’s promise (“Left Outer: All from first, matching from second”)

• Avoid breaking existing queries, since it’s a new function

• Help users understand that expand behavior is more nuanced than it appears

Even a tooltip noting that this option may be more expensive would be fine — the key is giving users a safe, intuitive way to expand without losing rows.