如何对Excel表格每行执行Web JSON查询获取加州销售税税率信息

需求背景 #

需要验证Excel表格中交易对应的销售税税率，Excel表格包含Address列，计划调用加利福尼亚州销售税API，获取返回JSON中的税率、辖区等信息，追加到原表格每行对应位置，要求使用每行自身数据构造查询请求、逐行执行查询。
示例查询接口为：https://services.maps.cdtfa.ca.gov/api/taxrate/GetRateByAddress?Address=1313%20Disneyland%20Dr&City=Anaheim&Zip=92802

已解决问题 #

此前不清楚如何将表格行内数据作为查询参数，目前已通过创建自定义函数、将函数作为新列调用，再展开返回的JSON数据得到了基础的预期结果。

现存问题 #

• 合并总税率包含1个或多个税率项时，返回的JSON格式存在差异，不清楚如何实现条件转换
• 编写的try...otherwise逻辑未达预期，当无接口返回时转换空JSON会报错

当前自定义函数代码 #

(URLParam as text) =>
let
    Source = try Json.Document(Web.Contents(URLParam), 65001) otherwise MissingField.UseNull,
    rate = Source[taxRateInfo]{0},
    #"Converted to Table" = Record.ToTable(rate),
    #"Transposed Table" = Table.Transpose(#"Converted to Table"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"jurisdiction", type text}, {"rate", type number}, {"city", type text}, {"county", type text}})
in
    #"Changed Type"

修复方案 #

调整后自定义函数代码 #

针对问题做两点核心修改：1. 扩大异常捕获范围，覆盖请求、解析、字段取值全流程，出错时返回空表避免后续展开报错；2. 兼容多税率场景，先判断taxRateInfo数组长度，同时支持保留单条辖区税率和计算合并总税率：

(URLParam as text) as table =>
let
    RunQuery = try let
        Source = Json.Document(Web.Contents(URLParam), 65001),
        // 先校验taxRateInfo字段存在性
        TaxRateList = if Record.HasFields(Source, "taxRateInfo") then Source[taxRateInfo] else {},
        Result = if List.IsEmpty(TaxRateList) then #table({"jurisdiction", "rate", "city", "county", "total_rate"}, {}) else 
            let
                // 计算所有税率项的合并总税率
                TotalRate = List.Sum(TaxRateList[rate]),
                // 取首个税率项作为基础信息，也可替换为Table.FromRecords(TaxRateList)保留所有辖区条目
                BaseRecord = TaxRateList{0},
                FinalRecord = Record.AddField(BaseRecord, "total_rate", TotalRate),
                // 格式化转表
                #"Converted to Table" = Record.ToTable(FinalRecord),
                #"Transposed Table" = Table.Transpose(#"Converted to Table"),
                #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
                #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{
                    {"jurisdiction", type text}, 
                    {"rate", type number}, 
                    {"city", type text}, 
                    {"county", type text},
                    {"total_rate", type number}
                })
            in
                #"Changed Type"
    // 全流程异常兜底返回空表
    in Result otherwise #table({"jurisdiction", "rate", "city", "county", "total_rate"}, {})
in
    RunQuery

补充使用提示 #

• 构造请求参数时建议用Uri.EscapeDataString()函数包裹地址、城市、邮编值，避免特殊字符导致请求失败
• 如果需要保留所有辖区的税率条目，直接用Table.FromRecords(TaxRateList)替换单条记录转换逻辑即可，不需要额外转置操作

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