Forum Discussion

appeljr's avatar
appeljr
Frequent Visitor
6 years ago
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.  (...
Need Help
  • appeljr's avatar
    appeljr
    6 years ago

    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!

v-juanli-msft's avatar
v-juanli-msft
Community Support
6 years ago

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
Maggie
Community 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.
appeljr's avatar
appeljr
Frequent Visitor
6 years ago

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!