cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BananaBanana
Helper I
Helper I

Create unique ID from transaction number if no ID exists

I'm trying to migrate some manual customer data and having issues with blank Unique ID fields. Thanks in advance for any help on how to create this column

 

Some of my rows have a unique ID and some don't. I need to allocate Unique IDs in a new column called UniqueID to all the rows but it's a bit complicated.

Is it somehow possible to do the following in this order?

  • If oldID is not blank and is unique, use oldID
  • else if transactionNumber is not blank use transaction number + instance # of transaction (eg 3rd row containing 5555 will get the id 555503)
  • else prefix the column with E and assign it an incremented unique number

 

EG: 

oldIDTransactionNumberNameUniqueID
 11653Alice1165300
2411654Mark24
 11654Kate1165401
2511655Anne25
611656Peter6
2411657Terence1165701
 11658Andrew1165800
 11658Toby1165801
 11658Laura1165802
  SamanthaE000001
  SarahE000002
  KateE000003

 

1 ACCEPTED SOLUTION
amitchandak
Super User IV
Super User IV

@BananaBanana ,

 

Add and index column - https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi

 

and create new columns like


rank = "E" & format(rankx(filter(Table, isblank([TransactionNumber]) && isblank([OldID])),[Index],,asc), "00000")

 

UniqueID=

Switch =( True(),
isblank([OldID]) && isblank([TransactionNumber]) , [Rank],
isblank([OldID]) , [TransactionNumber],
[OldID]
)



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

3 REPLIES 3
amitchandak
Super User IV
Super User IV

@BananaBanana ,

 

Add and index column - https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi

 

and create new columns like


rank = "E" & format(rankx(filter(Table, isblank([TransactionNumber]) && isblank([OldID])),[Index],,asc), "00000")

 

UniqueID=

Switch =( True(),
isblank([OldID]) && isblank([TransactionNumber]) , [Rank],
isblank([OldID]) , [TransactionNumber],
[OldID]
)



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

View solution in original post

This looks really promising but it's still doubling up some of my values where the old transaction number exists across multiple records. 

 

Any idea how to add the occurence number to each transaction #? (so first transaction lableled 0400 gets labelled 040001 then 040002 etc)

OK so that woked bout I had to make the random number column go up to 10,000 before it worked properly. To be honest I still don't really understand how this column works but at least it did the job, thanks!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.