Power BI Connector for Jira - replace accountIDs with username

[tnriverfish]

relatively new user to powerBI.

We are using the Power BI Connector for JIRA and I've got a table showing open epics, issues and last comment on the issue.

JIRA has replaced all the usernames in the comments (where someone is tagged) with [~accountid:xxxxxxxxxxxxxxxxxxxxxx] that corresponds to their account ID on the users table.  It has something to do with right to be forgotten rules.

I was hoping to find and replace these IDs with the actual username so the report is functional.

Can this be done as a step in powerquery so the field is just available with the names for PowerBI?

UPDATE: solved this way... 

(idea from https://youtu.be/QL0p-SHb7Yg)

- made Reference copy of user table
- created a new column "acnt" on new Users(2) table that included the prefix, accountid and closing wrapper.

- created a new column with column acnt,username

- removed all other columns

- turned column into List

- On comments table make a new column where comment is made into an array 

- compare every comment array item to the account ID portion of list and replace with username portion of list

- reconstruct comment by putting comment array back together.

I hadn't used PowerQuery much before this - huge shout out to David Benaim (https://youtu.be/QL0p-SHb7Yg) who has got some great videos on all kinds of stuff for the guide to getting this working. @DavidBenaim 

[Daryl-Lynch-Bzy · Accepted solution]

Hi @tnriverfish - The answer is yes.  You need to use the Text.BetweenDelimiters - PowerQuery M | Microsoft Docs to find the "xxxxxxxxx" string (I am assuming it there is only one per string), and then lookup this value on the "User" table provided in your Power BI Connector for JIRA.

To find the value from the User table the following can be used:

 = Users{[USER_ACCOUNT_ID="xxxxxxxxx"]}[USER_NAME]

[tnriverfish]

I can see how this would work.  The problem would be that multiple people can be tagged so it's a little more difficult. I'll update my comments above with info on how I solved.