How to pull Jira Report into Excel

In IT industry we are using Jira for tyask management and we need to pull status reports frequently. Below are the steps can be used to get the report from Jira into Excel:

1. Login to https://jira.mycompany.com
2. Create a filter to get your selected issues and desired. (Example URL: https://mycompany.atlassian.net/issues/?filter=12345 )
3. Selected Export dropdown menu
4. Right-clicked on the CSV (Current fields or All fields) option and copy link
5. Copied the CSV URL (https://jira.mycompany.com/sr/jira.issueviews:searchrequest-csv-current-fields/12345/SearchRequest-12345.csv)
6. Open Excel Workbook
7. Select the Data tab
8. Select Get Data -> From Web
9. Pasted the CSV URL in the Web Address box (Basic selected), clicked OK

From here, the preview window was showing up empty so make sure that you have the correct permissions set by doing the following:

1. Select Edit on the preview window
2. Select Data source settings
3. Select Edit Permissions
4. Select Edit (Permissions were already set to Basic)
5. Select Basic from the side panel on the left
6. Enter jira.mycompany.com credentials
7. Click Save and closed the windows, returning to the Power Query Editor
8. Click Refresh Preview

You can perform few more steps as well like –
Remove few columns
Add a new column and concatenate all Label columns into this new column. Also can have a new column named as Comment
Then might need to change the column order.

Here is sample power query:

let
    Source = Csv.Document(Web.Contents("https://jira.mycompany.com/sr/jira.issueviews:searchrequest-csv-current-fields/12345/SearchRequest-12345.csv"),[Delimiter=",", Columns=26, Encoding=65001, QuoteStyle=QuoteStyle.None]), 

    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),

    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Issue Type", type text}, {"Issue key", type text}, {"Issue id", Int64.Type}, {"Status", type text}, {"Assignee", type text}, {"Summary", type text}, {"Due Date", type datetime}, {"Reporter", type text}, {"Priority", type text}, {"Created", type datetime}, {"Updated", type datetime}, {"Resolved", type text}, {"Custom field (Epic Link)", type text}, {"Component/s", type text}, {"Component/s_1", type text}, {"Labels", type text}, {"Labels_2", type text}, {"Labels_3", type text}, {"Labels_4", type text}, {"Project key", type text}, {"Project name", type text}, {"Project type", type text}, {"Project lead", type text}, {"Project description", type text}, {"Project url", type text}, {"Environment", type text}}),


    #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"Issue key", "Status", "Assignee", "Summary", "Due Date", "Reporter", "Priority", "Created", "Updated", "Resolved", "Custom field (Epic Link)", "Component/s", "Component/s_1", "Labels", "Labels_2", "Labels_3", "Labels_4"}, MissingField.Ignore),


    #"Added Custom" = Table.AddColumn(#"Removed Other Columns", "All_Labels", each ([Labels] & ", " & [Labels_2] & ", " & [Labels_3] & ", " & [Labels_4])),

    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Comment", each ""),

    #"Reorder Columns" = Table.ReorderColumns(#"Added Custom1",{"Issue key", "Status", "Summary","Comment", "All_Labels","Assignee", "Due Date", "Reporter", "Priority", "Created", "Updated", "Resolved", "Custom field (Epic Link)", "Component/s", "Component/s_1", "Labels", "Labels_2", "Labels_3", "Labels_4"}, MissingField.Ignore)
in
    #"Reorder Columns"