如何在Power Query中捕获Excel文件的深层加载错误？

问题背景 #

从SharePoint列表读取数百个包含30+工作表的Excel工作簿，部分工作表存在#REF!、#NAME!等公式错误。常规场景下Power Query会直接显示错误，但当前场景中转换界面预览完全正常，仅在点击「关闭并加载」时弹出[DataFormat.Error] Invalid cell value '#REF!'错误。现有错误处理逻辑均为加载后的转换步骤，无法提前拦截该错误，需实现加载阶段捕获错误（将错误值以文本形式加载）。已尝试数据质量视图、try/catch语句但未解决，同时发现直接加载与使用Table.AddColumn加载时错误捕获行为存在差异。

核心原因分析 #

1. 预览与加载的扫描范围差异：Power Query预览时仅读取部分样本数据，未触碰到全量数据中的公式错误；而「关闭并加载」时会扫描完整数据集，触发未被预览到的错误。
2. 加载机制差异：直接加载（导航器选择工作表）是Power Query自动生成的强校验逻辑，会严格校验数据类型；Table.AddColumn属于自定义动态加载，允许在加载过程中插入提前处理逻辑，错误捕获时机更早。

解决方案 #

方法1：强制以文本类型加载所有列 #

修改Excel.Workbook函数的参数，强制所有列以文本类型加载，公式错误会被保留为对应文本值：

let
    Source = SharePoint.Files("https://your-sharepoint-site-url", [ApiVersion=15]),
    FilteredExcelFiles = Table.SelectRows(Source, each Extension = ".xlsx"),
    // 加载工作簿时强制所有列按文本处理
    AddWorksheets = Table.AddColumn(FilteredExcelFiles, "Worksheets", each Excel.Workbook([Content], [UseHeaders=true, DataType=Text])),
    ExpandWorksheets = Table.ExpandTableColumn(AddWorksheets, "Worksheets", {"Name", "Data"}, {"SheetName", "CleanedData"})
in
    ExpandWorksheets

如果使用OLEDB驱动连接，可在数据源连接字符串中添加;IMEX=1参数，强制混合类型列转为文本。

方法2：加载阶段逐列捕获错误 #

在读取工作表内容的步骤中，针对每一列提前用try/otherwise捕获错误，将错误值转为文本：

let
    Source = SharePoint.Files("https://your-sharepoint-site-url", [ApiVersion=15]),
    FilteredExcelFiles = Table.SelectRows(Source, each Extension = ".xlsx"),
    AddWorksheets = Table.AddColumn(FilteredExcelFiles, "Worksheets", each Excel.Workbook([Content])),
    ExpandWorksheets = Table.ExpandTableColumn(AddWorksheets, "Worksheets", {"Name", "Data"}, {"SheetName", "RawData"}),
    // 加载阶段处理每一列的错误
    ProcessErrorValues = Table.AddColumn(ExpandWorksheets, "CleanedData", each 
        let
            ColumnNames = Table.ColumnNames([RawData]),
            // 对每一列应用错误捕获逻辑
            TransformColumns = List.Transform(ColumnNames, (colName) => 
                {colName, each try _ otherwise Text.From(_)}
            ),
            CleanedTable = Table.TransformColumns([RawData], TransformColumns)
        in
            CleanedTable
    ),
    RemoveRawData = Table.RemoveColumns(ProcessErrorValues, {"RawData"})
in
    RemoveRawData

此方法的关键是在读取工作表Data后立即处理错误，而非加载完成后再执行转换，避免全量扫描时触发错误。

方法3：使用Excel.CurrentWorkbook加载（特定场景） #

针对本地同步到SharePoint的文件，可尝试用Excel.CurrentWorkbook替代Excel.Workbook，它对错误值的处理更宽松：

let
    Source = SharePoint.Files("https://your-sharepoint-site-url", [ApiVersion=15]),
    FilteredExcelFiles = Table.SelectRows(Source, each Extension = ".xlsx"),
    AddWorksheets = Table.AddColumn(FilteredExcelFiles, "Worksheets", each 
        let
            TempFile = File.Contents([Path]),
            WorkbookSheets = Excel.CurrentWorkbook(),
            // 按需筛选目标工作表
            TargetSheets = Table.SelectRows(WorkbookSheets, each Name = "TargetSheetName")
        in
            TargetSheets
    )
in
    AddWorksheets

注意事项 #

• 强制文本加载可能导致数值型数据转为文本，后续需手动转换数据类型，但可保证错误值被完整捕获。
• IMEX=1仅适用于OLEDB驱动，新Excel连接器优先使用DataType=Text参数。
• 直接加载与Table.AddColumn加载的核心差异在于错误触发时机：前者是全量扫描时校验，后者是逐工作表加载时处理。

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