Forum Discussion
JIRA & PowerBI
Hey everyone, I badly need to connect JIRA with Power BI. I did go through a document that has been shared all over the internet but it didn't help me much. For starters, this is the URL that I u...
Anonymous wrote:
Hey everyone,
I badly need to connect JIRA with Power BI. I did go through a document that has been shared all over the internet but it didn't help me much. For starters, this is the URL that I use, when PBI asks me to enter the URL for JIRA.
https://jira.rd.abcpharma.net/secure/RapidBoard.jspa?rapidView=5048&projectKey=SSCL
This is the link to the Kanban board that is in JIRA, and I'd like to bring it to Power BI. Any help would be much appreciated! :)
TIA :)
Anonymous
When connecting to JIRA, the required URL is like "https://xxxxxx.atlassian.net", while clicking next, it acutally calls the API "https://xxxxxx.atlassian.net/rest/api/2/search".
So simplely speaking, at this moment, Power BI now can only get the data that returnbed by API "https://xxxxxx.atlassian.net/rest/api/2/search", As to your link to other JIRA REST APIs, you can do it by calling REST API in Power BI desktop, this would require REST API and programing skill. Check how to call such similar APIs in a demo Get Data from Twitter API with Power Query.
You can also submit your idea at Power BI Ideas and vote it up.
this thread and code was a savior, and a bit more work got it working completely
others items that allowed the completion of the process for me :
- the auth key needed to be [email protected]:JIRA_API_KEY
- changing the last bit of the previous code to remove the data1 = data{0} allowed all data to be retreived.
- converting to table and expanding the fields resulted in all data.
i then removed all the fields i didnt need and expanded some to data.
the code below has a fake auth key
let
Source = Json.Document(Web.Contents("https://siteid.atlassian.net/rest/api/2/search?jql:project=RPSD&maxResults=300", [Headers=[ContentType="application/json", Authorization="Basic cGF1bC5234sdfsdf2345dfgc2dRV0tiQzM4Qg=="]])),
#"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 "https://siteid.atlassian.net/rest/api/2/search?jql:project=RPSD&maxResults=100&startAt=" & Text.From([startAt])),
data = List.Transform(#"Added Custom"[URL], each Json.Document(Web.Contents(_, [Headers=[ContentType="application/json", Authorization="Basic cGF1bC5234sdfsdf2345dfgc2dRV0tiQzM4Qg=="]]))),
#"Converted to Table2" = Table.FromList(data, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Column1 = #"Converted to Table2"[Column1],
#"Converted to Table3" = Table.FromList(Column1, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table3", "Column1", {"expand", "startAt", "maxResults", "total", "issues"}, {"expand", "startAt", "maxResults", "total", "issues"}),
#"Expanded issues" = Table.ExpandListColumn(#"Expanded Column1", "issues"),
#"Expanded issues1" = Table.ExpandRecordColumn(#"Expanded issues", "issues", {"expand", "id", "self", "key", "fields"}, {"expand.1", "id", "self", "key", "fields"}),
#"Expanded fields" = Table.ExpandRecordColumn(#"Expanded issues1", "fields", {"statuscategorychangedate", "issuetype", "timespent", "project", "customfield_11000", "customfield_13465", "fixVersions", "customfield_13464", "customfield_11200", "aggregatetimespent", "customfield_13467", "customfield_13466", "resolution", "customfield_13468", "customfield_12800", "customfield_10501", "customfield_10504", "resolutiondate", "workratio", "watches", "lastViewed", "created", "customfield_13410", "priority", "customfield_10100", "customfield_13412", "customfield_10101", "customfield_13411", "customfield_10102", "customfield_13458", "customfield_10025", "customfield_13457", "labels", "customfield_11303", "customfield_13407", "customfield_11306", "customfield_13406", "aggregatetimeoriginalestimate", "timeestimate", "versions", "issuelinks", "assignee", "updated", "status", "components", "timeoriginalestimate", "customfield_13441", "customfield_13440", "description", "customfield_13443", "customfield_10010", "customfield_13442", "customfield_10011", "customfield_11100", "customfield_13401", "customfield_13400", "customfield_11500", "customfield_13402", "customfield_10006", "customfield_10600", "customfield_12901", "customfield_10007", "security", "customfield_10601", "customfield_12900", "customfield_10008", "customfield_12903", "aggregatetimeestimate", "customfield_10009", "customfield_12902", "customfield_12905", "customfield_12904", "summary", "creator", "subtasks", "customfield_13474", "reporter", "customfield_10000", "customfield_13475", "aggregateprogress", "customfield_10001", "customfield_10002", "environment", "duedate", "progress", "votes"}, {"statuscategorychangedate", "issuetype", "timespent", "project", "customfield_11000", "customfield_13465", "fixVersions", "customfield_13464", "customfield_11200", "aggregatetimespent", "customfield_13467", "customfield_13466", "resolution", "customfield_13468", "customfield_12800", "customfield_10501", "customfield_10504", "resolutiondate", "workratio", "watches", "lastViewed", "created", "customfield_13410", "priority", "customfield_10100", "customfield_13412", "customfield_10101", "customfield_13411", "customfield_10102", "customfield_13458", "customfield_10025", "customfield_13457", "labels", "customfield_11303", "customfield_13407", "customfield_11306", "customfield_13406", "aggregatetimeoriginalestimate", "timeestimate", "versions", "issuelinks", "assignee", "updated", "status", "components", "timeoriginalestimate", "customfield_13441", "customfield_13440", "description", "customfield_13443", "customfield_10010", "customfield_13442", "customfield_10011", "customfield_11100", "customfield_13401", "customfield_13400", "customfield_11500", "customfield_13402", "customfield_10006", "customfield_10600", "customfield_12901", "customfield_10007", "security", "customfield_10601", "customfield_12900", "customfield_10008", "customfield_12903", "aggregatetimeestimate", "customfield_10009", "customfield_12902", "customfield_12905", "customfield_12904", "summary", "creator", "subtasks", "customfield_13474", "reporter", "customfield_10000", "customfield_13475", "aggregateprogress", "customfield_10001", "customfield_10002", "environment", "duedate", "progress", "votes"}),
#"Expanded issuetype" = Table.ExpandRecordColumn(#"Expanded fields", "issuetype", {"name"}, {"issuetype.name"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded issuetype",{"expand", "startAt", "maxResults", "total", "expand.1"}),
#"Expanded project1" = Table.ExpandRecordColumn(#"Removed Columns", "project", {"name"}, {"project.name"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded project1",{{"customfield_13465", "Platform Component"}}),
#"Renamed Columns2" = Table.RenameColumns(#"Renamed Columns1",{{"customfield_13464", "Client Organisation"}}),
#"Expanded Client Organisation" = Table.ExpandRecordColumn(#"Renamed Columns2", "Client Organisation", {"value"}, {"Client Organisation.value"}),
#"Renamed Columns3" = Table.RenameColumns(#"Expanded Client Organisation",{{"customfield_13467", "Client Reference"}}),
#"Expanded resolution" = Table.ExpandRecordColumn(#"Renamed Columns3", "resolution", {"name"}, {"resolution.name"}),
#"Renamed Columns4" = Table.RenameColumns(#"Expanded resolution",{{"customfield_12800", "VSTS ID"}}),
#"Renamed Columns5" = Table.RenameColumns(#"Renamed Columns4",{{"customfield_13410", "Cab Approvers"}}),
#"Expanded priority" = Table.ExpandRecordColumn(#"Renamed Columns5", "priority", {"name", "id"}, {"priority.name", "priority.id"}),
#"Renamed Columns6" = Table.RenameColumns(#"Expanded priority",{{"customfield_13411", "Time to Close"}, {"customfield_10102", "Time to Respond"}}),
#"Renamed Columns7" = Table.RenameColumns(#"Renamed Columns6",{{"customfield_13458", "Time to Investigate"}}),
#"Renamed Columns8" = Table.RenameColumns(#"Renamed Columns7",{{"customfield_10025", "Time To Resolve"}}),
#"Expanded Time To Resolve" = Table.ExpandRecordColumn(#"Renamed Columns8", "Time To Resolve", {"completedCycles"}, {"Time To Resolve.completedCycles"}),
#"Expanded customfield_13457" = Table.ExpandRecordColumn(#"Expanded Time To Resolve", "customfield_13457", {"value"}, {"value"}),
#"Renamed Columns9" = Table.RenameColumns(#"Expanded customfield_13457",{{"value", "Environment"}}),
#"Expanded assignee" = Table.ExpandRecordColumn(#"Renamed Columns9", "assignee", {"displayName"}, {"assignee.displayName"}),
#"Expanded status" = Table.ExpandRecordColumn(#"Expanded assignee", "status", {"name"}, {"status.name"}),
#"Renamed Columns10" = Table.RenameColumns(#"Expanded status",{{"customfield_13401", "Urgency"}}),
#"Renamed Columns11" = Table.RenameColumns(#"Renamed Columns10",{{"customfield_12901", "Change Type"}}),
#"Renamed Columns12" = Table.RenameColumns(#"Renamed Columns11",{{"customfield_12900", "Impact"}}),
#"Expanded customfield_10008" = Table.ExpandRecordColumn(#"Renamed Columns12", "customfield_10008", {"requestType"}, {"customfield_10008.requestType"}),
#"Renamed Columns13" = Table.RenameColumns(#"Expanded customfield_10008",{{"customfield_10008.requestType", "Customer requestType"}}),
#"Expanded Customer requestType" = Table.ExpandRecordColumn(#"Renamed Columns13", "Customer requestType", {"name"}, {"Customer requestType.name"}),
#"Renamed Columns14" = Table.RenameColumns(#"Expanded Customer requestType",{{"customfield_12903", "Change Reason"}}),
#"Renamed Columns15" = Table.RenameColumns(#"Renamed Columns14",{{"customfield_12902", "Change Risk"}, {"customfield_12905", "Change Complete Date"}}),
#"Renamed Columns16" = Table.RenameColumns(#"Renamed Columns15",{{"customfield_12904", "Change Start date"}}),
#"Expanded creator" = Table.ExpandRecordColumn(#"Renamed Columns16", "creator", {"displayName"}, {"creator.displayName"}),
#"Renamed Columns17" = Table.RenameColumns(#"Expanded creator",{{"customfield_13474", "Domain Of Reporter"}}),
#"Expanded reporter" = Table.ExpandRecordColumn(#"Renamed Columns17", "reporter", {"emailAddress"}, {"reporter.emailAddress"}),
#"Renamed Columns18" = Table.RenameColumns(#"Expanded reporter",{{"customfield_10000", "Date of First Response"}}),
#"Renamed Columns19" = Table.RenameColumns(#"Renamed Columns18",{{"customfield_13475", "VSTS Status"}}),
#"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns19",{"customfield_11000", "customfield_11200", "customfield_13466", "customfield_13468", "customfield_10501", "customfield_10504", "customfield_10100", "customfield_13412", "customfield_10101", "customfield_11303", "customfield_13407", "customfield_11306", "customfield_13406", "aggregatetimeoriginalestimate", "timeestimate", "versions", "timeoriginalestimate", "customfield_13441", "customfield_13440", "customfield_13443", "customfield_10010", "customfield_13442", "customfield_10011", "customfield_11100", "customfield_13400", "customfield_11500", "customfield_13402", "customfield_10006", "customfield_10600", "customfield_10007", "customfield_10601", "customfield_10009", "aggregateprogress", "customfield_10001", "customfield_10002", "progress", "votes", "fixVersions", "watches", "Time to Investigate", "Time To Resolve.completedCycles", "issuelinks", "labels", "components", "subtasks"})
in
#"Removed Columns1"Hi,
Anonymous Thanks for sharing this, When i tried using your code basically just by updating the JIRA URL of my need.
I got the following error :
Expression.Error: The 'ContentType' header is only supported when connecting anonymously. These headers can be used with all authentication types: Accept, Accept-Charset, Accept-Encoding, Accept-Language, Cache-Control, Content-Type, If-Modified-Since, Prefer, Range, Referer
So can you please elaborate a bit more on the authentication details as i am very new to this, on how does the authentication of JIRA should be supplied?
Hi,
Anonymous , I obtained my requirement using the experience shared in this link : https://community.atlassian.com/t5/Jira-questions/PowerBI-content-pack-only-pulling-50-rows-from-API/qaq-p/888050 .
So my above open query can be ignored.
Thanks,
Govind