Power Query新增列与刷新时格式保留问题技术求助

我来帮你搞定这个格式保留的问题——其实不用急着上VBA，Power Query本身就能解决，核心是要在M代码里明确给所有数值列设置显示格式，而不只是数据类型。

问题根源 #

你之前的代码只把Value列设为Percentage.Type，但透视生成的新日期列（也就是那些百分比列）并没有被指定统一的显示格式。Excel加载PQ数据时，如果PQ没明确给出格式规则，新增列就会用默认的常规格式；而现有列刷新时能保留格式，是因为Excel记住了你手动设置的格式，但新增列没这个“记忆”。

修改后的M代码方案 #

我们要在透视步骤之后，给所有非ID的列（也就是所有百分比列）统一设置数据类型和显示格式：

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", type text}, {"Date", type date}, {"Value", Percentage.Type}}),
    #"Pivoted Column" = Table.Pivot(
        Table.TransformColumnTypes(#"Changed Type", {{"Date", type text}}, "en-US"),
        List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"Date", type text}}, "en-US")[Date]),
        "Date",
        "Value",
        List.Sum
    ),
    #"Formatted Percentage Columns" = Table.TransformColumns(
        #"Pivoted Column",
        List.RemoveItems(Table.ColumnNames(#"Pivoted Column"), {"ID"}),
        each {_, Percentage.Type, [Format="0.00%"]}
    )
in
    #"Formatted Percentage Columns"

关键步骤解释 #

1. List.RemoveItems：筛选出除了ID之外的所有列，确保我们只处理百分比数据列，不会误改ID列的格式。
2. Table.TransformColumns：给这些列同时设置两个核心属性：
   • 数据类型为Percentage.Type，保证Power Query始终识别这些列为百分比数据
   • 显示格式为"0.00%"，强制保留两位小数的百分比格式，这个规则会直接传递到Excel中，不管是现有列还是新增列

加载时的额外保障 #

为了确保刷新时格式完全稳定，加载数据到Excel时可以加个小设置：

• 右键Power Query连接 → 选择「加载到」→ 点击「属性」
• 在「使用格式和布局」选项中，勾选「保留单元格格式」和「保留列排序、筛选和布局」

要不要用VBA？ #

如果上面的Power Query方案能满足需求，完全不需要VBA。只有当你有更复杂的格式规则（比如根据数值范围设置条件格式这类Power Query没法直接处理的需求），才需要考虑用Worksheet_Change事件来补充。

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