I will explain how to get testing tickets in Jira using the Xray cloud Rest API including test plans, test executions and test runs but if you are looking to import Jira tickets, read Power BI: connect to the Jira API v3 database.

I will explain 2 ways to do it, the option 1 is about to get few tickets (for instance, to get the last 250 tickets by using 3 times the code) but if I want to get all tickets, use the option 2. Before to start, I have to be “admin” or “owner” of the Jira project and I need to get those 3 main information:

• The project key (for instance TEST) or the project ID (for instance 11111). To know the project ID: https://yourjira.atlassian.net/rest/api/latest/project/project_key (for instance project_key = TEST so project_id = 11111)
  
• The client ID and the client secret, read Xray Global Settings - API Keys

The following steps are for both options.

Open Power BI and click on “home -> get data -> blank query”:

It will open Power Query Editor then click on “advanced editor”:

Delete everything:

Option 1 for small data

Put the following code and follow the instructions in the code:

let
    AuthResponse = Web.Contents("https://xray.cloud.getxray.app/", [RelativePath = "api/v2/authenticate",
        Headers = [#"Content-Type" = "application/json"],Content = Text.ToBinary
        // change your_client_id and your_client_secret by yours
        ("{""client_id"":""your_client_id"",""client_secret"":""your_client_secret""}")]),
    GetToken = Text.Replace(Text.Replace(Text.FromBinary(AuthResponse), """", ""),"Bearer ",""),
    query =
      // change your_project_key by yours and for muliple projects (jql: ""project in (your_project_key1, your_project_key2)"", start...)
      // to use project ID, replace (jql: ""project = your_project_key"", start...) by (projectId: ""your_project_id"", start...)
      // to use ticket key, replace (jql: ""project = your_project_key"", start...) by (jql: ""key = your_ticket_key"", start...)
      // to get next tickets, change start: 0 by start: 100, start: 200, etc.
      // to add more fields/columns, put inside results
      "{ 
        getTests (jql: ""project = your_project_key"", start: 0, limit: 100) {
          start
          limit
          total
          results {
            issueId
            projectId   
            status {name} 
            testType {name}
            steps {action data result}
            jira (fields: [""key"", ""created"", ""updated"", ""resolutiondate""])
          }
        } 
      }",
    source = Web.Contents("https://xray.cloud.getxray.app/", [RelativePath = "api/v2/graphql",
        Headers = [#"Content-Type"="application/json", #"Authorization"="Bearer " & GetToken],Content = Json.FromValue([query = query])]),
    issues = Record.ToTable(Json.Document(source)[data]){0}[Value],
    #"Converted to Table" = Table.FromList(issues[results], Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"           
              

The code above is to get test tickets but to get test plan and/or test execution tickets, modify the code as in the picture:

getTestPlans	getTestExecutions

To get all tickets of sub-sections (the tests, testPlans, testExecutions and/or testRuns inside “results”) for test plans and/or test executions, there are 2 alternatives, the first one is to use multiple times the code by changing the start of the corresponding sub-section and the second one, get all with one single code without change it but the code works for a ticket and not a project and one sub-section per code for a better performance. For instance, I have a data of 294 test plan tickets and only test-11 has 134 tests and 67 test executions.

Alternative 1, get all 134 tests with multiple codes

Alternative 2, get all 134 tests with 1 code using ticket key (for instance TEST-1) and excluding testExecutions. To get testExecutions for test plan, follow instructions in the code and for a project, check the option 2.

let
    AuthResponse = Web.Contents("https://xray.cloud.getxray.app/", [RelativePath = "api/v2/authenticate",
        Headers = [#"Content-Type" = "application/json"],Content = Text.ToBinary
        // change your_client_id and your_client_secret by yours
        ("{""client_id"":""your_client_id"",""client_secret"":""your_client_secret""}")]),
    GetToken = Text.Replace(Text.Replace(Text.FromBinary(AuthResponse), """", ""),"Bearer ",""),
    GetPage = (start as number) =>
        let
            // change your_project_key by yours
            // keep tests or testExecutions, not both
            Query = "query {
                getTestPlans (jql: ""key = your_ticket_key"", start: 0, limit: 100) {
                    start limit total results {
                        issueId projectId jira (fields: [""key"", ""created"", ""updated"", ""resolutiondate""])
                        tests (start: " & Number.ToText(start) & ", limit: 100) {total results {
                            status {name}
                            testType {name}
                            steps {action data result}
                            jira (fields: [""key""])
                        }}
                        testExecutions (start: " & Number.ToText(start) & ", limit: 100) {total results {
                            testEnvironments
                            jira (fields: [""key""])
                        }}                        
            }}}",
            source = Json.Document(Web.Contents("https://xray.cloud.getxray.app/", [RelativePath = "api/v2/graphql",
                Headers = [#"Content-Type"="application/json", #"Authorization"="Bearer " & GetToken],Content = Json.FromValue([query = Query])])),
            Mainkey = source[data][getTestPlans][results]{0}[jira][key],
            Mainid = source[data][getTestPlans][results]{0}[issueId],
            Mainpro = source[data][getTestPlans][results]{0}[projectId],
            Maincrea = source[data][getTestPlans][results]{0}[jira][created],
            Mainupd = source[data][getTestPlans][results]{0}[jira][updated],
            Mainres = source[data][getTestPlans][results]{0}[jira][resolutiondate],
            // if keep testExecutions, change [tests] by [testExecutions]
            Subresults = source[data][getTestPlans][results]{0}[tests][results],
            Subtotal = source[data][getTestPlans][results]{0}[tests][total]
        in
            [Ticketkey = Mainkey, Ticketid = Mainid, Projectid = Mainpro, CreatedDate = Maincrea, UpdatedDate = Mainupd,
                ResolvedDate = Mainres, Results = Subresults, Total = Subtotal],
    AllResults = List.Combine(List.Transform(List.Generate(() => 0, each _ < GetPage(0)[Total], each _ + 100), each GetPage(_)[Results])),
    #"Converted to Table" = Table.FromList(AllResults, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Added Custom" = Table.AddColumn(#"Converted to Table", "Test plan", each GetPage(0)[Ticketkey], type text),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "issueId", each GetPage(0)[Ticketid], type text),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "projectId", each GetPage(0)[Projectid], type text),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Created", each GetPage(0)[CreatedDate], type text),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Updated", each GetPage(0)[UpdatedDate], type text),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Resolved", each GetPage(0)[ResolvedDate], type text)
in
    #"Added Custom5"            
              

This is to get tests for test execution:

Option 2 for big data

Put the following code and follow the instructions in the code:

let
    AuthResponse = Web.Contents("https://xray.cloud.getxray.app/", [RelativePath = "api/v2/authenticate",
        Headers = [#"Content-Type" = "application/json"],Content = Text.ToBinary
        // change your_client_id and your_client_secret by yours
        ("{""client_id"":""your_client_id"",""client_secret"":""your_client_secret""}")]),
    GetToken = Text.Replace(Text.Replace(Text.FromBinary(AuthResponse), """", ""),"Bearer ",""),
    // change your_project_key by yours and for muliple projects (jql: ""project in (your_project_key1, your_project_key2)"", limit...)
    // to use project ID, replace (jql: ""project = your_project_key"", limit...) by (projectId: ""your_project_id"", limit...)
    TotalQuery = "{getTests (jql: ""project = your_project_key"", limit: 100) {total}}",
    TotalResponse = Json.Document(Web.Contents("https://xray.cloud.getxray.app/", [RelativePath = "api/v2/graphql",
        Headers = [#"Content-Type"="application/json", #"Authorization"="Bearer " & GetToken], Content = Json.FromValue([query = TotalQuery])])),
    TotalFinal = TotalResponse[data][getTests][total],
    Pages = List.Numbers(0, Number.RoundUp(TotalFinal/100), 100),
    GetPage = (start as number) =>
        let
            // change your_project_key by yours and for muliple projects (jql: ""project in (your_project_key1, your_project_key2)"", start...)
            // to use project ID, replace (jql: ""project = your_project_key"", start...) by (projectId: ""your_project_id"", start...)
            // to use ticket key, replace (jql: ""project = your_project_key"", start...) by (jql: ""key = your_ticket_key"", start...)
            // to add more fields/columns, put inside results
            query = "query ($start: Int!, $limit: Int!) {
                getTests (jql: ""project = your_project_key"", start: $start, limit: $limit) {
                    results {
                        issueId
                        projectId
			            status {name}
                        testType {name}
                        steps {action data result}
                        jira (fields: [""key"", ""created"", ""updated"", ""resolutiondate""])
                    }
                }
            }",
            response = Json.Document(Web.Contents("https://xray.cloud.getxray.app/", [RelativePath = "api/v2/graphql",
                Headers = [#"Content-Type"="application/json", #"Authorization"="Bearer " & GetToken],
                Content = Json.FromValue([query = query, variables = [start = start, limit = 100]])])),
            results = response[data][getTests][results]
        in
            results,
    AllResults = List.Combine(List.Transform(Pages, each GetPage(_))),
    #"Converted to Table" = Table.FromList(AllResults, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"
              

The code above is to get test tickets but to get test plan and/or test execution tickets, modify the code as in the picture:

getTestPlans	getTestExecutions

To get all related tickets of sub-sections (the tests, testPlans, testExecutions and/or testRuns inside “results”) for test plans and/or test executions, there are 2 alternatives, the first one is to use multiple times the code by changing the start of the corresponding sub-section and the second one, get all with one single code without change it but one sub-section per code for a better performance. For instance, I have a data of 294 test plan tickets and only test-11 has 134 tests and 67 test executions.

Alternative 1, get all 134 tests with multiple codes

Alternative 2, get all 134 tests with 1 code excluding testExecutions. To get testExecutions for test plan, follow instructions in the code.

let
    AuthResponse = Web.Contents("https://xray.cloud.getxray.app/", [RelativePath = "api/v2/authenticate",
        Headers = [#"Content-Type" = "application/json"],Content = Text.ToBinary
        // change your_client_id and your_client_secret by yours
        ("{""client_id"":""your_client_id"",""client_secret"":""your_client_secret""}")]),
    GetToken = Text.Replace(Text.Replace(Text.FromBinary(AuthResponse), """", ""),"Bearer ",""),
    GetPage1 = (start as number) =>
        // change your_project_key by yours
        let Query = "query {getTestPlans (jql: ""project = your_project_key"", start: " & Number.ToText(start) & ", limit: 100) {
                    total results {issueId projectId jira (fields: [""key"", ""created"", ""updated"", ""resolutiondate""])
            }}}",
            response1 = Json.Document(Web.Contents("https://xray.cloud.getxray.app/", [RelativePath = "api/v2/graphql",
                Headers = [#"Content-Type"="application/json", #"Authorization"="Bearer " & GetToken], Content = Json.FromValue([query = Query])])),
            Total1 = response1[data][getTestPlans][total],
            Results1 = response1[data][getTestPlans][results]
        in [Total1 = Total1, Results1 = Results1],
    AllResults1 = List.Combine(List.Transform(List.Generate(() => 0, each _ < GetPage1(0)[Total1], each _ + 100), each GetPage1(_)[Results1])),
    GetPage2 = (TicketID as text, ProjectID as text, TicketKey as text, CreatedDate as text, UpdatedDate as text, ResolvedDate as nullable text) =>
        let GetPage3 = (start as number) =>
                // keep tests or testExecutions, not both
                let Query = "query {getTestPlans (jql: ""key = " & TicketKey & """, start: 0, limit: 100)  {results {
                        tests (limit: 100, start: " & Number.ToText(start) & ") {total results {
                            issueId
                            status {name}
                            testType {name}
                            steps {action data result}
                            jira (fields: [""key""])
                        }}
                        testExecutions (limit: 100, start: " & Number.ToText(start) & ") {total results {
                            testEnvironments
                            jira (fields: [""key""])
                        }}                            
                    }}}",
                    response2 = Json.Document(Web.Contents("https://xray.cloud.getxray.app/", [RelativePath = "api/v2/graphql",
                        Headers = [#"Content-Type"="application/json", #"Authorization"="Bearer " & GetToken], Content = Json.FromValue([query = Query])])),
                    // if keep testExecutions, change [tests] by [testExecutions]
                    Total2 = response2[data][getTestPlans][results]{0}[tests][total],
                    Results2 = try response2[data][getTestPlans][results]{0}[tests][results] otherwise {}
                in [Total2 = Total2, Results2 = Results2],
            // if keep testExecutions, change {[issueId=null, status=null, testType=null, steps=null, jira=[key=null]]} by {[testEnvironments={}, jira=[key=null]]}
            AllTickets = if GetPage3(0)[Total2] = 0 then {[issueId=null, status=null, testType=null, steps=null, jira=[key=null]]} else
                List.Combine(List.Transform(List.Generate(() => 0, each _ < GetPage3(0)[Total2], each _ + 100), each GetPage3(_)[Results2])),
            AddTicketKey = List.Transform(AllTickets, each Record.AddField(Record.AddField(Record.AddField(Record.AddField(Record.AddField(Record.AddField(_, "TestPlanId", TicketID),
                "TestPlanProject", ProjectID), "TestPlanKey", TicketKey), "TestPlanCreated", CreatedDate), "TestPlanUpdated", UpdatedDate), "TestPlanResolved", ResolvedDate))
        in AddTicketKey,
    AllResults2 = List.Combine(List.Transform(AllResults1, each GetPage2(Text.From(_[issueId]),_[projectId],_[jira][key],_[jira][created],_[jira][updated],_[jira][resolutiondate]))),
    #"Converted to Table" = Table.FromList(AllResults2, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"              
              

This is to get tests for test execution:

NOTE for both options:

• For getTestPlans and getTestExecutions, I include the sub-sections but not for getTests (the main code) because I won't get all sub-section tickets but if you don't care, you can add them
• To avoid duplicate tickets, remove the sub-sections
• For testExecutions, only keep tests or testRuns sub-section:
  • tests: this sub-section includes the status and ticket ID
  • testRuns: this sub-section includes only the status

Back for both options, edit my credentials (only once):

Select my data privacy (only once):

Expand the columns by clicking on this icon:

Click “OK”:

About the 2 types of columns:

• List: select “expand to new rows” if I want to have each value per row or select “extract values” if I want to have all values in 1 single row
  
  NOTE: if “extract values” gives error values, it means that there are sub-columns so to see them, select “expand to new rows”.
• Record: to display sub-columns of the main column
  

Once published in Power BI Service, in “data source credentials”, click on this option:

Configure like in the picture then click on “sign in”:

Once done, I can configure the automatic refresh.