PowerBI/ PowerQuery: How to get Jira Issue Field Value by Name

June 30, 2022

I share in this post my learnings and code regarding how to get a field value of a Jira issue in PowerBI/ PowerQuery.

Problem

When you don't know the solution it isn't very immediate to extract a field value of a Jira issue in PowerBI / PowerQuery M language.

Especially if you want a user-friendly robust way based on the Field name and not the Field Id.

See code in this gist

	(FieldName as text) as text =>
	let
	Source = Json.Document(Web.Contents(JiraRootUrl & "/rest/api/latest/field")),
	#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
	#"ExpandedTable" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "name"}, {"id", "name"}),
	Record = Table.First(Table.SelectRows(ExpandedTable, each ([name] = FieldName))),
	FieldId = Record.Field(Record,"id")
	in
	FieldId

To get the mapping between Field Name and Field Id I use a call to Jira Rest API api/2/field

It will return a list of Records.

I convert it to a Table and Expand the two values 'id' and 'name'.

The way to make a lookup is by using Table.First(Table.SelectedRows...)

To get the Field Value of a given Issue, I use the API call api/2/issue/IssueKey?fields=FieldId

This requires the Field Id, not the Field Name (Therefore previous step is required)

	(IssueKey as text, FieldName as text) =>
	let
	FieldId = JiraFieldName2Id(FieldName),
	Source = Json.Document(Web.Contents(JiraRootUrl & "/rest/api/2/issue/" & IssueKey & "?fields=" & FieldId)),
	fields = Source[fields],
	Field = Record.Field(fields,FieldId),
	FieldValue = if Field = null then null else Record.Field(Field,"value")
	in
	FieldValue