Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Rajan244
Helper I
Helper I

Make duplicate column values to unique based the another two columns

Sample data:

 No Container_No Events Lat Lon
1 HLXU111 Dub-Rot 53.33306 -6.24889
1 HLXU111 Rot-Kara 51.9225 4.47917
1 HLXU222 Rot-Kara 51.225 4.7917
2 HLXU111 Dub-Rot 52.84235105 -6.16893985
2 HLXU111 Rot-Kara 51.79675051 4.429912263
2 HLXU222 Rot-Kara 51.925 4.4917
3 HLXU111 Dub-Rot 50.55237595 -5.79583915
3 HLXU111 Rot-Kara 51.533278 3.430639
3 HLXU222 Rot-Kara 51.922 4.4791
4 HLXU111 Dub-Rot 50.061667 -5.715889
4 HLXU111 Rot-Kara 49.727861 -1.940028
4 HLXU222 Rot-Kara 49.72786 -1.94002
5 HLXU111 Dub-Rot 50.08522105 -5.4050098

 Desire Data:

No  Container_No    Events  Lat Lon

1.1 HLXU111 Dub-Rot 53.33306    -6.24889
2.1 HLXU111 Rot-Kara    51.9225 4.47917
3.1 HLXU222 Rot-Kara    51.225  4.7917
1.2 HLXU111 Dub-Rot 52.84235105 -6.16893985
2.2 HLXU111 Rot-Kara    51.79675051 4.429912263
3.2 HLXU222 Rot-Kara    51.925  4.4917
1.3 HLXU111 Dub-Rot 50.55237595 -5.79583915
2.3 HLXU111 Rot-Kara    51.533278   3.430639
3.3 HLXU222 Rot-Kara    51.922  4.4791
1.4 HLXU111 Dub-Rot 50.061667   -5.715889
2.4 HLXU111 Rot-Kara    49.727861   -1.940028
4.4 HLXU222 Rot-Kara    49.72786    -1.94002
1.5 HLXU111 Dub-Rot 50.08522105 -5.4050098

 

 

2 REPLIES 2
amitchandak
Super User
Super User

@Rajan244 , A new column

 

= [No] + Rankx(Filter(Table, [No] = earlier([No]) ), [lat],asc, dense) /10

Hi @amitchandak Thank you for your quick reply. Actually I forgot to mention, need to make unique based on Container_no, and events. 

Also, if its possible can you please add littlebit explaination about query, so I could change by my dataset. Thanks again.

 

Thanks @amitchandak 

Used:

 

!no = RANKX(His_data, COMBINEVALUES(",", His_data[Container_No],COMBINEVALUES("-",[From],[To])),, ASC, DENSE) + ([no]/10)

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.