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

[Emmanuel1221]

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

I’m working with Power Query inside Power BI Dataflows, and I’ve run into an annoying problem. I have several nested fields (like tables and records) in my queries that I want to leave unexpanded for now — I’m using different reports that will each pull only what they need later.

But every time I press Save & Close, Power Query automatically adds steps that remove the nested fields (tables/records), and I can’t seem to stop it.

I want to clean and prep my data without expanding everything all at once. Is there any way to disable this auto-step behavior or keep nested columns from being dropped? Any advice or workarounds would be hugely appreciated. Thanks!

[v-dineshya · Accepted solution]

Hi @Emmanuel1221 ,

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

[v-dineshya]

Hi @Emmanuel1221 ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and consider giving a KUDOS. Feel free to reach out if you need further assistance.

Regards,

Dinesh

[v-dineshya]

Hi @Emmanuel1221 ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and consider giving a KUDOS. Feel free to reach out if you need further assistance.

Regards,

Dinesh

[v-dineshya]

Hi @Emmanuel1221 ,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided for the issue worked? or Let us know if you need any further assistance?
If our response addressed, please mark it as Accept as solution and consider giving a KUDOS. Feel free to reach out if you need further assistance.

Regards,

Dinesh

[rohit1991]

Hi @Emmanuel1221

Power Query tends to automatically remove unreferenced columns to optimize the query when loading to the data model.

There are a few approaches that can help:

1. Explicitly reference all needed fields before expanding a record/table. You can do this by selecting the fields manually during the expand step rather than using “Expand All” or leaving it dynamic.

2. Consider adding a Table.Buffer() before expanding, as it can sometimes help preserve schema.

3. If you’re merging or appending queries, double-check that the structures are consistent across steps to avoid schema mismatch.

Unfortunately, there’s no toggle to disable this behavior entirely, but structuring your query to only bring in the exact fields you need (and referencing them directly) usually prevents Power Query from auto-trimming them.

[Akash_Varuna]

@Emmanuel1221 Power Query auto-removes nested columns (tables/records) to optimize queries when saving in Dataflows , this cannot be disabled from my understandiong but try these workarounds please. To prevent this, explicitly use the Keep Columns step to retain the nested fields you need. Try to buffer the table with Table.Buffer to lock its structure or load nested fields into separate queries for later use.