Title: Help! Power Query keeps auto-removing table/record columns when I save — how do I stop this?

Hi Anonymous ,

Thank you for reaching out to the Microsoft Community Forum.

When you Save & Close in Power Query Online, the service tries to automatically "sanitize" your output by removing any columns of type table or record. It assumes you're publishing a clean flat table for loading into the model. This behavior is automatic and can add a Removed Other Columns or Removed Columns step, dropping nested fields unless they’ve been explicitly expanded.

Please follow these things:

1. Convert Nested Fields to Text Temporarily: If you want to preserve the column structure but don't need the nested data just yet: Add a custom column that converts the record/table to a placeholder like:

M code: Table.ToText([NestedTable]) or Record.ToText([NestedRecord])

Note: This way, the column isn’t removed, but it’s no longer a complex type.

2. Use Table.Buffer to Lock the Schema: Before the final step, you can wrap your table in a Table.Buffer(), which sometimes prevents Power Query from analyzing and modifying your query structure automatically.

M code : Table.Buffer(YourLastStep)

3. Add a Dummy "Expand" Step Then Undo It: Manually expand the nested field once. Then undo the expansion (or keep only the original structure). This sometimes tricks Power Query into thinking you’ve “touched” the column, so it leaves it alone.

4. Move Those Queries into Linked Entities Instead: If you're using Dataflows, consider separating your logic: Keep the raw data with nested fields in one dataflow (or linked entity). Then create downstream dataflows that consume this entity and perform any expansions there.

Note: There is currently no setting to disable this behavior entirely in Power BI Service. Watch for Auto-Inferred Steps
Before clicking “Save & Close,” check if any new steps like: Removed Columns, Kept Columns and Expanded Table Column have been automatically added. If so, delete them manually before saving, or revert to your previous step.

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you