如何在Power BI和Power Query中用双变量函数替代单变量抓取子URL数据

解决思路 #

你之前从页面解析得到的Style列（即原option标签的Attribute:value属性）本身就是对应车型的专属路径后缀，只需将其与loc列的基础URL拼接为完整的车型页面URL，再抓取数据即可实现精准匹配。

步骤1：修改自定义函数为双参数 #

将原单参数函数调整为接收基础URL、车型路径后缀两个入参，拼接为完整URL后再发起请求，修改后代码如下：

(PageBase as text, StylePath as text)=>
let
    // 拼接生成对应车型的专属URL
    FullUrl = Uri.Combine(PageBase, StylePath),
    Source = Web.BrowserContents(FullUrl),
    #"Extracted Table From Html" = Html.Table(Source, {{"Column1", "SECTION:nth-child(2) > DIV.table-responsive > TABLE.costs-table.text-gray-darker.table.table-borderless > * > TR > :nth-child(1)"}, {"Column2", "SECTION:nth-child(2) > DIV.table-responsive > TABLE.costs-table.text-gray-darker.table.table-borderless > * > TR > :nth-child(2)"}, {"Column3", "SECTION:nth-child(2) > DIV.table-responsive > TABLE.costs-table.text-gray-darker.table.table-borderless > * > TR > :nth-child(3)"}, {"Column4", "SECTION:nth-child(2) > DIV.table-responsive > TABLE.costs-table.text-gray-darker.table.table-borderless > * > TR > :nth-child(4)"}, {"Column5", "SECTION:nth-child(2) > DIV.table-responsive > TABLE.costs-table.text-gray-darker.table.table-borderless > * > TR > :nth-child(5)"}, {"Column6", "SECTION:nth-child(2) > DIV.table-responsive > TABLE.costs-table.text-gray-darker.table.table-borderless > * > TR > :nth-child(6)"}, {"Column7", "SECTION:nth-child(2) > DIV.table-responsive > TABLE.costs-table.text-gray-darker.table.table-borderless > * > TR > :nth-child(7)"}}, [RowSelector="SECTION:nth-child(2) > DIV.table-responsive > TABLE.costs-table.text-gray-darker.table.table-borderless > * > TR"]),
    #"Promoted Headers" = Table.PromoteHeaders(#"Extracted Table From Html", [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"", type text}, {"Year 1", Currency.Type}, {"Year 2", Currency.Type}, {"Year 3", Currency.Type}, {"Year 4", Currency.Type}, {"Year 5", Currency.Type}, {"Total", Currency.Type}})
in
    #"Changed Type"

注意将修改后的函数查询命名为fnGetCostToOwn，和后续主查询调用时的名称保持一致。

步骤2：调整主查询调用逻辑 #

在原有主查询的基础上，删除不需要的大体积中间列，再调用双参数函数即可，修改后主查询示例：

let
    Source = Xml.Tables(Web.Contents("https://www.edmunds.com/sitemap_web54-mmy-cost-to-own.xml")),
    Table0 = Source{0}[Table],
    #"Kept First Rows" = Table.FirstN(Table0,10),
    #"Added Custom" = Table.AddColumn(#"Kept First Rows", "Custom", each Web.BrowserContents([loc])),
    #"Added Custom3" = Table.AddColumn(#"Added Custom", "Custom.3", each try Text.Range([Custom],Text.PositionOf([Custom],"<optgroup"),Text.PositionOf([Custom],"</optgroup>")-Text.PositionOf([Custom],"<optgroup")+11) otherwise "<optgroup/>")),
    #"Parsed XML" = Table.TransformColumns(#"Added Custom3",{{"Custom.3", Xml.Tables}}),
    #"Expanded Custom.3" = Table.ExpandTableColumn(#"Parsed XML", "Custom.3", {"option"}, {"option"}),
    #"Expanded option" = Table.ExpandTableColumn(#"Expanded Custom.3", "option", {"Element:Text", "Attribute:value"}, {"Model", "Style"}),
    // 移除占用内存的页面缓存列，优化性能
    #"Removed Unused Columns" = Table.RemoveColumns(#"Expanded option",{"Custom", "Custom.3"}),
    // 传入两个参数调用自定义函数，加错误捕获避免个别无效路径报错
    #"Added Cost Table" = Table.AddColumn(#"Removed Unused Columns", "持有成本表", each try fnGetCostToOwn([loc], [Style]) otherwise #table({},{}))
in
    #"Added Cost Table"

可选优化 #

可根据需要在自定义函数中增加请求延迟逻辑，避免短时间发起大量请求被站点反爬拦截。

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