如何修改Power Query自定义函数读取Providers表NPI列调用CMS API查询数据

第一步：修改自定义函数，增加NPI入参 #

把原来硬编码的NPI值替换为函数入参，修改后的完整函数代码如下：

// 函数名可自定义，这里示例叫GetCMSData
(eAPI as text, NPI as text) =>
let
    Source = Json.Document(Web.Contents("https://data.cms.gov/data-api/v1/dataset/" & eAPI & "/data?filter[Rndrng_NPI]=" & NPI)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    // 这里直接保留需要的字段，省去后续删列步骤
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"Rndrng_NPI", "Rndrng_Prvdr_Last_Org_Name", "Rndrng_Prvdr_First_Name", "Rndrng_Prvdr_MI", "Rndrng_Prvdr_Crdntls", "HCPCS_Cd", "HCPCS_Desc", "HCPCS_Drug_Ind", "Place_Of_Srvc", "Tot_Benes", "Tot_Srvcs", "Tot_Bene_Day_Srvcs", "Avg_Sbmtd_Chrg", "Avg_Mdcr_Alowd_Amt", "Avg_Mdcr_Pymt_Amt", "Avg_Mdcr_Stdzd_Amt"}, {"Rndrng_NPI", "Rndrng_Prvdr_Last_Org_Name", "Rndrng_Prvdr_First_Name", "Rndrng_Prvdr_MI", "Rndrng_Prvdr_Crdntls", "HCPCS_Cd", "HCPCS_Desc", "HCPCS_Drug_Ind", "Place_Of_Srvc", "Tot_Benes", "Tot_Srvcs", "Tot_Bene_Day_Srvcs", "Avg_Sbmtd_Chrg", "Avg_Mdcr_Alowd_Amt", "Avg_Mdcr_Pymt_Amt", "Avg_Mdcr_Stdzd_Amt"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Column1",{{"Avg_Mdcr_Alowd_Amt", Currency.Type}, {"Avg_Mdcr_Pymt_Amt", Currency.Type}, {"Avg_Mdcr_Stdzd_Amt", Currency.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"Avg_Mdcr_Stdzd_Amt", Order.Descending}})
in
    #"Sorted Rows"

第二步：调用Providers表批量查询 #

1. 先将你的「Providers」表导入Power Query，提前把NPI列转换为文本格式，避免拼接URL出错：

= Table.TransformColumns(导入后的Providers表, {{"NPI", Text.From, type text}})

2. 给Providers表添加自定义列，调用上面改好的函数即可，示例公式（替换成你实际的eAPI值）：

= GetCMSData("你的CMS数据集ID", [NPI])

3. 展开自定义列的表数据，就能得到所有NPI对应的查询结果。

注意事项 #

• 运行前需要将Power Query隐私级别设置为「忽略隐私级别以提升性能」，避免跨源调用Web.Contents报错。
• 如果NPI数量较多，可以优化为单次请求传多个NPI，URL格式调整为filter[Rndrng_NPI][in]=NPI1,NPI2,NPI3，大幅减少请求次数、提升查询效率。

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