PowerBI数据流刷新问题：Web.BrowserContents动态数据源无法自动刷新

问题背景 #

数据源URL结构：https://www.margonem.pl/ladder/players,Aether?page=2，其中Aether为服务器名称，2为页码。数据集包含多条服务器和页码记录：

最初使用Web.Contents结合RelativePath处理动态参数可正常刷新：

=Table.AddColumn(Source, "Custom", each Web.Page(Web.Contents("https://www.margonem.pl", [RelativePath="/ladder/players,"&[Server]&"?page="&[Page]&""])))

因需要提取约7000个页面的HTML代码，改用Web.BrowserContents并创建自定义函数：

(Server as text, Page as text) =>
let
    #"HTML Code" = Web.BrowserContents("https://www.margonem.pl/ladder/players,"&Server&"?page="&Page&""),
    #"Split Text" = Text.Split(#"HTML Code", "profile"),
    #"Converted to Table" = Table.FromList(#"Split Text", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Converted to Table", "Column1", Splitter.SplitTextByDelimiter("#", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Split Column by Delimiter",{"Column1.1", "Column1.2"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each Text.StartsWith([Column1.1], "/view")),
    #"Inserted Text Between Delimiters" = Table.AddColumn(#"Filtered Rows", "Char", each Text.BetweenDelimiters([Column1.2], " ", "                                ", 35, 0), type text),
    #"Removed Other Columns1" = Table.SelectColumns(#"Inserted Text Between Delimiters",{"Column1.1", "Char"})
in
    #"Removed Other Columns1"

设置自动刷新时收到错误：

无法保存数据流
一个或多个表引用了动态数据源。

解决方案 #

1. 重构函数明确静态基础URL #

修改自定义函数，将基础URL提取为静态常量，让PowerBI识别固定数据源，同时保留动态参数拼接：

(Server as text, Page as text) =>
let
    // 定义静态基础URL
    BaseUrl = "https://www.margonem.pl/ladder/players,",
    // 拼接完整URL
    FullUrl = BaseUrl & Server & "?page=" & Page,
    // 调用Web.BrowserContents并指定Origin头强化静态URL识别
    #"HTML Code" = Web.BrowserContents(FullUrl, [Headers=[#"Origin"=BaseUrl]]),
    // 后续处理逻辑保持不变
    #"Split Text" = Text.Split(#"HTML Code", "profile"),
    #"Converted to Table" = Table.FromList(#"Split Text", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Converted to Table", "Column1", Splitter.SplitTextByDelimiter("#", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6"}),
    #"Removed Other Columns" = Table.SelectColumns(#"Split Column by Delimiter",{"Column1.1", "Column1.2"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", each Text.StartsWith([Column1.1], "/view")),
    #"Inserted Text Between Delimiters" = Table.AddColumn(#"Filtered Rows", "Char", each Text.BetweenDelimiters([Column1.2], " ", "                                ", 35, 0), type text),
    #"Removed Other Columns1" = Table.SelectColumns(#"Inserted Text Between Delimiters",{"Column1.1", "Char"})
in
    #"Removed Other Columns1"

2. 配置PowerBI数据源设置 #

• 在PowerBI Desktop中，点击数据选项卡 → 数据源设置，找到该Web数据源：
  • 设置隐私级别为公共（若目标网站为公开访问）；
  • 勾选“允许数据源刷新”。
• 发布到PowerBI服务后，进入数据集的设置页面：
  • 若使用网关，确保https://www.margonem.pl已添加到网关的允许数据源列表；
  • 确认刷新配置中启用“计划刷新”。

3. 优化批量刷新性能（针对7000页场景） #

为避免被目标网站限流封禁，可在函数中添加延迟步骤：

// 在获取HTML代码后添加延迟
#"Added Delay" = Table.AddColumn(#"HTML Code", "Delay", each Duration.FromSeconds(1)),
#"Waited" = Table.TransformColumns(#"Added Delay", {{"Delay", each WaitFor(_), type null}}),
// 移除延迟列后继续后续处理
#"Removed Delay Column" = Table.RemoveColumns(#"Waited",{"Delay"})

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