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
appeljr
Frequent Visitor

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.

 

Excel sheet link

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!

1 ACCEPTED SOLUTION

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!

Book Title Sort.png

 

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!

View solution in original post

2 REPLIES 2
v-juanli-msft
Community Support
Community Support

Hi @appeljr 

Would you like this ?

Capture18.JPG

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.

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!

Book Title Sort.png

 

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!

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.

Top Solution Authors
Top Kudoed Authors