Power Query展开含Web调用结果的表列时错误处理方案咨询

需要对包含自定义网页抓取函数生成列的表执行展开操作，部分行调用自定义函数时会返回错误。由于Power Query的表展开逻辑要求所有行的嵌套表结构统一、表头字段完全一致，错误行会导致整个展开操作失败，预期效果是调用出错的行展开字段留空，不中断整体展开流程。

相关截图 #

• 错误提示截图
• 展开结果截图

原有M代码 #

主查询代码 #

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"No.", Int64.Type}, {"CAS Number", type text}, {"Chemical name", type text}}),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "Fx GetBriefProfileLink", each #"Fx GetBriefProfileLink"([CAS Number])),
    #"Expanded Fx GetBriefProfileLink" = Table.ExpandTableColumn(#"Invoked Custom Function", "Fx GetBriefProfileLink", {"Name", "Cas Number", "exported-column-briefProfileLink"}, {"Name", "Cas Number.1", "exported-column-briefProfileLink"})
in
    #"Expanded Fx GetBriefProfileLink"

自定义函数Fx GetBriefProfileLink代码 #

(CAsNumberorName as text) =>

let
    Source = Excel.Workbook(Web.Contents("https://echa.europa.eu/search-for-chemicals?p_p_id=disssimplesearch_WAR_disssearchportlet&p_p_lifecycle=2&p_p_state=normal&p_p_mode=view&p_p_resource_id=exportResults&p_p_cacheability=cacheLevelPage&_disssimplesearch_WAR_disssearchportlet_sessionCriteriaId=dissSimpleSearchSessionParam101401654440118533&_disssimplesearch_WAR_disssearchportlet_formDate=1654440118558&_disssimplesearch_WAR_disssearchportlet_sskeywordKey="&CAsNumberorName&"&_disssimplesearch_WAR_disssearchportlet_orderByCol=relevance&_disssimplesearch_WAR_disssearchportlet_orderByType=asc&_disssimplesearch_WAR_disssearchportlet_exportType=xls"))[Data]{0},
    #"Removed Top Rows" = Table.Skip(Source,2),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Name", type text}, {"EC / List Number", type text}, {"Cas Number", type text}, {"Substance Information Page", type text}, {"exported-column-briefProfileLink", type text}, {"exported-column-obligationsLink", type text}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Name", "EC / List Number", "Cas Number", "exported-column-briefProfileLink"}),
    #"Kept First Rows" = Table.FirstN(#"Removed Other Columns",1),
    #"Removed Other Columns1" = Table.SelectColumns(#"Kept First Rows",{"Name", "Cas Number", "exported-column-briefProfileLink"})
in
    #"Removed Other Columns1"

示例数据 #

No. CAS Number  Chemical name
43  3380-30-1   5-chloro-2-(4-chlorphenoxy)phenol
44  03228-02-2  4-isopropyl-m-cresol
45  89-83-8 Thymol
46  60207-90-1  Propiconazole
47  5395-50-6   Tetrahydro-1,3,4,6-tetrakis(hydroxymethyl)imidazo[4,5-d]imidazole-2,5(1H,3H)-dione
48  15630-89-4  Sodium percarbonate
49  027176-87-0 Dodecylbenzenesulfonic acid
50  001344-09-8 Sodium silicate

这个报错的根本原因不是Power Query的缺陷，而是自定义函数调用失败时返回的要么是错误值，要么是列不全/结构不对的表，Table.ExpandTableColumn做展开时会先扫描整列所有值的结构，只要有一个值不符合列要求，就会直接抛错。解决思路非常直接：不管函数调用成不成功，都让它返回列名完全一致的表，展开的时候自然就不会卡壳。

步骤1：修改自定义函数，增加错误捕获和结构对齐 #

在原有函数逻辑外增加try...otherwise错误捕获，出错时直接返回和成功结果列完全一致的空表；最后再加一层结构校验，哪怕查询逻辑返回缺列/多列的表，也强制对齐到标准列结构：

(CAsNumberorName as text) =>
let
    // 定义统一的标准输出列，所有分支返回的表都必须包含这几个列
    StandardCols = {"Name", "Cas Number", "exported-column-briefProfileLink"},
    BaseEmptyTable = #table(StandardCols, {}),
    // 原有查询逻辑放入try块，出错直接返回空表
    QueryData = try
        let
            Source = Excel.Workbook(Web.Contents("https://echa.europa.eu/search-for-chemicals?p_p_id=disssimplesearch_WAR_disssearchportlet&p_p_lifecycle=2&p_p_state=normal&p_p_mode=view&p_p_resource_id=exportResults&p_p_cacheability=cacheLevelPage&_disssimplesearch_WAR_disssearchportlet_sessionCriteriaId=dissSimpleSearchSessionParam101401654440118533&_disssimplesearch_WAR_disssearchportlet_formDate=1654440118558&_disssimplesearch_WAR_disssearchportlet_sskeywordKey="&CAsNumberorName&"&_disssimplesearch_WAR_disssearchportlet_orderByCol=relevance&_disssimplesearch_WAR_disssearchportlet_orderByType=asc&_disssimplesearch_WAR_disssearchportlet_exportType=xls"))[Data]{0},
            #"Removed Top Rows" = Table.Skip(Source,2),
            #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
            #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Name", type text}, {"EC / List Number", type text}, {"Cas Number", type text}, {"Substance Information Page", type text}, {"exported-column-briefProfileLink", type text}, {"exported-column-obligationsLink", type text}}),
            #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Name", "EC / List Number", "Cas Number", "exported-column-briefProfileLink"}),
            #"Kept First Rows" = Table.FirstN(#"Removed Other Columns",1),
            #"Select Standard Cols" = Table.SelectColumns(#"Kept First Rows", StandardCols)
        in
            #"Select Standard Cols"
    otherwise BaseEmptyTable,
    // 最终结构校验：和基准空表合并，自动补全缺失列、删除多余列，保证结构100%统一
    Output = Table.SelectColumns(Table.Combine({BaseEmptyTable, QueryData}), StandardCols)
in
    Output

步骤2（可选兜底）：主查询调用时增加二次校验 #

如果担心存在其他边缘情况导致返回非表值，可以在主查询调用自定义函数的步骤再加一层校验，把所有不符合要求的返回值都替换成标准空表，修改后主查询代码：

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"No.", Int64.Type}, {"CAS Number", type text}, {"Chemical name", type text}}),
    StandardCols = {"Name", "Cas Number", "exported-column-briefProfileLink"},
    BaseEmptyTable = #table(StandardCols, {}),
    #"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "Fx GetBriefProfileLink", each 
        let
            CallRes = try #"Fx GetBriefProfileLink"([CAS Number]) otherwise BaseEmptyTable,
            CheckedRes = if Value.Is(CallRes, type table) then CallRes else BaseEmptyTable
        in CheckedRes
    ),
    #"Expanded Fx GetBriefProfileLink" = Table.ExpandTableColumn(#"Invoked Custom Function", "Fx GetBriefProfileLink", StandardCols, {"Name", "Cas Number.1", "exported-column-briefProfileLink"})
in
    #"Expanded Fx GetBriefProfileLink"

修改后运行查询，请求失败、无匹配结果的行会自动在展开列显示空值，不会中断整体展开流程。

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