Power BI中用Web.Contents规避动态数据源问题失败求助

问题背景 #

在Power BI的Power Query中使用M语言Web.Contents函数连接Ruddr API时，遇到动态数据源判定问题，导致无法执行计划刷新。已按照规范将URL路径拆分到RelativePath、查询参数放入Query，且baseUrl设为静态值，但Power BI服务仍判定为动态数据源。

问题排查与修正点 #

1. 全局变量引用触发动态判定 #

原代码中headers定义在全局作用域，被递归函数fetchPage引用。虽然apiKey是参数，但Power BI服务可能因headers是动态拼接生成（结合了apiKey），误判整个请求为动态数据源。

修正方案：将headers的定义移至fetchPage函数内部，确保每次请求的头信息基于传入的参数生成，避免全局变量的动态引用。

2. 递归函数参数传递优化 #

确保递归调用时，所有静态参数（如baseUrl、RelativePath）直接传递，避免间接引用可能导致的动态检测误判。

3. 数据源参数配置验证 #

确认ApiKey是在Power BI中创建的数据源参数（而非普通查询参数），并在服务端正确配置了凭据，避免因参数类型问题触发动态判定。

修正后的完整代码 #

let
    // 从数据源参数获取API密钥
    apiKey = #"ApiKey",
    baseUrl = "https://www.ruddr.io",
    
    // 初始化查询参数
    initialQuery = [
        limit = "100",
        dateAfter = "2024-01-01",
        startingAfter = ""
    ],

    // 静态相对路径
    RelativePath = "/api/workspace/time-entries", 

    // 初始化URL存储列表
    initialUrls = {},

    // 单页数据获取函数（将headers移至内部）
    fetchPage = (urlFetchPage as text, queryFetchPage as record, relPathFetchPage as text, apiKey as text, urls as list) as record =>
        let
            headers = [
                #"Authorization" = "Bearer " & apiKey
            ],
            response = Json.Document(Web.Contents(urlFetchPage, [
                Headers = headers,
                Query = queryFetchPage,
                RelativePath = relPathFetchPage
            ])),
            results = response[results],
            hasMore = response[hasMore],
            lastId = if List.Count(results) > 0 then results{List.Count(results) - 1}[id] else null,
            updatedUrls = List.Distinct(urls & {urlFetchPage})
        in
            [Results = results, HasMore = hasMore, LastId = lastId, Urls = updatedUrls],

    // 递归分页获取函数
    fetchAllPages = (urlFetchPages as text, queryFetchPages as record, relPathFetchPages as text, apiKey as text, urls as list, results as list) as record =>
        let
            pageResult = fetchPage(urlFetchPages, queryFetchPages, relPathFetchPages, apiKey, urls),
            pageResults = pageResult[Results],
            newResults = List.Combine({results, pageResults}),
            updatedQuery = if pageResult[HasMore] and pageResult[LastId] <> null then
                Record.TransformFields(queryFetchPages, {{"startingAfter", each pageResult[LastId]}})
            else
                Record.TransformFields(queryFetchPages, {{"startingAfter", each null}}),
            delay = Function.InvokeAfter(() => null, #duration(0, 0, 0, 0.5)), // 500毫秒延迟避免限流
            updatedUrls = pageResult[Urls]
        in
            if Record.Field(updatedQuery, "startingAfter") <> null then
                @fetchAllPages(urlFetchPages, updatedQuery, relPathFetchPages, apiKey, updatedUrls, newResults)
            else
                [Results = newResults, Urls = updatedUrls],

    // 开始获取所有分页数据
    resultAndUrls = fetchAllPages(baseUrl, initialQuery, RelativePath, apiKey, initialUrls, {}),
    allResults = resultAndUrls[Results],
    allUrls = resultAndUrls[Urls],

    // 将结果转换为表格
    resultTable = Table.FromList(allResults, Splitter.SplitByNothing(), null, null, ExtraValues.Error),

    // 确保字段名唯一的辅助函数
    EnsureUniqueNames = (fieldNames as list) as list =>
        let
            AddSuffix = (name, existingNames) =>
                let
                    count = List.Count(List.Select(existingNames, each _ = name))
                in
                    if count = 0 then name else name & "_" & Text.From(count + 1),
            uniqueNames = List.Accumulate(fieldNames, {}, (state, current) =>
                state & {AddSuffix(current, state)})
        in
            uniqueNames,

    // 递归展开记录列的辅助函数
    ExpandRecordColumns = (table as table, optional parentColumn as text) as table =>
        let
            GetNonNullRecord = (column) =>
                let
                    nonNullValue = List.First(List.Select(Table.Column(table, column), each _ <> null))
                in
                    if Type.Is(Value.Type(nonNullValue), type record) then nonNullValue else null,
            RecordColumns = List.Select(Table.ColumnNames(table), each GetNonNullRecord(_) <> null),
            ExpandTable = List.Accumulate(RecordColumns, table, (state, current) => 
                let
                    record = GetNonNullRecord(current),
                    fieldNames = Record.FieldNames(record),
                    newFieldNames = EnsureUniqueNames(List.Transform(fieldNames, each if parentColumn = null then current & "_" & _ else parentColumn & "_" & current & "_" & _)),
                    expanded = Table.ExpandRecordColumn(state, current, fieldNames, newFieldNames)
                in
                    expanded)
        in
            if List.Count(RecordColumns) = 0 then table else @ExpandRecordColumns(ExpandTable),

    // 格式化列名的辅助函数
    FormatColumnNames = (table as table) as table =>
        let
            removePrefix = (name as text) as text => Text.Replace(name, "Column1_", ""),
            capitalizeWords = (name as text) as text =>
                let
                    words = Text.Split(name, "_"),
                    capitalizedWords = List.Transform(words, each Text.Proper(_)),
                    newName = Text.Combine(capitalizedWords, " ")
                in
                    newName,
            newColumnNames = List.Transform(Table.ColumnNames(table), each capitalizeWords(removePrefix(_))),
            renamedTable = Table.RenameColumns(table, List.Zip({Table.ColumnNames(table), newColumnNames}))
        in
            renamedTable,

    // 展开记录并格式化列名
    expandedTable = ExpandRecordColumns(resultTable),
    finalTable = FormatColumnNames(expandedTable),
    #"Removed Columns" = Table.RemoveColumns(finalTable,{"Timerstartedat", "Createdat", "Role", "Project Client Name", "Project Id", "Project Name", "Project Client Id", "Timeofftype Id", "Invoice Id", "Invoice Number"}),

    // 调试输出（可保留用于验证）
    DebugOutput = [
        Results = #"Removed Columns",
        Urls = allUrls
    ],
    Urls = DebugOutput[Urls]
in
    #"Removed Columns"

额外验证步骤 #

1. 检查请求URL静态性：可临时添加步骤输出Web.Contents生成的请求URL，确认所有请求的baseUrl和RelativePath均为静态值，仅Query参数动态变化。
2. 服务端数据源配置：在Power BI服务的数据集设置中，确认数据源类型为Web，凭据配置正确，且未启用“允许动态数据源”选项（该选项仅作为临时 workaround，不推荐长期使用）。

内容的提问来源于stack exchange，提问作者Yannick De Ceulaer