custom sort for large amount of data

[gingersnap]

I have a column with over 40k entries. 100 of them are unique. I want to custom sort them. Should I try to use a custom sort table?

[Kedar_Pande · Accepted solution]

@gingersnap 
Create a Custom Sort Order Table:

Unique Value Sort Order
Value A 1
Value B 2
Value C 3
Value D 4
Value E 5
... ...

Create a Relationship from your main table (the one with over 40k entries) to the CustomSortOrder table.

Select the column you want to sort (the one with over 40k entries).
In the Column tools tab, find the Sort by Column dropdown.
Select the Sort Order column from the CustomSortOrder table.

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[gingersnap]

Thank you! Can I duplicate the table with the 40k entries to create the new sort table and then remove the other columns I dont need?

[PijushRoy]

Hi @gingersnap 
If you want to sort in Power Query, yes you can sort. 
More details https://www.youtube.com/watch?v=uZLmWz60_do

https://www.youtube.com/watch?v=7PVjvY4a_V8

https://community.fabric.microsoft.com/t5/Desktop/Power-Query-Custom-Sort/m-p/523096