当列同时含数值与Excel函数时，如何设置Power Query列类型？

从外部Excel文件查询数据时，PRICE和FEES列存在混合内容：部分行是直接输入的数值（如1、0.8），部分行是引用同表其他列的Excel函数。当前Power Query代码如下：

#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"PRICE", type any}, {"FEES", type any}})

遇到的问题：

• 设为type any时，函数所在单元格显示为文本格式，需手动点击单元格按Enter才会显示计算结果（目标列已在Excel中设为常规格式）
• 改为type number时，函数所在单元格直接变为空值
• 调整“Table”>>“External Data Properties”中的“Preserve cell formatting”选项，无任何效果

1. 修改源文件读取逻辑，优先读取计算后的值 #

Power Query默认可能读取单元格的公式文本而非计算结果，需在读取源Excel文件的步骤中指定加载选项，强制读取计算后的值：

// 替换原读取源文件的代码，示例如下
let
    Source = Excel.Workbook(File.Contents("C:\你的源文件路径.xlsx"), null, null, LoadOption.OnlyValues),
    // 后续步骤（如选择工作表、重命名列等）
    #"Renamed Columns" = Table.RenameColumns(Source{[Item="Sheet1",Kind="Sheet"]}[Data],{{"Column1", "PRICE"}, {"Column2", "FEES"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"PRICE", type number}, {"FEES", type number}})
in
    #"Changed Type"

关键参数说明：LoadOption.OnlyValues（对应数值1）会让Power Query读取单元格的最终计算值，而非公式内容。

2. 若需保留源文件公式，在Power Query中解析计算 #

如果必须保留源文件的公式，同时在查询中自动计算结果，可使用自定义函数调用Excel计算引擎：

// 定义自定义计算函数
let
    EvaluateFormula = (formula as text) =>
        let
            TempSheet = Excel.CurrentWorkbook(){[Name="TempSheet"]}[Data],
            UpdatedTemp = Table.ReplaceValue(TempSheet, null, formula, Replacer.ReplaceValue, {"Formula"}),
            // 强制Excel计算
            Refresh = Excel.CurrentWorkbook(){[Name="TempSheet"]}[Data],
            Result = Refresh{0}[Result]
        in
            Result,
    // 应用到目标列
    #"Added Custom Price" = Table.AddColumn(#"Renamed Columns", "Calculated PRICE", each if Value.Is([PRICE], type text) and Text.StartsWith([PRICE], "=") then EvaluateFormula([PRICE]) else [PRICE]),
    #"Added Custom Fees" = Table.AddColumn(#"Added Custom Price", "Calculated FEES", each if Value.Is([FEES], type text) and Text.StartsWith([FEES], "=") then EvaluateFormula([FEES]) else [FEES]),
    // 转换为数值类型并替换原列
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom Fees",{{"Calculated PRICE", type number}, {"Calculated FEES", type number}}),
    #"Removed Original Columns" = Table.RemoveColumns(#"Changed Type",{"PRICE", "FEES"}),
    #"Renamed Calculated Columns" = Table.RenameColumns(#"Removed Original Columns",{{"Calculated PRICE", "PRICE"}, {"Calculated FEES", "FEES"}})
in
    #"Renamed Calculated Columns"

注意：此方法需要在当前工作簿中创建名为TempSheet的辅助表，包含Formula和Result两列，其中Result列直接引用Formula列的公式进行计算。

• 刷新Power Query查询
• 检查PRICE和FEES列，确认函数行已自动显示计算后的数值
• 无需手动点击单元格按Enter即可查看结果

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