Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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 |
Solved! Go to 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 ,
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]
)
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
46 | |
31 | |
30 | |
18 | |
18 |