MS Fabric Dataflow Gen2调用动态API发布失败：动态数据源不支持

从API查询过去6周数据，通过动态生成URL分批加载，Power Query M代码内部运行正常，但发布到Lakehouse时提示“Dynamic datasources are not supported”错误。原代码如下：

BaseUrl = "https://www.website.com:1111/api/Name/search/week>=",
CurrentWeek = Date.StartOfWeek( DateTime.LocalNow()),
DateSixWeeksAgo = Date.AddDays(CurrentWeek, -42),
YearSixWeeksAgo = Date.Year(DateSixWeeksAgo),
WeekSixWeeksAgo = Date.WeekOfYear(DateSixWeeksAgo),
DynamicUrl = BaseUrl & Text.From(YearSixWeeksAgo) & Text.From(WeekSixWeeksAgo),
Source = Json.Document(Web.Contents(DynamicUrl))

解决方案 #

方法1：拆分URL为基础路径+查询参数 #

Power BI/Dataflow对Web.Contents的全动态URL兼容性差，建议将固定基础路径与动态参数拆分，用Query参数传递动态部分，让系统识别固定数据源：

BaseUrl = "https://www.website.com:1111/api/Name/search",
CurrentWeek = Date.StartOfWeek(DateTime.LocalNow()),
DateSixWeeksAgo = Date.AddDays(CurrentWeek, -42),
YearSixWeeksAgo = Date.Year(DateSixWeeksAgo),
WeekSixWeeksAgo = Date.WeekOfYear(DateSixWeeksAgo),
// 构造查询参数
QueryParams = [week = Text.From(YearSixWeeksAgo) & Text.From(WeekSixWeeksAgo)],
// 通过Query参数传递动态值
Source = Json.Document(Web.Contents(BaseUrl, [Query=QueryParams]))

方法2：手动声明固定数据源路径 #

如果API格式无法拆分查询参数，可通过Headers指定固定数据源路径，告诉系统动态URL属于该固定数据源：

BaseUrl = "https://www.website.com:1111/api/Name/search/week>=",
CurrentWeek = Date.StartOfWeek(DateTime.LocalNow()),
DateSixWeeksAgo = Date.AddDays(CurrentWeek, -42),
YearSixWeeksAgo = Date.Year(DateSixWeeksAgo),
WeekSixWeeksAgo = Date.WeekOfYear(DateSixWeeksAgo),
DynamicUrl = BaseUrl & Text.From(YearSixWeeksAgo) & Text.From(WeekSixWeeksAgo),
// 手动指定固定数据源路径
Source = Json.Document(Web.Contents(DynamicUrl, [Headers=[#"DataSource.Path"=BaseUrl]]))

方法3：使用参数化基础URL #

在Dataflow中创建固定基础URL参数，动态拼接参数部分，系统会将参数识别为固定数据源：

1. 在Dataflow中添加参数BaseApiUrl，值设为https://www.website.com:1111/api/Name/search
2. 修改M代码：

CurrentWeek = Date.StartOfWeek(DateTime.LocalNow()),
DateSixWeeksAgo = Date.AddDays(CurrentWeek, -42),
YearSixWeeksAgo = Date.Year(DateSixWeeksAgo),
WeekSixWeeksAgo = Date.WeekOfYear(DateSixWeeksAgo),
DynamicUrl = BaseApiUrl & "/week>=" & Text.From(YearSixWeeksAgo) & Text.From(WeekSixWeeksAgo),
Source = Json.Document(Web.Contents(DynamicUrl))

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