Power BI Connector for Jira - replace accountIDs with username
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?
- 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