powerbi - 无法在 Power BI for Jira Reports 中计划数据集刷新

Question

在尝试为我的 Power BI Jira 报表安排数据集刷新时（我正在使用 Power BI Desktop 构建报表并将其发布到我的 Power BI 帐户）。

我收到以下错误：

You can't schedule refresh for this dataset because the following data sources currently don't support refresh:
Data source for Query1

当我检查我的“数据源设置”时，我可以看到警告说Some data sources may not be listed because of hand-authored queries

这是我的 Power BI 查询：

        let 
Source = Json.Document(Web.Contents(JIRA_URL,[RelativePath="/rest/api/2/search",Query=[jql=& TESTCASE_QUERY]])), 
#"Converted to Table" = Record.ToTable(Source), 
#"Transposed Table" = Table.Transpose(#"Converted to Table"), 
#"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]), 
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"expand", type text}, {"startAt", Int64.Type}, {"maxResults", Int64.Type}, {"total", Int64.Type}, {"issues", type any}}), 
#"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"total"}), 
#"total" = #"Removed Other Columns"{0}[total], 
#"startAt List" = List.Generate(()=>0, each _ < #"total", each _ +100), #"Converted to Table1" = Table.FromList(#"startAt List", Splitter.SplitByNothing(), null, null, ExtraValues.Error), 
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table1",{{"Column1", "startAt"}}), 
#"Added Custom" = Table.AddColumn(#"Renamed Columns", "URL", each JIRA_URL & "/rest/api/2/search?maxResults=100&jql=" & TESTCASE_QUERY & "&startAt=" & Text.From([startAt])), data = List.Transform(#"Added Custom"[URL], each Json.Document(Web.Contents(_))), 
#"Converted to TableQuery" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error), 
#"Expanded ColumnIssues" = Table.ExpandRecordColumn(#"Converted to TableQuery", "Column1", {"issues"}, {"issues"}), 
#"Expanded issues" = Table.ExpandListColumn(#"Expanded ColumnIssues", "issues"), 
#"Expanded issues1" = Table.ExpandRecordColumn(#"Expanded issues", "issues", {"id", "key", "fields"}, {"id", "key", "fields"}) in 
#"Expanded issues1"

我也尝试使用相对路径，但是无法解决此问题。是不是因为我在里面又使用了一个查询#"Added Custom" = Table.AddColumn(#"Renamed Columns", "URL", each JIRA_URL & "/rest/api/2/search?maxResults=100&jql=" & QUERY & "&startAt=" & Text.From([startAt])), data = List.Transform(#"Added Custom"[URL], each Json.Document(Web.Contents(_))),

什么可能是最好的解决方案。

我能够使用以下示例代码在我的 PowerBI 桌面中解决上述问题

    let 
Source = Json.Document(
            Web.Contents(JIRA_URL,
            [
                RelativePath="rest/api/2/search",
                Query=
                [
                  maxResults="100",
                  jql= EPICS_QUERY,
                  startAt="0",
                  apikey="MjY0MzgyODgyNDg4OnRnBcfBqhio"
                ]
            ]
)),

numIssues = Source[total],

startAtList = List.Generate(()=>0, each _ < numIssues, each _ +100),
 
data        = List.Transform(startAtList, each Json.Document(Web.Contents(JIRA_URL,
            [
            RelativePath="rest/api/2/search",
            Query=
                [
                  maxResults="100",
                 jql=EPICS_QUERY,
                 startAt=Text.From(_),
                  apikey="MjY0MzgyODgyNDg4OnRnBcfBqhio"
                ]
            ]))),

 iLL = List.Generate(
     () => [i=-1, iL={} ],
     each [i] < List.Count(data),
     each [
         i = [i]+1,
         iL = data{i}[issues]
     ],
     each [iL]
 ),
 // and finally, collapse that list of lists into just a single list (of issues)
 issues = List.Combine(iLL), 
 #"Converted to Table" = Table.FromList(issues, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
 #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"key", "fields"}, {"issue", "fields"}),
 #"Expanded fields" = Table.ExpandRecordColumn(#"Expanded Column1", "fields", {"assignee", "created", "creator", "description", "issuetype", "parent", "priority", "project", "reporter", "resolution", "resolutiondate", "status", "summary", "updated"}, {"assigneeF", "created", "creatorF", "description", "issuetypeF", "parentF", "priorityF", "projectF", "reporterF", "resolutionF", "resolutiondate", "statusF", "summary", "updated"}),
 #"Expanded assignee" = Table.ExpandRecordColumn(#"Expanded fields", "assigneeF", {"key"}, {"assignee"}),
 #"Expanded creator" = Table.ExpandRecordColumn(#"Expanded assignee", "creatorF", {"key"}, {"creator"}),
 #"Expanded issuetype" = Table.ExpandRecordColumn(#"Expanded creator", "issuetypeF", {"name"}, {"issuetype"}),
 #"Expanded priority" = Table.ExpandRecordColumn(#"Expanded issuetype", "priorityF", {"name"}, {"priority"}),
 #"Expanded project" = Table.ExpandRecordColumn(#"Expanded priority", "projectF", {"key"}, {"project"}),
 #"Expanded reporter" = Table.ExpandRecordColumn(#"Expanded project", "reporterF", {"key"}, {"reporter"}),
 #"Expanded resolution" = Table.ExpandRecordColumn(#"Expanded reporter", "resolutionF", {"name"}, {"resolution"}),
 #"Expanded status" = Table.ExpandRecordColumn(#"Expanded resolution", "statusF", {"name"}, {"status"}),
 #"Changed Type" = Table.TransformColumnTypes(#"Expanded status",{{"created", type datetimezone}, {"resolutiondate", type datetimezone}, {"updated", type datetimezone}}),
 #"Expanded parentF" = Table.ExpandRecordColumn(#"Changed Type", "parentF", {"key"}, {"parent"})
in
 #"Expanded parentF"

标签： powerbipowerquerypowerbi-desktoppowerbi-datasource