Forum Discussion
Cant Refresh Dataset
I am attempting to get some data from our Jira Software cloud instance. I found the code listed here on teh Atlassian site and it works great on teh Desktop PowerBI, but when I publish the dataset to the Service and try to schedule a refresh I get an error shown below.
Something went wrong There was an error when processing the data in the dataset. Please try again later or contact support. If you contact support, please provide these details. Data source error: Unable to refresh the model (id=2201454) because it references an unsupported data source. Cluster URI: WABI-WEST-EUROPE-redirect.analysis.windows.net Activity ID: 8985f632-6baf-46a8-925f-e0577539cf36 Request ID: e8b00ccf-5ec4-17a0-27fd-3f959d805e53 Time: 2018-11-05 17:51:51Z
How can we have this query work as a scheduled refresh?
let
yourJiraInstance = "https://yourinstance.jira.com/rest/api/2/",
jql = "&jql=status+not+in+(Cancelled,+Closed,+Done)",
qryBase = yourJiraInstance & "search?maxResults=100" & jql & "&startAt=",
Source = Json.Document(Web.Contents( qryBase & "0" )),
numIssues = Source[total],
startAtList = List.Generate(()=>0, each _ < numIssues, each _ +100),
urlList = List.Transform(startAtList, each qryBase & Text.From(_) ),
data = List.Transform(urlList, each Json.Document(Web.Contents(_))),
iLL = List.Generate(
() => [i=-1, iL={} ],
each [i] < List.Count(data),
each [
i = [i]+1,
iL = data{i}[issues]
],
each [iL]
),
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"Hi Anonymous,
As the error message shows, the data source isn't supported. I would suggest you refer to dynamic-web-contents-and-power-bi-refresh-errors/ and using-the-relativepath-and-query-options-with-web-contents-in-power-query-and-power-bi-m-code/ and makr some changes.
Best Regards,
Dale
7 Replies
- v-jiascu-msftMicrosoft Employee
Hi Anonymous,
As the error message shows, the data source isn't supported. I would suggest you refer to dynamic-web-contents-and-power-bi-refresh-errors/ and using-the-relativepath-and-query-options-with-web-contents-in-power-query-and-power-bi-m-code/ and makr some changes.
Best Regards,
Dale- AnonymousNot applicable
I was able to use these articles to successfully change the "query" in to Jira in a manner that allowed me to schedule refreshes on the PowerBI Service.
Took some tweaking of the actual JQL into Jira and the authentication methods but after a couple of trials and errors got it working.
Thanks!!!
- gidster99Frequent Visitor
Hi Ray - I have the same issues as you decribe... Would you please post your modifed code that allowed you to refresh your JIRA Data via the Power BI Service?
Many thanks