Sort by Column Not Working no Duplicate Values

[Ana2022]

Hi guys,

I'm trying to sort Level II by Level II sort. I keep getting a message that there are duplicate values but there are none. See link to the file below. Can someone please help me with this? Very frustrating! Thanks!

bi test.xlsx

 

[HotChilli · Accepted solution]

Insurance has 2 different values.  It doesn't know which one to use.

I'll have a quick look for any more

[Ana2022]

You are a life-saver! I can go sleep now! How did I miss that? I really appreciate your time and kindness to help me

[HotChilli]

can you clarify what the exact error message is please?

[Ana2022]

Thanks for the quick response...here's the error message

[AllisonKennedy]

@Ana2022 I've written a blog on this exact error and why it happens. Hopefully it will help you here: https://excelwithallison.blogspot.com/2021/11/custom-sort-order-for-text-columns-in.html 

How was the Sort column generated? If you use a merge to a lookup Sort order table where the duplicates are removed first on that table, it should be more reliable. https://excelwithallison.blogspot.com/2020/10/merge-ahead-one-for-one.html 

However, it's important to note that 'Remove Duplicates' in Power Query is case sensitive, while it's not in the data model, so you may need to UPPERCASE all values before you do a remove duplicates to ensure ALL duplicates are removed. 

[Ana2022]

Thanks Allison for your reply....but I couldnt find any steps in here that would have helped me to identify the error sooner. 

[AllisonKennedy]

 , I'm working on a new blog inspired by you, basically, you should do a REMOVE DUPLICATES on the Level 2 column in Sheet 2. You can do this in Power Query with Right click. That will ensure there's no duplicate values coming through, rather than having each Level II listed multiple times.  

Now, that remove duplicates won't work if you wrote Other Income and Other income, because Power Query views these as technically two distinct values, but for purposes of sort by column and relationships they are the same.

Remove duplicates also won't work if you have extra spaces after one of the values. For you, Insurance was written as "Insurance" and "Insurance    ", so you also need to TRIM before you remove duplicates.

To resolve your problem quicker, an option could be to remove duplicates on the entire table, then Trim and Uppercase the values, and then keep duplicates in the Level II column to see where the issues are:

Paste this code into Power Query Advanced Editor using New Blank Query and click through the Applied Steps to see what I mean: 

 let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("1VnbcuM2Ev0V1FRtVVKV7FA3iny0JflS8YxdltfzkOSBoiAJMUVoQVK28vVpdDdJgJbH3pnZ1OSRfQ5ufUM3+Ouv76Zqr5YyX4qToqiMSt799K43Grz7/SeA5M7IVCWl0rkVh99QfKaNVOtczJ7STZKvpZiq1UoamaeyQNrw+6epPAGRuMxLAIsSoRFCVzIppFAuECLwUZaiHjfRDI3/+VDjRadGV4/SqD8QjHxwOj9FceyLL5IHo/JFkj9YdBz46FxmK3ErM2XXRULPJ9xpkxiVoueO+x6G5hqTx52DTQqdKinNUgoxlTLL5Vbla3E/vxB3s8kFksm2l/keDLeVeQnmTfWWFibjzjeJkUKvRFEtCrVUiTmIG6NXChUyJkOfpKmuYLTvOuMg+FYoaOMgVpKhwfcFlZVVSiMme58s99KUqrAql087mRcE9+hcpwk4iFyURQ0i1mMsfxAT0Puah/S/RFypbGkXVzlkOvamcW/kg9vEhm3ewGTOSWJEZoOaJiSnn2SJ2vrbJYef6O22ysElbdLzzxrXhF0FycHD+oGP4XoI9BiAfWe+xvvs77KwCSuFoCQxGWI2mVziJzn1LJNpCZGiyoO/MKlg9t9K7dDlP/g66Idvw/cWa/cQfU76tNOmFCdLCEDIs+1eBoGLT1UBG15UqMdmjgHp46ySGX72Ob5zaZLMn23wNZBagTMmkGGWOkdTEhpyhmAnEgKl489J7zx9D+LPS+8M6Nf6orupIR36Kll4o4b99sLB78EL32s4pD3LMS8a8q2lkoXKrHe0x0A4ZDjFXITTzK9O2uF0yiudwhrHTTaMHIo1nH82OvqHJN2oXHZDcBQw2khxB0buEmVw/Ih0c71awRY99Yz6b0O6Sw5dsKh2u0zxsNFrSLmRBr/H7be/cvRPAGz4NeYLnbPxhSjeu4mPyxsiOMp0pnDVAdflskp9FwndDeyM3sNNwSHHBcM13B8FODYsYB5kaSOkGc1VQ0MpTbIHL3NPyHUDry0eEwMuXh4QIme4lTDWpBv0r6mECTQmPKQMakqpSvLuXXKwKE1OHjOXKRTRnfxa1w1QLqRG7Z5dClwyvI4XKBN89SIUfU8QJK68sJ5DF58b4tHwrYyqKGRu5IPEFHh/fyN+FueXN0ghRfwnf8j1Y46S8QuST0lJYRiR78yTDIpEXCkKWl8pFZZ2UUC+MdvuMn2QUizgWoCCjuhcPui8om/yhPuE7/dlcqCcuLAMJyqigI50A9+WeGOg3qy2NMnICwcHCGtFPbsDoqA+rVppsxUnWaYfOWVFAZtAbne2Hj6I+iw0MnaDC2JE5zmERx0+Eddg8+tbcS/Nn6B9qMjXiHC1rbVZyCwpSon1Ms0BNbR4gDlkjlRS1KQyxt75c2n2qm0Vot6Ar0jqigRoZHp6jQjrCWZ/PmrUOg5UwwUCkIMg/1xXxAhd25nfqiDojwvxQS5V6t/sERduNSRdKDoKtZkISa4aU523N90PVinn0PzsfrREruWeExH0na3esDdfqQVUkMzvfw3fvM4ffhGfynwFylqDr4I9Ml2gKrkevIUEmlcSwvdOGqjzgAgVAxBiDsCWMK/MXh4KxHpdjByCwH4HhGS+1UKVckv4oINjDS1kXbkiZ9jhXG7xsrtOZZKLM9v6b4g46hCnFST2lYH0UGw0b6h71ImRj7b3yNdMGHcIJ8p4i0QujpL4/ywhp4SEKN1kFfeO3PFgOlOlD5QK4l54jHErbZAVkNGlOK8OSZ4gd3yMO7mcCGyfUTe96BkHxfHrYvU/iV+YpM5+MTv09W4BiWtdlO8p2+EltJRthou5U2qI4pOU64Wp1o2LIyvssuDEG12RnvvjDmozx5XWS9pK1EFdA/TjLlgH1ryEpL+WTtcbcyfVkucQzTswt3g/L6vVik7EvZTD2mgD80C6xd1yc+XgnJ8hlMWugmLJNsXiXwIq4sIOxUGDziC44HhxhIdd2MZfUzpxkhh0de2t0FXxuXW/D3qPAZ0LzGEh3xDxoKvymZsQBl2VQyKEbbBKkdHVO3XSCfi+XNn7O6vWqPNhV+fTSoMibdNVGp3J1ga8QWsJHNg1w1RBXJZQoWRUB+Gtidobdi3Spc6eGmrXDm528vQ57JrkVD8Zq9KP99cTJHStwQTE2BjbP6X1Mkg4S83lQ+z1hsUGDu+W7bHXFzqpijtCkuONr1eUsizKHSH0wroy7jBuBH/BgMUoFqUt5jCSQettJHP7R0w/BPn1myE3/rgtrJE6+DzK6EspthxV5G/cOj5jUNM5b5vOmLu2LpPenz9eICV2KW0SEJwFvNQVBj4ZbQlabAIGVw17HsvPGKGnWbgdoJ+al0n6gPV4HHra9RND6On35maGQk9drtNy08kIvg9gz2m3/e/ulj2VevEfejqk4L+Aej7jDXv6eynyx57iXgp7N/OOPSXW8eZ4wbh/hIAAF9Lzm/klfrdv1jkkrYP4BHlU6hVmFm4+vwZsX0tjbvSOy/CSX0FFJmb5WkFzYJOAKJz6LQpc6qQqSr0t7D8DcA57fTEXqaQeLs7si6oqCvB6t3iK+sc5iJGSLqrK6DppgRrbPMr9Zpcgm+wZ0fnPJE/IbQY9jThJhzvO+slSp5X1LOoNz+gui7xXzxcOEx/nWCwOmiZsDY4nnA0jXP8RKbAjpyH9l2VyCy2a1bd1VQmTcGcSx6STabIFdCnWdUqMR18kJwNDmBQJ9BZ8FH4i79TJcf0wDhdDk+ub6p8yZy8IgoYFjRm2iS/5UEEDeu0AZ1as/5jRP8qgDDrRO9UQB0eJ7HyrxpDAHB5lPm8fgDo6SvXWDY9SbB/hLTp+TiMg+nuBblPWC7jxn3AtAQJWEP4/IQn/hUEr1g2oLTF/pr+odD0BL3yRR4Fb80gbJ2vpvHODOPp2Yn5zcjIBQPFrUPf1zOPwq8GbOPgT0sh9klXNv/WAXxbIR+oUUmP9N2H4Y7Nu0QDxO+ql/yc8sJ3T738B", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Level II" = _t, #"Level II Sort" = _t]), #"Removed Duplicates" = Table.Distinct(Source), #"Trimmed Text" = Table.TransformColumns(#"Removed Duplicates",{{"Level II", Text.Trim, type text}}), #"Uppercased Text" = Table.TransformColumns(#"Trimmed Text",{{"Level II", Text.Upper, type text}}), #"Kept Duplicates" = let columnNames = {"Level II"}, addCount = Table.Group(#"Uppercased Text", columnNames, {{"Count", Table.RowCount, type number}}), selectDuplicates = Table.SelectRows(addCount, each [Count] > 1), removeCount = Table.RemoveColumns(selectDuplicates, "Count") in Table.Join(#"Uppercased Text", columnNames, removeCount, columnNames, JoinKind.Inner) in #"Kept Duplicates" 

@Ana2022