Power Query修改后Excel列乱序致公式失效，求可行规避方案

I’ve dealt with this exact headache before—there’s nothing worse than spending time building formulas just to have them break because columns randomly shift after a Power Query edit! Here are proven fixes to stop this from happening:

1. Lock Column Order in Power Query Editor #

This is the most reliable first step. Whenever you tweak your query:

• Open the Power Query Editor.
• Drag columns into your desired sequence, then explicitly save this order as part of the query: right-click any column > Reorder Columns > confirm the sequence.
• Apply and close the editor. Now, every refresh will output columns in this fixed order, so Excel won’t reorder them unexpectedly.

2. Use Explicit "Keep Columns" to Define Your Sequence #

Instead of letting Power Query load all columns automatically, specify exactly which columns you need and their order:

• In Power Query Editor, go to Home > Keep Columns > Choose Columns.
• Select columns in the exact order you want them to appear in Excel.
• This acts as a safeguard—even if your upstream query changes (like adding/removing columns), the final output will only include your selected columns in your preferred sequence.

3. Preserve Excel Table Layout Settings #

Excel has a built-in setting to maintain your table’s structure during refreshes:

• Select your Excel table.
• Go to the Table Design tab.
• Click Properties.
• Check the box for Preserve column sort/filter.
• This tells Excel to keep your existing column order, sort, and filter settings even when the underlying data from Power Query updates. Pair this with the Power Query fixes above for best results.

4. Sync Power Pivot Column Order with Excel #

If your Power Pivot calculated columns are contributing to the shift:

• Open Power Pivot (Data > Manage Data Model).
• Locate your table in the model and reorder columns to match your desired Excel sequence.
• Save the model and refresh your Excel table. This ensures Excel pulls data from the Data Model using the fixed order you set.

5. Switch to Structured Formula References #

Positional references (like =A1) break when columns shift. Instead, use structured references that target columns by name:

• Replace =B2*C2 with =Table[Price]*Table[Quantity].
• This way, even if columns move, your formulas will still reference the correct data.

内容的提问来源于stack exchange，提问作者Martin