I am trying to connect Jira with Power BI through rest API. Below is the code for the same. I have kept xx to maintain the security of the data but the code gives the gist. Now earlier it was working suddnely from last 20 days it is not working. Please anyone can help.
(start_month as text, end_month as text) =>
let
//Testing and config not as function
//start_month = "-1",
//end_month = "0",
// Set your Jira Cloud URL details - change 'yourDomain' as appropriate
yourJiraInstance = "https://atlassian.xx/rest/api/2/",
// ===== Firstly set the scope of your preferred query =======================
jql = "&jql=project=FICS and ""createdDate"" >= startOfMonth(" & start_month & ") and ""createdDate"" < startOfMonth(" & end_month & ")",
// Limit the fields to return
query_fields ="&fields=key, issuetype,status, assignee, description, aggregatetimespent, customfield_21100, customfield_21204, customfield_24700, customfield_21101, type , customfield_31709, customfield_24700, created, customfield_26206, customfield_28505",
// So lets construct the actual base query string to send to Jira
qryBase = yourJiraInstance & "search?maxResults=10000" & jql & query_fields & "&startAt=",
// ===== Pull the records from Jira Cloud ======
Source = Json.Document(Web.Contents( qryBase & "0" )),
numIssues = Source[total],
// Now it is time to build a list of startAt values, starting on 0, incrementing 10000 per item
startAtList = List.Generate(()=>0, each _ < numIssues, each _ +10000),
urlList = List.Transform(startAtList, each qryBase & Text.From(_) ),
data = List.Transform(urlList, each Json.Document(Web.Contents(_))),
// ===== Consolidate records into a single list ======
// so we have all the records in data, but it is in a bunch of lists each 10000 records
// long. The issues will be more useful to us if they're consolidated into one long list
//
// In essence we need extract the separate lists of issues in each data{i}[issues] for 0<=i<#"total"
// and concatenate those into single list of issues .. from which then we can analyse
//
// so first create a single list that has as its members each sub-list of the issues,
// 100 in each except for the last one that will have just the residual list.
// So iLL is a List of Lists (of issues):
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"}, {"Column1.key", "Column1.fields"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Column1",{{"Column1.key", "JIRA Key"}}),
#"Expanded Column1.fields" =xxxxxx,
#"Expanded Column1.fields.issuetype" = Table.ExpandRecordColumn(#"xx"}),
#"Renamed Columns1" = Table.RenameColumns(#"Expanded Column1.fields.issuetype",{{"Column1.fields.issuetype.name", "Issue Type"}, {"Column1.fields.customfield_31709", "Artifact Complete Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns1",{{"xx}}),
#"Renamed Columns2" = Table.RenameColumns(#"Changed Type",{{"xx"}, {"xx"}, {"Column1.fields.description", "Description"}}),
#"Expanded Column1.fields.assignee" = Table.ExpandRecordColumn(#"xx"}),
in
#"Renamed Columns8"
Now this is giving error:
An error occurred in the ‘’ query. DataSource.Error: Web.Contents failed to get contents from 'https://atlassian.xx.com/rest/api/2/search?maxResults=10000&jql=project=FICS%20and%20%22createdDate%...' (400):
Details:
DataSourceKind=Web
DataSourcePath=https://atlassian.xx.com/rest/api/2/search
Url=https://atlassian.xx.com/rest/api/2/search?maxResults=10000&jql=project=FICS%20and%20%22createdDate%...
