New API 3 cannont connect to Power BI (Error 410)

[Regional Administrator]

Hello Community, 

I was using REST API 2 to integrated JIRA SD Cloud data with Power BI and it was working well. Due to the new update of API 3, the integration not working and keep giving error 410 access blocked! Below the Queries (5 components) that I used in Power BI:

URL:
"https://NAME.atlassian.net/" meta [IsParameterQuery=true, Type="Any", IsParameterQueryRequired=true]

_____________________________________________________

GenerateByPage:
(getNextPage as function, optional tableType as type) as table =>
let
listOfPages = List.Generate(
() => getNextPage(null),
(lastPage) => lastPage <> null,
(lastPage) => getNextPage(lastPage)
),
tableOfPages = Table.FromList(listOfPages, Splitter.SplitByNothing(), {"Column1"}),
firstRow = tableOfPages{0}?,
keys = if tableType = null then Table.ColumnNames(firstRow[Column1])
else Record.FieldNames(Type.RecordFields(Type.TableRow(tableType))),
appliedType = if tableType = null then Value.Type(firstRow[Column1]) else tableType
in
if tableType = null and firstRow = null then
Table.FromRows({})
else
Value.ReplaceType(Table.ExpandTableColumn(tableOfPages, "Column1", keys), appliedType)

_____________________________________________________

FetchPages:

let

    FetchPages = (url as text, pageSize as number) => 

let 

    Source = GenerateByPage(

    (previous) =>

    let

        skipRows = if previous = null then 0 else Value.Metadata(previous)[skipRows],

        totalItems = if previous = null then 0 else Value.Metadata(previous)[total],

        table = if previous = null or Table.RowCount(previous) = pageSize then 

                    FetchPage(url, pageSize, skipRows) 

else null

    in table,

    type table [Column1])

in

    Source

in

    FetchPages

_____________________________________________________

FetchPage:

let
FetchPage = (url as text, pageSize as number, skipRows as number) as table =>
let
//Here is where you run the code that will return a single page
contents = Web.Contents(URL&"/rest/api/3/search?jql=project=ProjectName",[Query = [maxResults= Text.From(pageSize), startAt = Text.From(skipRows)]]),
json = Json.Document(contents),
Value = json[issues],
table = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
table meta [skipRows = skipRows + pageSize, total = 500]
in
FetchPage

_____________________________________________________

GetIssues:

let
Source = FetchPages("", 100),
#"Expanded Column1" = Table.ExpandRecordColumn(Source, "Column1", {"expand", "id", "self", "key", "fields"}, {"Column1.expand", "Column1.id", "Column1.self", "Column1.key", "Column1.fields"}),
in
#"Expanded Column1"

Used basic authentication email and API token as password. What should I fix exactly!

Thanks for your support.

Best

[Andy H · Accepted solution]

Hi @Regional Administrator 

I can see you're getting an error when trying to use the REST API to search for Jira issues.  This is most likely being caused by that old search endpoint has been deprecated, see https://developer.atlassian.com/changelog/#CHANGE-2046

Fortunately, there is an updated endpoint you can use that should provide the same kind of information, see https://developer.atlassian.com/cloud/jira/platform/rest/v3/api-group-issue-search/#api-rest-api-3-search-jql-get

I think you will need to change the syntax you have of:

/rest/api/3/search?jql=project=ProjectName

into:

/rest/api/3/search/jql?jql=project=ProjectName

That should then return results.  If you're getting an error using this endpoint, let me know what error/results are returned.  

Jira has a tendency to allow for anonymous calls to endpoints like search, so if your authorization failed, you might just get back 0 issues that an anonymous user would see.  But if you're calling the old deprecated endpoint the error is likely to be different and halting for all queries.

Andy

[Regional Administrator]

Thanks Andy, it working well now. 

Much appreciated :)

[Regional Administrator]

Hi @Andy H , 

the connection work well but it keep loading infinity / non stopping. Even after testing, not showing all fields only showing one field Column1.id. Could you please support this?

Thanks

[Andy H]

@Regional Administrator I am not as familiar with using Power BI to do this.  Perhaps it has something to do with switching to the v3 API as well here.  Maybe you can tweak the call to still use the v2 endpoint, but the newer one.  

Try using 

/rest/api/2/search/jql?jql=project=ProjectName

 And see if that helps.  If not, I'm not sure what else could be different here aside from the old endpoint being deprecated.