PowerBI服务中Jira动态查询刷新失败问题求助

问题场景 #

通过Power Query从SharePoint列表提取Issue-ID生成JQL调用Jira API，桌面端刷新正常，但发布至Power BI服务后手动刷新失败，提示动态数据源错误。使用Jira Email、JiraToken、JiraHost和JiraApiBase作为参数，Features为获取SharePoint列表数据的查询。

相关查询代码 #

Query: JiraKeys #

let
    ListItems = Features,
    Filtered = Table.SelectRows(ListItems, each [Rally_Jira] = "Jira"),
    Keys     = List.Buffer(List.Distinct(Filtered[Feature_ID]))
in
    Keys

Query: fnJiraGetJson #

// Query name: fnJiraGetJson
// Purpose: Reusable Jira Cloud GET with Basic auth and optional query params

let
    fnJiraGetJson = (relPath as text, optional query as nullable record) =>
    let
        // Read parameter values (use #"" if your parameter names have spaces)
        _Host    = JiraHost,
        _ApiBase = JiraApiBase,
        _Email   = JiraEmail,
        _Token   = JiraToken,
        
        // Build Basic auth header (email:token)
        _AuthHeader = "Basic " &
            Binary.ToText(Text.ToBinary(_Email & ":" & _Token), BinaryEncoding.Base64),

        // Only include Query when provided
        _OptionsBase = [
            RelativePath = _ApiBase & relPath,
            Headers      = [ Authorization = _AuthHeader, Accept = "application/json" ]
        ],
        _Options = if query = null then _OptionsBase else _OptionsBase & [ Query = query ],

        // Call Jira
        _Response = Web.Contents(_Host, _Options),
        _Json     = Json.Document(_Response)
    in
        _Json
in
    fnJiraGetJson

Query: JiraIssuesBatch #

// Query name: JiraIssuesBatch
let
    // Keys from SharePoint query (unchanged elsewhere) — buffer to avoid combination violations
    KeysList = JiraKeys,

    // Build JQL
    Jql      = "issuekey in (" & Text.Combine(KeysList, ",") & ")",

    // NEW endpoint path and fields kept explicit
    Json     = fnJiraGetJson(
                  "search/jql",
                  [
                    jql        = Jql,
                    maxResults = "100",
                    fields     = "summary,status,issuetype,priority,created,updated"
                  ]
               ),

    Issues   = Json[issues],
    ToTable  = Table.FromList(Issues, Splitter.SplitByNothing(), {"issue"}),
    Expand1  = Table.ExpandRecordColumn(ToTable, "issue", {"key","fields"}, {"key","fields"}),
    Expand2  = Table.ExpandRecordColumn(
                  Expand1, "fields",
                  {"summary","status","issuetype","priority","created","updated"},
                  {"summary","status","issuetype","priority","created","updated"}
               )
in
    Expand2

报错信息 #

Something went wrong
This dataset includes a dynamic data source. Since dynamic data sources aren't refreshed in the Power BI service, this dataset won't be refreshed. Learn more: https://aka.ms/dynamic-data-sources

解决方法 #

1. 确保JiraHost参数为静态不可变值 #

在Power BI参数设置中，将JiraHost参数类型设为文本，设置固定值（如https://your-domain.atlassian.net），禁用“允许在刷新时更改”选项，让Power BI服务识别该URL为静态根路径。

2. 修改fnJiraGetJson函数，标记参数化查询 #

更新函数，添加IsParameterQuery = true选项，告知Power BI服务Query参数为动态参数而非动态路径，避免被判定为动态数据源：

// Query name: fnJiraGetJson
// Purpose: Reusable Jira Cloud GET with Basic auth and optional query params
let
    fnJiraGetJson = (relPath as text, optional query as nullable record) =>
    let
        _Host    = JiraHost,
        _ApiBase = JiraApiBase,
        _Email   = JiraEmail,
        _Token   = JiraToken,
        
        _AuthHeader = "Basic " & Binary.ToText(Text.ToBinary(_Email & ":" & _Token), BinaryEncoding.Base64),

        // 构建静态相对路径，动态参数仅放入Query
        _FullRelativePath = _ApiBase & relPath,
        _Options = [
            RelativePath = _FullRelativePath,
            Headers = [ Authorization = _AuthHeader, Accept = "application/json" ],
            // 标记Query为参数化查询，避免动态数据源检测
            IsParameterQuery = true
        ] & if query <> null then [ Query = query ] else [],

        _Response = Web.Contents(_Host, _Options),
        _Json     = Json.Document(_Response)
    in
        _Json
in
    fnJiraGetJson

3. 配置Power BI服务数据源凭据 #

发布数据集后，进入Power BI服务的数据集设置页面，分别配置SharePoint和Jira的数据源凭据，确保权限足够完成刷新。

4. 检查隐私级别设置（可选） #

在Power Query编辑器中，依次点击文件>选项和设置>查询选项>隐私，选择忽略隐私级别检查，避免因隐私级别冲突导致的刷新问题。

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