Hey @GauravSinghPBI,
Try this: -
let
// === Configuration ===
BaseUrl = "https://natwest.atlassian.net/rest/api/3/search?",
JQL = "jql=project=PPPP AND created >= -365d ORDER BY created ASC",
PageSize = 100,
Email = "my email", // Replace with your Atlassian email
APIToken = "Actual API token", // Replace with your API token
// === Function to Get a Page of Issues ===
GetPage = (StartAt as number) =>
let
Url = BaseUrl & JQL & "&startAt=" & Number.ToText(StartAt) & "&maxResults=" & Number.ToText(PageSize),
// Add error handling for API calls
Source = try Json.Document(Web.Contents(Url, [
Headers = [
#"Authorization" = "Basic " & Binary.ToText(Text.ToBinary(Email & ":" & APIToken), BinaryEncoding.Base64),
#"Accept" = "application/json"
]
])) otherwise [issues = {}, total = 0],
Issues = if Record.HasFields(Source, "issues") then Source[issues] else {},
Total = if Record.HasFields(Source, "total") then Source[total] else 0
in
[issues = Issues, total = Total, startAt = StartAt],
// === Get First Page to Determine Total Count ===
FirstPage = GetPage(0),
TotalRecords = FirstPage[total],
// === Generate All Pages Without Duplication ===
AllPages = List.Generate(
() => [currentStart = 0, pageData = FirstPage],
each [currentStart] < TotalRecords,
each [
currentStart = [currentStart] + PageSize,
pageData = if [currentStart] < TotalRecords then GetPage([currentStart]) else [issues = {}]
],
each [pageData][issues]
),
// === Flatten All Issues ===
AllIssues = List.Combine(AllPages),
// === Process Data Only If Issues Exist ===
FinalResult = if List.Count(AllIssues) > 0 then
let
// === Convert to Table ===
IssuesTable = Table.FromList(AllIssues, Splitter.SplitByNothing(), {"Issue"}),
// === Expand Main Fields ===
ExpandedIssues = Table.ExpandRecordColumn(IssuesTable, "Issue", {"key", "fields"}),
// === Add Derived Columns with Safe Field Access ===
WithKey = Table.AddColumn(ExpandedIssues, "Key", each
if Record.HasFields([fields], "key") then [key] else null, type text),
WithSummary = Table.AddColumn(WithKey, "Summary", each
if Record.HasFields([fields], "summary") then [fields][summary] else null, type text),
WithCreated = Table.AddColumn(WithSummary, "Created", each
if Record.HasFields([fields], "created") then
try DateTime.FromText([fields][created]) otherwise null
else null, type nullable datetime),
WithUpdated = Table.AddColumn(WithCreated, "Updated", each
if Record.HasFields([fields], "updated") then
try DateTime.FromText([fields][updated]) otherwise null
else null, type nullable datetime),
WithStatus = Table.AddColumn(WithUpdated, "Status Name", each
if Record.HasFields([fields], "status") and [fields][status] <> null then
if Record.HasFields([fields][status], "name") then [fields][status][name] else null
else null, type text),
WithAssignee = Table.AddColumn(WithStatus, "Assignee Name", each
if Record.HasFields([fields], "assignee") and [fields][assignee] <> null then
if Record.HasFields([fields][assignee], "displayName") then [fields][assignee][displayName] else null
else null, type text),
WithSprint = Table.AddColumn(WithAssignee, "Sprint", each
if Record.HasFields([fields], "customfield_10007") then
// Sprint field can be complex, handle accordingly
let SprintValue = [fields][customfield_10007]
in if SprintValue <> null then Text.From(SprintValue) else null
else null, type text),
// === Remove Intermediate Columns ===
CleanedTable = Table.RemoveColumns(WithSprint, {"Issue", "fields"})
in
CleanedTable
else
// === Return Empty Table with Correct Schema ===
Table.FromRecords({}, type table [
Key = text,
Summary = text,
Created = nullable datetime,
Updated = nullable datetime,
#"Status Name" = text,
#"Assignee Name" = text,
Sprint = text
])
in
FinalResult
Fixed? ✓ Mark it • Share it • Help others!
Best Regards,
Jainesh Poojara | Power BI Developer
