Obtaining changelog history in Excel using API

[Niall Smith]

Hi,

I am using this line in power query in excel to get the history of an issue via JIRA API

Table.AddColumn(#"Changed Type2", "Custom", each (Web.Contents("https://xxxxx.atlassian.net/rest/api/3/issue",[RelativePath="/" & [Key] & "changelog"])))

It returns a binary column but when I try to expand it I get the message "evaluating query" and then nothing happens.

I have tried variations such as:

& "changelog/list"]

& "changelog/list headers: {'Accept': 'application/json','Content-Type': 'application/json'}"]

These don't seem to make any difference.

I used a similar approach to get comments and that worked fine but it does not work with the change log

[Carlos Faddul]

@Niall Smith , how are you ?

For you to do this, you need to thread your code to do two things

First > run to get all issues

Second > to get changelog issue by issue (it's more costly but works)

[Niall Smith]

Hi,

Yes I have that. I should have mentioned the first part of the query is

UrlQueryPart = "?jql=project=xxxx",
JiraIDPerPage = 50,
GetJson = (Url) =>
let
RawData = Web.Contents("https://xxxxxx.atlassian.net/rest/api/3/search",[RelativePath=Url]),
Json = Json.Document(RawData)
in Json,

GetJiraIDCount = () =>
let Url = UrlQueryPart & "&maxResults=0",
Json = GetJson(Url),
Count = Json[#"total"]
in Count,

GetPage = (Index) =>
let Skip = "&startAt=" & Text.From(Index * JiraIDPerPage),
Top = "&maxResults=" & Text.From(JiraIDPerPage),
Url = UrlQueryPart & Skip & Top,
Json = GetJson(Url),
Value = Json[#"issues"]
in Value,

JiraIDCount = List.Max({ JiraIDPerPage, GetJiraIDCount() }),
PageCount = Number.RoundUp(JiraIDCount / JiraIDPerPage),
PageIndices = { 0 .. PageCount - 1 },
Pages = List.Transform(PageIndices, each GetPage(_)),
JiraID = List.Union(Pages),
Table = Table.FromList(JiraID, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(Table, "Column1", {"id", "key", "fields"}, {"Column1.id", "Column1.key", "Column1.fields"})

it is on each row that I have 

#"Added Custom2" = Table.AddColumn(#"Changed Type2", "Custom", each (Web.Contents("https://xxxx.atlassian.net/rest/api/3/issue",[RelativePath="/" & [Key] & "/changelog"]))),

I did find changelog/list needs the id's for the changes you want to get.

I have the changelog working now. Rookie error I was missing a "/" in the URL