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 ,
This behavior happens because Power Query, especially in Power BI Dataflows, tries to automatically "sanitize" the output by removing any unexpanded structured columns like tables and records before loading the data. 

Unfortunately, there is no official setting to completely turn off this auto-generated "Removed Other Columns" step. However, a common workaround is to manually delete the automatically added step after saving, or better yet, explicitly define the columns you want to retain using a SelectColumns or KeepColumns step before saving. 

Another approach is to ensure your final query output is a flat table — perhaps by creating a staging query where nested fields are preserved, and then referencing that query separately in your final output, where you can expand only what you need. This way, you maintain control over nested structures without being forced to expand everything prematurely. It’s a bit of extra setup but can save a lot of headaches in the long run.

Passionate about leveraging data analytics to drive strategic decision-making and foster business growth.

Connect with me on LinkedIn: Rohit Kumar.

[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.