Excel Power Query调用Web接口时的404错误处理咨询

问题根源：当接口返回404状态时，Instrument_Common字段为null值，原代码直接执行Record.ToTable(Value)时，无法将null转换为Record类型，触发报错。

修改后的函数代码：

(id as text)=>
let
    Source = Json.Document(Web.Contents("https://mrsrdac.gdc.standardchartered.com:7778/Instrument?RequestType=ByIdentifier&IdentifierType=ISIN&IdentifierValue="&id&"&Requester=GPTM")),
    Instrument = Source[Instrument],
    Instrument1 = Instrument{0},
    // 提取响应状态码
    DataResponseCode = Instrument1[Data_Flow][Data_Response_Code],
    // 根据状态码分支处理
    Result = if DataResponseCode = 200 then
        let
            InstrumentCommon = Instrument1[Instrument_Common],
            // 提取需要的字段，转为表后透视
            ConvertedToTable = Record.ToTable(InstrumentCommon),
            FilteredRows = Table.SelectRows(ConvertedToTable, each ([Name] = "CFI_Code" or [Name] = "Instrument_Full_Name")),
            PivotedColumn = Table.Pivot(FilteredRows, List.Distinct(FilteredRows[Name]), "Name", "Value")
        in
            PivotedColumn
    else
        // 404时返回包含空值的表，保证结构一致
        Table.FromRecords({[Instrument_Full_Name=null, CFI_Code=null]})
in
    Result

关键改动说明：

• 新增对Data_Response_Code的判断，明确区分200和404的处理逻辑
• 404分支直接返回结构一致的空值表，避免null转换错误
• 200分支仅在状态正常时处理Instrument_Common，逻辑更清晰

如果不需要严格判断状态码，也可以用try...otherwise简化处理：

(id as text)=>
let
    Source = Json.Document(Web.Contents("https://mrsrdac.gdc.standardchartered.com:7778/Instrument?RequestType=ByIdentifier&IdentifierType=ISIN&IdentifierValue="&id&"&Requester=GPTM")),
    Instrument = Source[Instrument],
    Instrument1 = Instrument{0},
    // 尝试提取字段，失败则返回空表
    Result = try
        let
            Value = Instrument1[Instrument_Common],
            ConvertedToTable1 = Record.ToTable(Value),
            FilteredRows = Table.SelectRows(ConvertedToTable1, each ([Name] = "CFI_Code" or [Name] = "Instrument_Full_Name")),
            PivotedColumn = Table.Pivot(FilteredRows, List.Distinct(FilteredRows[Name]), "Name", "Value")
        in
            PivotedColumn
    otherwise
        Table.FromRecords({[Instrument_Full_Name=null, CFI_Code=null]})
in
    Result

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