Forum Discussion
Solved
Successfully grouping via key; need a second key to keep sort order
For ImkeF (who wrote a good chunk of this code already) or others:
I'm trying to maintain the sort order of the source: it ignores A, An, or The in the title but correctly alphabetizes by title.
(If there's a way to get something similar in M sort, suggest that instead, but in Excel it seems pretty rudimentary!)
To that end, I've attached the source and an example chunk of my Query. Right now, it creates a unique key that allows me to clump the book data together. When it gets to the PivotWithTextCombination that ImkeF wrote for me last month, it loses sort.
plaintext source it pulls from link
I've tried a bunch of solutions to try to generate a key and I keep getting stumped. The few examples that come close all rely on DAX (which I don't think you can combine in PQ, correct?). Otherwise, when I try to generate another key to correspond to the clumps, it just tries to number each row sequentially.
If there's some way to stick in a key row based on the null rows and then fill up the key from that, that would work.
Ultimately, I want "An address..." followed by "By His Excellency..." since they are the correctly alphabetized BOOK sort order getting pulled from the catalog.
My thanks for any assistance!
Hi Maggie, (v-juanli-msft)
At first I thought you misunderstood but going through the steps I realized it was a conceptually brilliant solution that will work across our whole project! Thank you!
That said, you made a few errors (in the line that subtracts you confused the 2 with the 3; spaces are needed with "A " "An " "The " etc. so they don't get caught in other parts of titles; also, by not trimming whitespace, the sort was unalphabetical) so I'm reluctant to mark it as the solution. If you want to mark my reply as the solution, OR, take the code below and edit yours (it's all your handiwork anyway!), then whomever stumbles on this will find a solution!
Advanced Editor:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("5Vrbchy3Ef0VlJ6SquVaUqVEx36SqHtZNktirEopesDO9OxAnAHGAIar9dfndAOY2SGpmJKpiip5W3Jx6evp04199+7OQ1V5E01lQq9co175tXrbr9WjtXpNVP9rvHv3/nFQOgzkg3E2KGdVbElVrfa6iuR512PsekT2g+6NVa/H0K7UD2pnYqu0VcZWpiYbdacqHIDPXkccxRufkcVfnXrpAg3t4sr5AvWd2uyVVj31m3QfC3Damk7X1A2t0WqjvVqv13dWd3Z33q9Yq8G7qtN9ugnbn5ugnnysqOvIVvvDC1fqSfhtNJ5W2ERBZF1B8FqueTPinz1hL1VjNBekTtwIjboix4nre2d3pDuoi/+dkrVh311oa/QaZnjbkicdZG3jus7tjN0qq3uqFRsVJlmpTkeSK3lxdI3zBLu1emOitjGku6BBiLwQmqpnEMXKJx0XtlillXEHNfZHgWDzWtV6z2e8HC20NMmDe9JiTDd69ZPzsIOzhG/cGFiSQHxBO9raQ9LpPzjTGktiavqWbf3cWWimNx3JnyXSHoaAMOr2S5Ou2Iq8DDaxFKIKrkNOxKQSy2csFtqKiig9eUQo+0ak9zC/n/2Erfubuak1PhqCOtlFDwdvuk/66HNclBMi+8jTB2cshFTRSZY/gjbeNbHkG6SJzpsKNqIQWBirTouPxHlrJOKjvXprus7ovkAE3+FpumWAaXHDf0hsuFj7cwESnQJ/GDewdrJ1VvwFRB3Iyt1b/TvFSOR/KKgSHKLEbQL5C9klh7E5dQ1ThMAS8CkDuaGjK5FyaJU/lvd1knf1BQKvbk9gwMIssze4JQciiyq+P9H1TnN8eTXofmg7QnB+N8GwHlg883HW3k63N971f5CBWcBLoHRZyhQHj/4QAn78nPTXN8p/nLMAIsaDM+wRh/XUIdxbB2dLhoTktpKp/YiUbxDXKbWRJ3w6Vvq92jpXq14nFLhAto7IPt5mfkfyiXV5U01wP07xtIWrZDGQtyIPQW4CBMF8nEHglyq6DYfPLcDA5O9b9sqXO8UpDUt/gM1NRaEcM5CugMMBEG6aBunGZCEyfieIdVjkYfcLubnBVh+ylOnA8s8JoU0HJ+krQI8dslzOazQc2IzdIrD5G3LN+nI9WUNXu5WMyXf8w5oIm7/ho7M0l2xcgOWtDi0Kf3SWNesRUJyo8G/VGmrKedr3+5VqNazsSQqlRCICLozD0JlkrqZDIKzAJrzeFo1mbsUVz44TVSLdJwO2zgcWEtvkrjFMxSxoU8vdn0MtGtPMEfty7PZfHK5ktm2qlwdA962Ea4mBGmfNwXWFnIER454oxjgR6wNxICcDRMGaHIHs3hXzJOwyHpbo2WUDRcN3rgTgKUArqgvsHpCZ5y47rjCZYnYclffBxCHIUbFNvAb0WRgl9EsUEvt7B7gyPdCOt9TabhlxpBTogwC6MK7jBTtTnc+UddYrR0/SHvq2DroRc9yaIlWsBEc5f02+IeiIr+ABlomjYaxa1ZlzUC/O7IE5P8NCzoLN6G1iZqD4zJSR1V2JZxgifHHEvtIgcH8yZNeHHOtbC1jhHLDWUW3CAEkO2o0aUmwtOwLxha/j6DczCItoUvZgiLnhkkrJX/EGEMWYbC9uEkIE6K5Tw9dAHgjkUD4HjVtlYa05FpbeEbar7v1tpe4df383GYVFY0xFpyR/vxm5XZjd/On6STDsLRHp7N9v1bc372iACp0OEyQAXKqUgjfJG+qESBzkzJ8F+SP3WYzk67SKly0rNH0J89flTQp1VO6xAUghKrllRg3vB+cTzAlazx74bUR9gLVT1d7owEQ06jkqgxGSMtYCyI94EBNaXNM1RyDr1c06/QYeWIb+bRRj9tON0W3938mAEUV4GPYMSMMIAgYnmqpNbgrjJgCnoNSGAP90WIKzdFp6qF+N3xq5LDrUIgFFnL0DmQd5VxuIW2t/Kbs+p+qIQSffPKZKxli3xu0h5SccJIC+dMntGZ/pRBASryrNjASxvyEw1AvKpoU+lR+py+OTC7ABz6YDbPWGUhUatPFs8YlC2d9G7qIYv3viaQTzJDdQar+Qe8A5EFjuFJjRaLgnSgNQuyHmwUzu1YK+AD1WA09yxPRSt5i8SxFjklVnzW7UoC0maV9nTJPdc3TS6hQjSznebcApz99zrbzH7niBBsmPVRooMK0HuPsxwTs3BH4Q4EE/1bFdmA86xTMITp1oUrfbGJ8M/hLAxoFu6WOcJHoMAD644EpfdS0tQJk5gxvZVHzYStYB3bZQRT3WUlYpJpr/lG2GrRArTdvSoIV3cKniCESN0zViMsKdopBmRAZfHvHNaGXomHP8GcIzHjGQamPLQLWEErPQzlnEnsQyFkVsYbV4HvZG+y2cUqY+8O/vdNQjRLrctCZw+QvikemTp79yAiCIGvORKwGOp7FzWzP26GIHd46zsyJwoJFZwjQ7rFmnFK9pliQ9IUN+z1w7h9WJQyZv0zhoEQiow+r+3dgybXrwYHJWYQSTh5i+ibLL7Xm8oO4f8wHHf58nN+WEsvM56foIZQwFhrvtV857KH3mdoilEi/378spx2Xgss1ncM5gvYzXGlMQN5aRzPbwqhW35GkyLjbpxjSq1GzFaTrFyLFPQMN1GrhWT1G5wZeNmaZilR5AP6WLCPgOp/QITzkODhRqLG3zZuQCzxjDmBZKyduLNV7YApjJ5ImmptSLrJ91US7WZQjErDr1gBoMGj634s/8NJFsfFA3cZC+OniV545n5BCP05RWOuN4ZX9NTZo5woCowi9da/GFwceHfjvK3cVAaalO0UXeOz/FzYJ1aTRndULqHIZT+rymC9eNAplso5gKz0+moTzv8oDXwbFAaVC+VFXCkiMGUIouPxfVw8EI4uMEyOdNLXON+sPIHfbRFCqlagG8bHpReigDk+XL0A2GNFNZLGs+yU/elHb5sDYmF3FV2HocF1j2a8bie0qvUT+PMcFnMzJtLB7XVua+FVrec5xYcbZLv8xPKM5zJ/YWuNTziPFHro0MaiVFrpn2rtSvyCL01U8ldDqeQSOzKFaTp5c4MGXw8RzYr/YpPcluAbc95QGHLjhldVJBuj4vdQwSc1rqc5YNlRAkwHNqmBQEmgcaYMhSWwUDXmA7N5dqh+TMU9I0JuB3lNyW/ky7o386f77G8ppAQ5hxxJz9je5CCrqAwASa8kw2ZRntUvIu3y6uePCHJFea/jP+X0q4eeaY2tyntPFsLPSs68drde/7B8fiYp8vc2gVIBQoSk1lamOdPWJcN9J6iRB5RCmSu642ByOlhXg/GZtauFPwo4okO4q7Drx1o/bp9CotP5gnpboMQY4K+J+6FDhTOk0JseSHiAH2VQuX15m+9UOHGJ1nYpOOwqvgwWbsYHI9ghxxgZUBF1iRpCtTS2dFqK0HkeTntsmrX9nMZx72+b838j73Eq+/+Inux+nhX4WWk/5GD1wT5QPBB/4YNBLZeZ3MyRedHcvD6g0DbMKEeqv5cTbNz0Xn+XcA/wOKXNbjmdAKgCcXe9QRS4WyhoEIZ0OrUwZhzeB9oEauL/mXFzo/dqUqkYTkS7EHKC9Tu8CjaAjJVRFat0J3OQFlTs3/SkUiD8RxTG7duPvJsYlaQ1uuu1OwXldPVzPjXZZuWR0O2kFpYSBF72K698LQblpeJigHaABOxzC9o32JiMXrKRIE3T/ysdhByObMSfi6RcLXoy8l+BpeBGujIQC7SkmXtZqfY3Gr9IevdJt/VyCtxyDOFVJxDY0Quc8k9RcEqzQ9l7bIscIEYeKshYTIA/UGH98RR4FHH1RB1gCEqug95DY2ezsprT/rnTk93OTKe7b4pUsaAGcYS0ycR/7wz+E7RyHlZdR2+DzL47kXKWW4p962UeIw/ZQhT55BDeaXLXyFzrsrYQ5y0hCLc5RfbQve+Uncjjkslj8J8ir4GKFEvo7MEhqExQ7gG8qXKQ6uMkJY6yJltNgt+Q42ySTwTAIBHTKIUfqt1SE7Ft8/6bqwA9a0+IRlKEY+00i/z83DWfEE00HPUBD2kLIZfX7O5P5bEl7iUOIkI9biF1tLyp+J9tyT2ZJ9TXP4M605L7LDvzmZuIC9/zc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Title = _t, Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Title", type text}, {"Column1", type text}}), #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Title", "Title - Copy"), #"Replaced Value" = Table.ReplaceValue(#"Duplicated Column","An ","Zn ",Replacer.ReplaceText,{"Title - Copy"}), #"Inserted Text Length" = Table.AddColumn(#"Replaced Value", "Length", each Text.Length([#"Title - Copy"])), #"Added Conditional Column" = Table.AddColumn(#"Inserted Text Length", "conditional column1", each if Text.StartsWith([#"Title - Copy"], "A ") then 1 else if Text.StartsWith([#"Title - Copy"], "Zn ") then 2 else if Text.StartsWith([#"Title - Copy"], "The ") then 3 else null), #"Added Custom" = Table.AddColumn(#"Added Conditional Column", "Custom.1", each if [conditional column1]=1 or [conditional column1]=2 then [Length]-2 else if [conditional column1]=3 then [Length]-3 else [Length]), #"Inserted Last Characters" = Table.AddColumn(#"Added Custom", "Last Characters", each Text.End([Title], [Custom.1]), type text), #"Uppercased Text" = Table.TransformColumns(#"Inserted Last Characters",{{"Last Characters", Text.Upper, type text}}), #"Trimmed Text" = Table.TransformColumns(#"Uppercased Text",{{"Last Characters", Text.Trim, type text}}), #"Sorted Rows" = Table.Sort(#"Trimmed Text",{{"Last Characters", Order.Ascending}}), #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1), #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"Title - Copy", "Length", "conditional column1", "Custom.1", "Last Characters", "Column1"}) in #"Removed Columns"Thank you so much!
2 Replies
Hi appeljr
Would you like this ?
If so, please look into my code in Advanced editor
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("5Vrbchy3Ef0VlJ6SquVaUqVEx36SqHtZNktirEopesDO9OxAnAHGAIar9dfndAOY2SGpmJKpiip5W3Jx6evp04199+7OQ1V5E01lQq9co175tXrbr9WjtXpNVP9rvHv3/nFQOgzkg3E2KGdVbElVrfa6iuR512PsekT2g+6NVa/H0K7UD2pnYqu0VcZWpiYbdacqHIDPXkccxRufkcVfnXrpAg3t4sr5AvWd2uyVVj31m3QfC3Damk7X1A2t0WqjvVqv13dWd3Z33q9Yq8G7qtN9ugnbn5ugnnysqOvIVvvDC1fqSfhtNJ5W2ERBZF1B8FqueTPinz1hL1VjNBekTtwIjboix4nre2d3pDuoi/+dkrVh311oa/QaZnjbkicdZG3jus7tjN0qq3uqFRsVJlmpTkeSK3lxdI3zBLu1emOitjGku6BBiLwQmqpnEMXKJx0XtlillXEHNfZHgWDzWtV6z2e8HC20NMmDe9JiTDd69ZPzsIOzhG/cGFiSQHxBO9raQ9LpPzjTGktiavqWbf3cWWimNx3JnyXSHoaAMOr2S5Ou2Iq8DDaxFKIKrkNOxKQSy2csFtqKiig9eUQo+0ak9zC/n/2Erfubuak1PhqCOtlFDwdvuk/66HNclBMi+8jTB2cshFTRSZY/gjbeNbHkG6SJzpsKNqIQWBirTouPxHlrJOKjvXprus7ovkAE3+FpumWAaXHDf0hsuFj7cwESnQJ/GDewdrJ1VvwFRB3Iyt1b/TvFSOR/KKgSHKLEbQL5C9klh7E5dQ1ThMAS8CkDuaGjK5FyaJU/lvd1knf1BQKvbk9gwMIssze4JQciiyq+P9H1TnN8eTXofmg7QnB+N8GwHlg883HW3k63N971f5CBWcBLoHRZyhQHj/4QAn78nPTXN8p/nLMAIsaDM+wRh/XUIdxbB2dLhoTktpKp/YiUbxDXKbWRJ3w6Vvq92jpXq14nFLhAto7IPt5mfkfyiXV5U01wP07xtIWrZDGQtyIPQW4CBMF8nEHglyq6DYfPLcDA5O9b9sqXO8UpDUt/gM1NRaEcM5CugMMBEG6aBunGZCEyfieIdVjkYfcLubnBVh+ylOnA8s8JoU0HJ+krQI8dslzOazQc2IzdIrD5G3LN+nI9WUNXu5WMyXf8w5oIm7/ho7M0l2xcgOWtDi0Kf3SWNesRUJyo8G/VGmrKedr3+5VqNazsSQqlRCICLozD0JlkrqZDIKzAJrzeFo1mbsUVz44TVSLdJwO2zgcWEtvkrjFMxSxoU8vdn0MtGtPMEfty7PZfHK5ktm2qlwdA962Ea4mBGmfNwXWFnIER454oxjgR6wNxICcDRMGaHIHs3hXzJOwyHpbo2WUDRcN3rgTgKUArqgvsHpCZ5y47rjCZYnYclffBxCHIUbFNvAb0WRgl9EsUEvt7B7gyPdCOt9TabhlxpBTogwC6MK7jBTtTnc+UddYrR0/SHvq2DroRc9yaIlWsBEc5f02+IeiIr+ABlomjYaxa1ZlzUC/O7IE5P8NCzoLN6G1iZqD4zJSR1V2JZxgifHHEvtIgcH8yZNeHHOtbC1jhHLDWUW3CAEkO2o0aUmwtOwLxha/j6DczCItoUvZgiLnhkkrJX/EGEMWYbC9uEkIE6K5Tw9dAHgjkUD4HjVtlYa05FpbeEbar7v1tpe4df383GYVFY0xFpyR/vxm5XZjd/On6STDsLRHp7N9v1bc372iACp0OEyQAXKqUgjfJG+qESBzkzJ8F+SP3WYzk67SKly0rNH0J89flTQp1VO6xAUghKrllRg3vB+cTzAlazx74bUR9gLVT1d7owEQ06jkqgxGSMtYCyI94EBNaXNM1RyDr1c06/QYeWIb+bRRj9tON0W3938mAEUV4GPYMSMMIAgYnmqpNbgrjJgCnoNSGAP90WIKzdFp6qF+N3xq5LDrUIgFFnL0DmQd5VxuIW2t/Kbs+p+qIQSffPKZKxli3xu0h5SccJIC+dMntGZ/pRBASryrNjASxvyEw1AvKpoU+lR+py+OTC7ABz6YDbPWGUhUatPFs8YlC2d9G7qIYv3viaQTzJDdQar+Qe8A5EFjuFJjRaLgnSgNQuyHmwUzu1YK+AD1WA09yxPRSt5i8SxFjklVnzW7UoC0maV9nTJPdc3TS6hQjSznebcApz99zrbzH7niBBsmPVRooMK0HuPsxwTs3BH4Q4EE/1bFdmA86xTMITp1oUrfbGJ8M/hLAxoFu6WOcJHoMAD644EpfdS0tQJk5gxvZVHzYStYB3bZQRT3WUlYpJpr/lG2GrRArTdvSoIV3cKniCESN0zViMsKdopBmRAZfHvHNaGXomHP8GcIzHjGQamPLQLWEErPQzlnEnsQyFkVsYbV4HvZG+y2cUqY+8O/vdNQjRLrctCZw+QvikemTp79yAiCIGvORKwGOp7FzWzP26GIHd46zsyJwoJFZwjQ7rFmnFK9pliQ9IUN+z1w7h9WJQyZv0zhoEQiow+r+3dgybXrwYHJWYQSTh5i+ibLL7Xm8oO4f8wHHf58nN+WEsvM56foIZQwFhrvtV857KH3mdoilEi/378spx2Xgss1ncM5gvYzXGlMQN5aRzPbwqhW35GkyLjbpxjSq1GzFaTrFyLFPQMN1GrhWT1G5wZeNmaZilR5AP6WLCPgOp/QITzkODhRqLG3zZuQCzxjDmBZKyduLNV7YApjJ5ImmptSLrJ91US7WZQjErDr1gBoMGj634s/8NJFsfFA3cZC+OniV545n5BCP05RWOuN4ZX9NTZo5woCowi9da/GFwceHfjvK3cVAaalO0UXeOz/FzYJ1aTRndULqHIZT+rymC9eNAplso5gKz0+moTzv8oDXwbFAaVC+VFXCkiMGUIouPxfVw8EI4uMEyOdNLXON+sPIHfbRFCqlagG8bHpReigDk+XL0A2GNFNZLGs+yU/elHb5sDYmF3FV2HocF1j2a8bie0qvUT+PMcFnMzJtLB7XVua+FVrec5xYcbZLv8xPKM5zJ/YWuNTziPFHro0MaiVFrpn2rtSvyCL01U8ldDqeQSOzKFaTp5c4MGXw8RzYr/YpPcluAbc95QGHLjhldVJBuj4vdQwSc1rqc5YNlRAkwHNqmBQEmgcaYMhSWwUDXmA7N5dqh+TMU9I0JuB3lNyW/ky7o386f77G8ppAQ5hxxJz9je5CCrqAwASa8kw2ZRntUvIu3y6uePCHJFea/jP+X0q4eeaY2tyntPFsLPSs68drde/7B8fiYp8vc2gVIBQoSk1lamOdPWJcN9J6iRB5RCmSu642ByOlhXg/GZtauFPwo4okO4q7Drx1o/bp9CotP5gnpboMQY4K+J+6FDhTOk0JseSHiAH2VQuX15m+9UOHGJ1nYpOOwqvgwWbsYHI9ghxxgZUBF1iRpCtTS2dFqK0HkeTntsmrX9nMZx72+b838j73Eq+/+Inux+nhX4WWk/5GD1wT5QPBB/4YNBLZeZ3MyRedHcvD6g0DbMKEeqv5cTbNz0Xn+XcA/wOKXNbjmdAKgCcXe9QRS4WyhoEIZ0OrUwZhzeB9oEauL/mXFzo/dqUqkYTkS7EHKC9Tu8CjaAjJVRFat0J3OQFlTs3/SkUiD8RxTG7duPvJsYlaQ1uuu1OwXldPVzPjXZZuWR0O2kFpYSBF72K698LQblpeJigHaABOxzC9o32JiMXrKRIE3T/ysdhByObMSfi6RcLXoy8l+BpeBGujIQC7SkmXtZqfY3Gr9IevdJt/VyCtxyDOFVJxDY0Quc8k9RcEqzQ9l7bIscIEYeKshYTIA/UGH98RR4FHH1RB1gCEqug95DY2ezsprT/rnTk93OTKe7b4pUsaAGcYS0ycR/7wz+E7RyHlZdR2+DzL47kXKWW4p962UeIw/ZQhT55BDeaXLXyFzrsrYQ5y0hCLc5RfbQve+Uncjjkslj8J8ir4GKFEvo7MEhqExQ7gG8qXKQ6uMkJY6yJltNgt+Q42ySTwTAIBHTKIUfqt1SE7Ft8/6bqwA9a0+IRlKEY+00i/z83DWfEE00HPUBD2kLIZfX7O5P5bEl7iUOIkI9biF1tLyp+J9tyT2ZJ9TXP4M605L7LDvzmZuIC9/zc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Title = _t, Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Title", type text}, {"Column1", type text}}), #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Title", "Title - Copy"), #"Replaced Value" = Table.ReplaceValue(#"Duplicated Column","An","Xn",Replacer.ReplaceText,{"Title - Copy"}), #"Inserted Text Length" = Table.AddColumn(#"Replaced Value", "Length", each Text.Length([#"Title - Copy"])), #"Added Conditional Column" = Table.AddColumn(#"Inserted Text Length", "conditional column1", each if Text.StartsWith([#"Title - Copy"], "A") then 1 else if Text.StartsWith([#"Title - Copy"], "Xn") then 2 else if Text.StartsWith([#"Title - Copy"], "The") then 3 else null), #"Added Custom" = Table.AddColumn(#"Added Conditional Column", "Custom.1", each if [conditional column1]=1 or [conditional column1]=3 then [Length]-2 else if [conditional column1]=2 then [Length]-3 else [Length]), #"Inserted Last Characters" = Table.AddColumn(#"Added Custom", "Last Characters", each Text.End([Title], [Custom.1]), type text), #"Uppercased Text" = Table.TransformColumns(#"Inserted Last Characters",{{"Last Characters", Text.Upper, type text}}), #"Sorted Rows" = Table.Sort(#"Uppercased Text",{{"Last Characters", Order.Ascending}}), #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1), #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"Title - Copy", "Length", "conditional column1", "Custom.1", "Last Characters"}) in #"Removed Columns"Best Regards
MaggieCommunity Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi Maggie, (v-juanli-msft)
At first I thought you misunderstood but going through the steps I realized it was a conceptually brilliant solution that will work across our whole project! Thank you!
That said, you made a few errors (in the line that subtracts you confused the 2 with the 3; spaces are needed with "A " "An " "The " etc. so they don't get caught in other parts of titles; also, by not trimming whitespace, the sort was unalphabetical) so I'm reluctant to mark it as the solution. If you want to mark my reply as the solution, OR, take the code below and edit yours (it's all your handiwork anyway!), then whomever stumbles on this will find a solution!
Advanced Editor:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("5Vrbchy3Ef0VlJ6SquVaUqVEx36SqHtZNktirEopesDO9OxAnAHGAIar9dfndAOY2SGpmJKpiip5W3Jx6evp04199+7OQ1V5E01lQq9co175tXrbr9WjtXpNVP9rvHv3/nFQOgzkg3E2KGdVbElVrfa6iuR512PsekT2g+6NVa/H0K7UD2pnYqu0VcZWpiYbdacqHIDPXkccxRufkcVfnXrpAg3t4sr5AvWd2uyVVj31m3QfC3Damk7X1A2t0WqjvVqv13dWd3Z33q9Yq8G7qtN9ugnbn5ugnnysqOvIVvvDC1fqSfhtNJ5W2ERBZF1B8FqueTPinz1hL1VjNBekTtwIjboix4nre2d3pDuoi/+dkrVh311oa/QaZnjbkicdZG3jus7tjN0qq3uqFRsVJlmpTkeSK3lxdI3zBLu1emOitjGku6BBiLwQmqpnEMXKJx0XtlillXEHNfZHgWDzWtV6z2e8HC20NMmDe9JiTDd69ZPzsIOzhG/cGFiSQHxBO9raQ9LpPzjTGktiavqWbf3cWWimNx3JnyXSHoaAMOr2S5Ou2Iq8DDaxFKIKrkNOxKQSy2csFtqKiig9eUQo+0ak9zC/n/2Erfubuak1PhqCOtlFDwdvuk/66HNclBMi+8jTB2cshFTRSZY/gjbeNbHkG6SJzpsKNqIQWBirTouPxHlrJOKjvXprus7ovkAE3+FpumWAaXHDf0hsuFj7cwESnQJ/GDewdrJ1VvwFRB3Iyt1b/TvFSOR/KKgSHKLEbQL5C9klh7E5dQ1ThMAS8CkDuaGjK5FyaJU/lvd1knf1BQKvbk9gwMIssze4JQciiyq+P9H1TnN8eTXofmg7QnB+N8GwHlg883HW3k63N971f5CBWcBLoHRZyhQHj/4QAn78nPTXN8p/nLMAIsaDM+wRh/XUIdxbB2dLhoTktpKp/YiUbxDXKbWRJ3w6Vvq92jpXq14nFLhAto7IPt5mfkfyiXV5U01wP07xtIWrZDGQtyIPQW4CBMF8nEHglyq6DYfPLcDA5O9b9sqXO8UpDUt/gM1NRaEcM5CugMMBEG6aBunGZCEyfieIdVjkYfcLubnBVh+ylOnA8s8JoU0HJ+krQI8dslzOazQc2IzdIrD5G3LN+nI9WUNXu5WMyXf8w5oIm7/ho7M0l2xcgOWtDi0Kf3SWNesRUJyo8G/VGmrKedr3+5VqNazsSQqlRCICLozD0JlkrqZDIKzAJrzeFo1mbsUVz44TVSLdJwO2zgcWEtvkrjFMxSxoU8vdn0MtGtPMEfty7PZfHK5ktm2qlwdA962Ea4mBGmfNwXWFnIER454oxjgR6wNxICcDRMGaHIHs3hXzJOwyHpbo2WUDRcN3rgTgKUArqgvsHpCZ5y47rjCZYnYclffBxCHIUbFNvAb0WRgl9EsUEvt7B7gyPdCOt9TabhlxpBTogwC6MK7jBTtTnc+UddYrR0/SHvq2DroRc9yaIlWsBEc5f02+IeiIr+ABlomjYaxa1ZlzUC/O7IE5P8NCzoLN6G1iZqD4zJSR1V2JZxgifHHEvtIgcH8yZNeHHOtbC1jhHLDWUW3CAEkO2o0aUmwtOwLxha/j6DczCItoUvZgiLnhkkrJX/EGEMWYbC9uEkIE6K5Tw9dAHgjkUD4HjVtlYa05FpbeEbar7v1tpe4df383GYVFY0xFpyR/vxm5XZjd/On6STDsLRHp7N9v1bc372iACp0OEyQAXKqUgjfJG+qESBzkzJ8F+SP3WYzk67SKly0rNH0J89flTQp1VO6xAUghKrllRg3vB+cTzAlazx74bUR9gLVT1d7owEQ06jkqgxGSMtYCyI94EBNaXNM1RyDr1c06/QYeWIb+bRRj9tON0W3938mAEUV4GPYMSMMIAgYnmqpNbgrjJgCnoNSGAP90WIKzdFp6qF+N3xq5LDrUIgFFnL0DmQd5VxuIW2t/Kbs+p+qIQSffPKZKxli3xu0h5SccJIC+dMntGZ/pRBASryrNjASxvyEw1AvKpoU+lR+py+OTC7ABz6YDbPWGUhUatPFs8YlC2d9G7qIYv3viaQTzJDdQar+Qe8A5EFjuFJjRaLgnSgNQuyHmwUzu1YK+AD1WA09yxPRSt5i8SxFjklVnzW7UoC0maV9nTJPdc3TS6hQjSznebcApz99zrbzH7niBBsmPVRooMK0HuPsxwTs3BH4Q4EE/1bFdmA86xTMITp1oUrfbGJ8M/hLAxoFu6WOcJHoMAD644EpfdS0tQJk5gxvZVHzYStYB3bZQRT3WUlYpJpr/lG2GrRArTdvSoIV3cKniCESN0zViMsKdopBmRAZfHvHNaGXomHP8GcIzHjGQamPLQLWEErPQzlnEnsQyFkVsYbV4HvZG+y2cUqY+8O/vdNQjRLrctCZw+QvikemTp79yAiCIGvORKwGOp7FzWzP26GIHd46zsyJwoJFZwjQ7rFmnFK9pliQ9IUN+z1w7h9WJQyZv0zhoEQiow+r+3dgybXrwYHJWYQSTh5i+ibLL7Xm8oO4f8wHHf58nN+WEsvM56foIZQwFhrvtV857KH3mdoilEi/378spx2Xgss1ncM5gvYzXGlMQN5aRzPbwqhW35GkyLjbpxjSq1GzFaTrFyLFPQMN1GrhWT1G5wZeNmaZilR5AP6WLCPgOp/QITzkODhRqLG3zZuQCzxjDmBZKyduLNV7YApjJ5ImmptSLrJ91US7WZQjErDr1gBoMGj634s/8NJFsfFA3cZC+OniV545n5BCP05RWOuN4ZX9NTZo5woCowi9da/GFwceHfjvK3cVAaalO0UXeOz/FzYJ1aTRndULqHIZT+rymC9eNAplso5gKz0+moTzv8oDXwbFAaVC+VFXCkiMGUIouPxfVw8EI4uMEyOdNLXON+sPIHfbRFCqlagG8bHpReigDk+XL0A2GNFNZLGs+yU/elHb5sDYmF3FV2HocF1j2a8bie0qvUT+PMcFnMzJtLB7XVua+FVrec5xYcbZLv8xPKM5zJ/YWuNTziPFHro0MaiVFrpn2rtSvyCL01U8ldDqeQSOzKFaTp5c4MGXw8RzYr/YpPcluAbc95QGHLjhldVJBuj4vdQwSc1rqc5YNlRAkwHNqmBQEmgcaYMhSWwUDXmA7N5dqh+TMU9I0JuB3lNyW/ky7o386f77G8ppAQ5hxxJz9je5CCrqAwASa8kw2ZRntUvIu3y6uePCHJFea/jP+X0q4eeaY2tyntPFsLPSs68drde/7B8fiYp8vc2gVIBQoSk1lamOdPWJcN9J6iRB5RCmSu642ByOlhXg/GZtauFPwo4okO4q7Drx1o/bp9CotP5gnpboMQY4K+J+6FDhTOk0JseSHiAH2VQuX15m+9UOHGJ1nYpOOwqvgwWbsYHI9ghxxgZUBF1iRpCtTS2dFqK0HkeTntsmrX9nMZx72+b838j73Eq+/+Inux+nhX4WWk/5GD1wT5QPBB/4YNBLZeZ3MyRedHcvD6g0DbMKEeqv5cTbNz0Xn+XcA/wOKXNbjmdAKgCcXe9QRS4WyhoEIZ0OrUwZhzeB9oEauL/mXFzo/dqUqkYTkS7EHKC9Tu8CjaAjJVRFat0J3OQFlTs3/SkUiD8RxTG7duPvJsYlaQ1uuu1OwXldPVzPjXZZuWR0O2kFpYSBF72K698LQblpeJigHaABOxzC9o32JiMXrKRIE3T/ysdhByObMSfi6RcLXoy8l+BpeBGujIQC7SkmXtZqfY3Gr9IevdJt/VyCtxyDOFVJxDY0Quc8k9RcEqzQ9l7bIscIEYeKshYTIA/UGH98RR4FHH1RB1gCEqug95DY2ezsprT/rnTk93OTKe7b4pUsaAGcYS0ycR/7wz+E7RyHlZdR2+DzL47kXKWW4p962UeIw/ZQhT55BDeaXLXyFzrsrYQ5y0hCLc5RfbQve+Uncjjkslj8J8ir4GKFEvo7MEhqExQ7gG8qXKQ6uMkJY6yJltNgt+Q42ySTwTAIBHTKIUfqt1SE7Ft8/6bqwA9a0+IRlKEY+00i/z83DWfEE00HPUBD2kLIZfX7O5P5bEl7iUOIkI9biF1tLyp+J9tyT2ZJ9TXP4M605L7LDvzmZuIC9/zc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Title = _t, Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Title", type text}, {"Column1", type text}}), #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Title", "Title - Copy"), #"Replaced Value" = Table.ReplaceValue(#"Duplicated Column","An ","Zn ",Replacer.ReplaceText,{"Title - Copy"}), #"Inserted Text Length" = Table.AddColumn(#"Replaced Value", "Length", each Text.Length([#"Title - Copy"])), #"Added Conditional Column" = Table.AddColumn(#"Inserted Text Length", "conditional column1", each if Text.StartsWith([#"Title - Copy"], "A ") then 1 else if Text.StartsWith([#"Title - Copy"], "Zn ") then 2 else if Text.StartsWith([#"Title - Copy"], "The ") then 3 else null), #"Added Custom" = Table.AddColumn(#"Added Conditional Column", "Custom.1", each if [conditional column1]=1 or [conditional column1]=2 then [Length]-2 else if [conditional column1]=3 then [Length]-3 else [Length]), #"Inserted Last Characters" = Table.AddColumn(#"Added Custom", "Last Characters", each Text.End([Title], [Custom.1]), type text), #"Uppercased Text" = Table.TransformColumns(#"Inserted Last Characters",{{"Last Characters", Text.Upper, type text}}), #"Trimmed Text" = Table.TransformColumns(#"Uppercased Text",{{"Last Characters", Text.Trim, type text}}), #"Sorted Rows" = Table.Sort(#"Trimmed Text",{{"Last Characters", Order.Ascending}}), #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 1, 1), #"Removed Columns" = Table.RemoveColumns(#"Added Index",{"Title - Copy", "Length", "conditional column1", "Custom.1", "Last Characters", "Column1"}) in #"Removed Columns"Thank you so much!
