Power Query合并ERP导出Excel时负数丢失负号的解决方法咨询

我通过Power Query批量合并ERP系统生成的多个Excel文件，其中“Expenditures”列的负数仅以红色字体显示、不带负号。合并后该列所有数值均被转为正数。已知Power Query无法原生检测字体颜色，且源文件无其他负数标识，手动修改源文件格式工作量极大，咨询能否在Power Query端解决该问题。

原Power Query代码：

let
    Source = Folder.Files("C:\Users\yevgen.matiukha\..."),
    #"Filtered Rows" = Table.SelectRows(Source, each ([Name] <> "desktop.ini")),
    #"Filtered Hidden Files1" = Table.SelectRows(#"Filtered Rows", each [Attributes]?[Hidden]? <> true),
    #"Invoke Custom Function1" = Table.AddColumn(#"Filtered Hidden Files1", "Transform File", each #"Transform File"([Content])),
    #"Renamed Columns1" = Table.RenameColumns(#"Invoke Custom Function1", {"Name", "Source.Name"}),
    #"Removed Other Columns1" = Table.SelectColumns(#"Renamed Columns1", {"Source.Name", "Transform File"}),
    #"Removed Errors1" = Table.RemoveRowsWithErrors(#"Removed Other Columns1", {"Transform File"}),
    #"Expanded Table Column1" = Table.ExpandTableColumn(#"Removed Errors1", "Transform File", Table.ColumnNames(#"Transform File"(#"Sample File")))
in
    #"Expanded Table Column1"

Power Query本身无法直接读取单元格字体颜色，但可以通过调用Excel的COM对象获取颜色信息，进而识别并修正负数。核心是修改Transform File自定义函数，在处理每个文件时完成颜色检测与数值修正。

修改后的完整代码 #

首先替换原有的Transform File自定义函数为以下内容：

let
    TransformFile = (fileContent as binary) as table =>
    let
        // 初始化Excel COM对象
        ExcelApp = CreateObject("Excel.Application"),
        // 隐藏Excel窗口、禁用警告弹窗，提升处理效率
        ExcelApp.Visible = false,
        ExcelApp.DisplayAlerts = false,
        // 打开目标文件
        Workbook = ExcelApp.Workbooks.OpenFromBinary(fileContent),
        // 获取第一个工作表（需指定表名则改为Workbook.Worksheets("你的表名")）
        Worksheet = Workbook.Worksheets(1),
        // 获取文件中已使用的数据范围
        DataRange = Worksheet.UsedRange,
        // 将范围值转为Power Query可识别的表结构
        RangeValues = DataRange.Value,
        Headers = List.First(RangeValues),
        DataRows = List.Skip(RangeValues, 1),
        RawTable = Table.FromRows(DataRows, Headers),
        // 定位Expenditures列的Excel索引（Excel列索引从1开始）
        ExpColName = "Expenditures",
        ExpColIndex = List.PositionOf(Headers, ExpColName) + 1,
        // 生成每行的颜色标记：红色字体则标记为true
        RowCount = List.Count(DataRows),
        ColorFlags = List.Generate(
            () => [RowNum=2, IsRed=false], // 数据行对应Excel第2行（表头是第1行）
            each [RowNum] <= RowCount + 1,
            each [
                RowNum = [RowNum] + 1,
                IsRed = Worksheet.Cells([RowNum], ExpColIndex).Font.Color = RGB(255, 0, 0)
            ],
            each [IsRed]
        ),
        // 给原表添加颜色标记列
        TableWithColor = Table.FromColumns(
            Table.ToColumns(RawTable) & {ColorFlags},
            Headers & {"IsRed"}
        ),
        // 修正Expenditures列数值：红色字体转为负数
        CorrectedTable = Table.TransformColumns(TableWithColor, {
            {ExpColName, (val, row) => if row[IsRed] then -val else val, type number}
        }, (row) => row),
        // 移除临时颜色标记列
        FinalTable = Table.RemoveColumns(CorrectedTable, {"IsRed"}),
        // 关闭文件并释放COM资源
        CloseWorkbook = Workbook.Close(false),
        QuitExcel = ExcelApp.Quit(),
        Cleanup = ExcelApp.Dispose()
    in
        FinalTable
in
    TransformFile

主查询代码保留原内容即可，确保调用的是修改后的Transform File函数。

注意事项 #

1. 需确保Excel支持COM对象调用，且Power Query已启用「允许COM自动化」选项
2. 若源文件中红色字体不是标准RGB(255,0,0)，可通过Excel「设置单元格格式」查看实际颜色值，替换代码中的RGB(255,0,0)
3. 处理大量文件时，COM对象会占用较多资源，建议分批处理或添加错误捕获逻辑

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