cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tnriverfish
Regular Visitor

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.

 

tnriverfish_0-1660665798463.png

tnriverfish_0-1660666218351.png

 

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 

1 ACCEPTED SOLUTION
Daryl-Lynch-Bzy
Memorable Member
Memorable Member

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]

View solution in original post

2 REPLIES 2
Daryl-Lynch-Bzy
Memorable Member
Memorable Member

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]

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.

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 25

Ted's Dev Camp - August 25, 2022

Watch Session 25 of Ted's Dev Camp.

Top Kudoed Authors