Create unique ID from transaction number if no ID exists

[Anonymous]

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: 

oldID	TransactionNumber	Name	UniqueID
	11653	Alice	1165300
24	11654	Mark	24
	11654	Kate	1165401
25	11655	Anne	25
6	11656	Peter	6
24	11657	Terence	1165701
	11658	Andrew	1165800
	11658	Toby	1165801
	11658	Laura	1165802
		Samantha	E000001
		Sarah	E000002
		Kate	E000003

[amitchandak · Accepted solution]

@Anonymous ,

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]
)

[Anonymous]

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)

[Anonymous]

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!