Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

DataFormat.Error, Can't trim text

Hi, I'm a quit beginner in Power BI and I know this issue was reported multiple times before but unfortunately, I did try multiple solutions I found here however not working! I was trying to im...
Tips and Tricks
  • parry2k's avatar
    parry2k
    6 years ago

    Anonymous here is the working query.

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nVuxkl23Df2VO6o1GQIECVJt3DgzSRxnJo3XhQql0qjwrIp0/qDkp/wlOby7+wjg4ia2NbOFnpZ4JAgcnANQP/zwjmafZfT27v27P3/3zd+Pb79/en5//PUvx4c6Ks/OlZ+en969P7795vhwEEsfXc6P1u/94+Pnr5+OD1z6ux/fn8ZGa8pvxv50/PPj18/PD4v4t6GD+FifLINEc4ypbGzR4Kefnr6k6yfX3tWu59JGncWsH3q7vIsoh+XSqJNZruV2eZuzSlxeCjW/+4cnauGZuVWI+qy8rcCho4m1otufMrPLeXquTUWsnadnWIKLiz3O0/OQtm31kuwItmYXGtFWJ6LibFHVfTr4LTndlCnNn06b1m7siMrDx/9/pfqVj+/vNLPvb7Uzy7aCkK1D4azD2Km17HMgolOfsOis0SezzCneJ3Dpw79akj3BFvKmyfC2tNQyq7fV9r4aNU7Oh4SZPKyXtEz20TPMhs7g+f7T89efvhzfffry8fPzvx62hOBg7/GuSnZPR9876oWTSIRfqwhjY8FVQ2lOd7xat6vaGMnxltuZ6RLWwsWF0NMz10dYT+Thcvvf/ng5YZvU4EmbsojzRjaoqJwpv34SCzKrLgvWwBC2m+FifAQHpseSCaRjfywZbap3ET7bLupSbrJVmaOLEBQOR5ctTfLsf62vcb3Jtptz8crRQc4W4gR44mwp7WMhF5LA7gjH1m0wNiSbWgBiEz4ptq4QU7qGTyuzl/uzlZEj7IQlqf5sFVlbvJ+QQzvhpEiSI2eS6JjT7Ox4iWwAqU1efMqzJfeWmXhYaP5wsxkYYb0px6uGyOAWo3JOLiEqlcXcX88zd+HSkGCMRCgAZu9iqlupGYrPihrokIlQHazfcasbwwclVpbDkP4acVdaDzVS9oaUstPBw/0stgZIUGv7HLasSLG5S/fAO/Et4oGXUCMtqHSHKUlMrVjHsa+xzkShqtSdfJVrYqui0k4bB0i+gsLmqkqbpvaDoCRWkC6xntSi1tdMmx/1VrMKDnSFbxxmA2UKkXW1trp3AwTKsQkFZ3bvn4aQV58uzfhHi+a4Aq6lV7aFZKw1hBLnoHtrQG43I63koIvbkmvaDg7A9MvP/wE2/fLzv2/KwGxJSeqlDJ/8vd2bQNGY0YTO0jgxset1q3mOgV3PoDcWh1kfHS9/vOwwfKKXMwhuEg5JoSNEZmDu/WQBd0xppYfLD0HBLL96/ZiCVPUJXxrR41zH+2veV/DvxE0VHH94BggFVGwYSdvY2LllCAseyS+58ebslRsgRxK0HRy3baH25VoEzDjhylpD1a1mX51zrnxi1KXqKuSBs3UcwAATVWOUrPRWlMdhS8mKKVDr0S2cMJmKKZxk3SXnkoxzRGC2TPwhbqS6y1vytduM60ZjYS85si3sCMjWC3LRk+RVyzbhvgETnIO6eluDCrOHASOMw3UhsS5bQQEvv265UHISqUE6pJJxBR40vcTAo6UB3XKEu2lQtCSIK4THCCla+uvf32TjaNadWbSBnklMTtbhSqARnwJ6lifBSVMiRUeYBrfQDpamklzwST0nzqYX6tkhjWy8nNTTlEKEwT2iIsApMgakrRVWG8om6RnId7YgyGJHpDRpuS12IBSy0+fmPtpZCmnVsS9+4VsRfCw8C+BeyF2zVa9177HqrHl21U48zilXJaQ3TBzbf6gR7dhG5h9MMVhU6q6tNiDnUCuM/3hIGRaayAAcpFOO4spNY9C1AQ3mkcAQwYJgv5GoPK7EALfkibfdFo2sEQNROjRwZRzWUTgIyqyhdLOy+5Vv368dWJJKEa7T4/5YTRdHjcu2AkaXewQxohd1pHN659JupSr+JDT7RUwOrcQhoxuw1GXN8eCjN4Jy4mv65Zqgj3wdgZHHpmoq/+pS9eydXdl3N2jLUnxFWmQBlTyrjeSKSs1eh2SV5G6l682yCbY2bq5ptnZtPxFAwTOZXQoUoJkwmeVeaiQaEwowHXpZO6FUekqNtXd6KUY2mFef5eWTt3AeW8iump5pNOw2dllVXa2n+rACp2Y9Pyi92l6kg00LVWK3H9r7US0thwpgd7/IkImi4EsdwmufrWSpuugAYiy5PdxC4KE7YZVueNGQwRIvj6ayt9WMy3EfqbhWGgFBBrvWerVWQE9zwq6xJYLcde1x6yLtKctB8FVftmlQdWnaxw5HogyTQaE4cqWKJLF7MZChS0OkV7/6Khf4ISFfJXgrkNIz2rUAsQv32OnBvTev9YnM4Vq2LXgHxChqxVZDhqQYlK+cfqUpnlnv6yyena+kVlCJ71AZeJYRSYQV1YjKuDrNLwok4+aiZC2LuYBPmy9eNq1GBor4FZTIfu2DICoDM9iNcK0tbRLKIkXNg9DSIsODEBAsu6zb1TWu3mdK8/uFYcsbQLt6jAi0HsKnhirISOFiQpxqEJog1tY9tNuxyzlZJR0gyuHmBcrT0aY9TdPVzEz30qj7vcAJjh0AXM2JJO3oQYAHCoUvHJq7eZDUPC9WL/cyt6irn+xDR9XQsazqoOZANoq//DrKOUm2ZzNkbODnRouj1F5mp6iGITdm3YHUZtYcmJPHZbaHSuGy9WEEqj2rE2ugHVtVa/Jrq03Z2CyT04ufPAN3RjS7I8lmLWu+kjb09BRlhw3lIQ5KcX3bxfVmPF0BrJdSvIpzoAim5oBtZ95BnPEMaYHEcJpgmsrVbobDkX+n9DtANGda6ThHniuKYw9kzYy9McPLGmdiHzDKEgcLIPmu/dANoMkN9wGvoGvnfKlez31MMe15CIDMUJjqLQ1/EwH43oy1KEMs+2NNJv9sxJYxBHU6YQQRsZpphSOKkTVztLEBmmuq3IX4SqGbuAasgVZcbnamTupGU+dgynMoJEpSuG4W6u9dOH7nQrb9BTNBA9dNH4aAil0fG8zmjOzMY4ikzGug99Jc/xSoJP55yQ5KyedUKBl+1NxBxZwos1kiNUP+RaNl+NAGbevtxgprpptP8kDaao3kAYlLsT1XDRHmtLAtKAmbQgAOi5PcLABkj3jOVlClKySVIaFbsfs3IHzZ45c1rpgUpGsDBPTpSu3ctzZK1jo/PTUg6eeFZkFlz2vbw0JTVm2pQft4KdRRsFxU6wYDwFRm5Tf2PVQ4A4PfyLNGuyHXuO1x7eAhIPxg0PQVSbIRw7o1jtOORQhujkV6ti2S9zPratYEz3AAXY0C1z8rpn8GOZo5CNI+KE6Zxbd3aBMsTse4yFdkQ3AziXs8JUbD1JJqaJDKy6RAi29SmXDWtC9fV3fvpaa5qjbXmYylYUtsydAMKUyxQVm6e8tjCR9wJQOO0ZhHTNO+OlhuO+aJw/q5ka9edL4+TGSJNH3X/VbT3msHRY/sAXfo3gRZfVZTDtLr6g2EozFqjwSyP83ZMgg6H13q6DHBEDPxyQUbTiPpGFfB4Ls/G2qxezZF1UiGVKAtpIa4uj7mmi3o6Waq4kjpkazRpEeyMfwTPhtHraVIhu1wZKANbrNWqiXYN1PgvgY6lwFYkeKDyHLikebHsjXavLSZBgSdd5FpwmFfGVmvDIdITFoanUKWmAlJyotXKVuq8fpQjUNLxTLJmbNjYZIVSYfB2LbUbDnco1cjZpnz9jtYbIjJqsU3DcoGJLCBXKgTaNlVr7FwFDRbQi46n4XTnPGxNCLbNUPE7EhG+uR6nvkfEICqBLlv94MvvZFGDQgU5ziQS/GVm2nRrLciqcam8OIRARwegm+8Xfzq5vmtrlbcZcwA1lqCu40xvnmp3Noa8cSuN6jfvLM1CLU541fiWe3KFOCSvbrNZwGrIx9KAvfqNZYg8kJj13StwdWySfhJa2e/jiWRwf50phvPmtqqq2O2mOjx0JKtjTbYzG9rM/A9JStNoFikEU/6OGWR2ZHBgKFZ+V4dCQR0i3iybPtZk1X/o6UNpFWx4p50avezJjMAA4hnaLIecV5IrbqxdLUd43nT9ltPZK4NtqZhJOfqN2VvPM7nyqPHh/iQB42CrpllP6ygFCxf3/SqGzEfr+80QAiNvfVp2/+jo9yRi/raL3PROdebYrc5XvXzx/8C", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Case Number" = _t, Description = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Case Number", Int64.Type}, {"Description", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Description", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Description"),
    #"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter","_x000D_","",Replacer.ReplaceText,{"Description"}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Replaced Value", "Description", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Description.1", "Description.2", "Description.3", "Description.4"}),
    #"Split Column by Delimiter3" = Table.SplitColumn(#"Split Column by Delimiter2", "Description.2", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Description.2.1", "Description.2.2"}),
    #"Split Column by Delimiter4" = Table.SplitColumn(#"Split Column by Delimiter3", "Description.3", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Description.3.1", "Description.3.2"}),
    #"Split Column by Delimiter5" = Table.SplitColumn(#"Split Column by Delimiter4", "Description.4", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Description.4.1", "Description.4.2"}),
    #"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter5", each ([Description.1] <> "#(cr)")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Description.2.1", "Description.3.1", "Description.4.1"}),
    #"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Description.4.2", Text.Trim, type text}}),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Description.4.2", Text.Clean, type text}}),
    #"Replaced Value1" = Table.ReplaceValue(#"Cleaned Text",Character.FromNumber(8237),"",Replacer.ReplaceText,{"Description.4.2"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",Character.FromNumber(8236),"",Replacer.ReplaceText,{"Description.4.2"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value2", "Custom", each Text.Length([Description.4.2])),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Description.4.2", type number}})
in
    #"Changed Type1"

     

parry2k's avatar
parry2k
Super User
6 years ago

Anonymous can you share pbix file with sample data and what steps you applied, will look into it.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thanks parry2k Please find the pbix file here with the sample data and steps

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    Anonymous there are so many queries which specific one  you are looking at?

    • parry2k's avatar
      parry2k
      Super User
      6 years ago

      Anonymous seems like some of the characters are not getting cleaned, see image below, it is giving length of the string to be different (5 and 4) whereas it suppose to be 3. Trying to find out what are the special character not getting cleaned. 

      • parry2k's avatar
        parry2k
        Super User
        6 years ago

        Anonymous here is the working query.

         

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nVuxkl23Df2VO6o1GQIECVJt3DgzSRxnJo3XhQql0qjwrIp0/qDkp/wlOby7+wjg4ia2NbOFnpZ4JAgcnANQP/zwjmafZfT27v27P3/3zd+Pb79/en5//PUvx4c6Ks/OlZ+en969P7795vhwEEsfXc6P1u/94+Pnr5+OD1z6ux/fn8ZGa8pvxv50/PPj18/PD4v4t6GD+FifLINEc4ypbGzR4Kefnr6k6yfX3tWu59JGncWsH3q7vIsoh+XSqJNZruV2eZuzSlxeCjW/+4cnauGZuVWI+qy8rcCho4m1otufMrPLeXquTUWsnadnWIKLiz3O0/OQtm31kuwItmYXGtFWJ6LibFHVfTr4LTndlCnNn06b1m7siMrDx/9/pfqVj+/vNLPvb7Uzy7aCkK1D4azD2Km17HMgolOfsOis0SezzCneJ3Dpw79akj3BFvKmyfC2tNQyq7fV9r4aNU7Oh4SZPKyXtEz20TPMhs7g+f7T89efvhzfffry8fPzvx62hOBg7/GuSnZPR9876oWTSIRfqwhjY8FVQ2lOd7xat6vaGMnxltuZ6RLWwsWF0NMz10dYT+Thcvvf/ng5YZvU4EmbsojzRjaoqJwpv34SCzKrLgvWwBC2m+FifAQHpseSCaRjfywZbap3ET7bLupSbrJVmaOLEBQOR5ctTfLsf62vcb3Jtptz8crRQc4W4gR44mwp7WMhF5LA7gjH1m0wNiSbWgBiEz4ptq4QU7qGTyuzl/uzlZEj7IQlqf5sFVlbvJ+QQzvhpEiSI2eS6JjT7Ox4iWwAqU1efMqzJfeWmXhYaP5wsxkYYb0px6uGyOAWo3JOLiEqlcXcX88zd+HSkGCMRCgAZu9iqlupGYrPihrokIlQHazfcasbwwclVpbDkP4acVdaDzVS9oaUstPBw/0stgZIUGv7HLasSLG5S/fAO/Et4oGXUCMtqHSHKUlMrVjHsa+xzkShqtSdfJVrYqui0k4bB0i+gsLmqkqbpvaDoCRWkC6xntSi1tdMmx/1VrMKDnSFbxxmA2UKkXW1trp3AwTKsQkFZ3bvn4aQV58uzfhHi+a4Aq6lV7aFZKw1hBLnoHtrQG43I63koIvbkmvaDg7A9MvP/wE2/fLzv2/KwGxJSeqlDJ/8vd2bQNGY0YTO0jgxset1q3mOgV3PoDcWh1kfHS9/vOwwfKKXMwhuEg5JoSNEZmDu/WQBd0xppYfLD0HBLL96/ZiCVPUJXxrR41zH+2veV/DvxE0VHH94BggFVGwYSdvY2LllCAseyS+58ebslRsgRxK0HRy3baH25VoEzDjhylpD1a1mX51zrnxi1KXqKuSBs3UcwAATVWOUrPRWlMdhS8mKKVDr0S2cMJmKKZxk3SXnkoxzRGC2TPwhbqS6y1vytduM60ZjYS85si3sCMjWC3LRk+RVyzbhvgETnIO6eluDCrOHASOMw3UhsS5bQQEvv265UHISqUE6pJJxBR40vcTAo6UB3XKEu2lQtCSIK4THCCla+uvf32TjaNadWbSBnklMTtbhSqARnwJ6lifBSVMiRUeYBrfQDpamklzwST0nzqYX6tkhjWy8nNTTlEKEwT2iIsApMgakrRVWG8om6RnId7YgyGJHpDRpuS12IBSy0+fmPtpZCmnVsS9+4VsRfCw8C+BeyF2zVa9177HqrHl21U48zilXJaQ3TBzbf6gR7dhG5h9MMVhU6q6tNiDnUCuM/3hIGRaayAAcpFOO4spNY9C1AQ3mkcAQwYJgv5GoPK7EALfkibfdFo2sEQNROjRwZRzWUTgIyqyhdLOy+5Vv368dWJJKEa7T4/5YTRdHjcu2AkaXewQxohd1pHN659JupSr+JDT7RUwOrcQhoxuw1GXN8eCjN4Jy4mv65Zqgj3wdgZHHpmoq/+pS9eydXdl3N2jLUnxFWmQBlTyrjeSKSs1eh2SV5G6l682yCbY2bq5ptnZtPxFAwTOZXQoUoJkwmeVeaiQaEwowHXpZO6FUekqNtXd6KUY2mFef5eWTt3AeW8iump5pNOw2dllVXa2n+rACp2Y9Pyi92l6kg00LVWK3H9r7US0thwpgd7/IkImi4EsdwmufrWSpuugAYiy5PdxC4KE7YZVueNGQwRIvj6ayt9WMy3EfqbhWGgFBBrvWerVWQE9zwq6xJYLcde1x6yLtKctB8FVftmlQdWnaxw5HogyTQaE4cqWKJLF7MZChS0OkV7/6Khf4ISFfJXgrkNIz2rUAsQv32OnBvTev9YnM4Vq2LXgHxChqxVZDhqQYlK+cfqUpnlnv6yyena+kVlCJ71AZeJYRSYQV1YjKuDrNLwok4+aiZC2LuYBPmy9eNq1GBor4FZTIfu2DICoDM9iNcK0tbRLKIkXNg9DSIsODEBAsu6zb1TWu3mdK8/uFYcsbQLt6jAi0HsKnhirISOFiQpxqEJog1tY9tNuxyzlZJR0gyuHmBcrT0aY9TdPVzEz30qj7vcAJjh0AXM2JJO3oQYAHCoUvHJq7eZDUPC9WL/cyt6irn+xDR9XQsazqoOZANoq//DrKOUm2ZzNkbODnRouj1F5mp6iGITdm3YHUZtYcmJPHZbaHSuGy9WEEqj2rE2ugHVtVa/Jrq03Z2CyT04ufPAN3RjS7I8lmLWu+kjb09BRlhw3lIQ5KcX3bxfVmPF0BrJdSvIpzoAim5oBtZ95BnPEMaYHEcJpgmsrVbobDkX+n9DtANGda6ThHniuKYw9kzYy9McPLGmdiHzDKEgcLIPmu/dANoMkN9wGvoGvnfKlez31MMe15CIDMUJjqLQ1/EwH43oy1KEMs+2NNJv9sxJYxBHU6YQQRsZpphSOKkTVztLEBmmuq3IX4SqGbuAasgVZcbnamTupGU+dgynMoJEpSuG4W6u9dOH7nQrb9BTNBA9dNH4aAil0fG8zmjOzMY4ikzGug99Jc/xSoJP55yQ5KyedUKBl+1NxBxZwos1kiNUP+RaNl+NAGbevtxgprpptP8kDaao3kAYlLsT1XDRHmtLAtKAmbQgAOi5PcLABkj3jOVlClKySVIaFbsfs3IHzZ45c1rpgUpGsDBPTpSu3ctzZK1jo/PTUg6eeFZkFlz2vbw0JTVm2pQft4KdRRsFxU6wYDwFRm5Tf2PVQ4A4PfyLNGuyHXuO1x7eAhIPxg0PQVSbIRw7o1jtOORQhujkV6ti2S9zPratYEz3AAXY0C1z8rpn8GOZo5CNI+KE6Zxbd3aBMsTse4yFdkQ3AziXs8JUbD1JJqaJDKy6RAi29SmXDWtC9fV3fvpaa5qjbXmYylYUtsydAMKUyxQVm6e8tjCR9wJQOO0ZhHTNO+OlhuO+aJw/q5ka9edL4+TGSJNH3X/VbT3msHRY/sAXfo3gRZfVZTDtLr6g2EozFqjwSyP83ZMgg6H13q6DHBEDPxyQUbTiPpGFfB4Ls/G2qxezZF1UiGVKAtpIa4uj7mmi3o6Waq4kjpkazRpEeyMfwTPhtHraVIhu1wZKANbrNWqiXYN1PgvgY6lwFYkeKDyHLikebHsjXavLSZBgSdd5FpwmFfGVmvDIdITFoanUKWmAlJyotXKVuq8fpQjUNLxTLJmbNjYZIVSYfB2LbUbDnco1cjZpnz9jtYbIjJqsU3DcoGJLCBXKgTaNlVr7FwFDRbQi46n4XTnPGxNCLbNUPE7EhG+uR6nvkfEICqBLlv94MvvZFGDQgU5ziQS/GVm2nRrLciqcam8OIRARwegm+8Xfzq5vmtrlbcZcwA1lqCu40xvnmp3Noa8cSuN6jfvLM1CLU541fiWe3KFOCSvbrNZwGrIx9KAvfqNZYg8kJj13StwdWySfhJa2e/jiWRwf50phvPmtqqq2O2mOjx0JKtjTbYzG9rM/A9JStNoFikEU/6OGWR2ZHBgKFZ+V4dCQR0i3iybPtZk1X/o6UNpFWx4p50avezJjMAA4hnaLIecV5IrbqxdLUd43nT9ltPZK4NtqZhJOfqN2VvPM7nyqPHh/iQB42CrpllP6ygFCxf3/SqGzEfr+80QAiNvfVp2/+jo9yRi/raL3PROdebYrc5XvXzx/8C", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Case Number" = _t, Description = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Case Number", Int64.Type}, {"Description", type text}}),
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(#"Changed Type", {{"Description", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Description"),
    #"Replaced Value" = Table.ReplaceValue(#"Split Column by Delimiter","_x000D_","",Replacer.ReplaceText,{"Description"}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Replaced Value", "Description", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Description.1", "Description.2", "Description.3", "Description.4"}),
    #"Split Column by Delimiter3" = Table.SplitColumn(#"Split Column by Delimiter2", "Description.2", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Description.2.1", "Description.2.2"}),
    #"Split Column by Delimiter4" = Table.SplitColumn(#"Split Column by Delimiter3", "Description.3", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Description.3.1", "Description.3.2"}),
    #"Split Column by Delimiter5" = Table.SplitColumn(#"Split Column by Delimiter4", "Description.4", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Description.4.1", "Description.4.2"}),
    #"Filtered Rows" = Table.SelectRows(#"Split Column by Delimiter5", each ([Description.1] <> "#(cr)")),
    #"Removed Columns" = Table.RemoveColumns(#"Filtered Rows",{"Description.2.1", "Description.3.1", "Description.4.1"}),
    #"Trimmed Text" = Table.TransformColumns(#"Removed Columns",{{"Description.4.2", Text.Trim, type text}}),
    #"Cleaned Text" = Table.TransformColumns(#"Trimmed Text",{{"Description.4.2", Text.Clean, type text}}),
    #"Replaced Value1" = Table.ReplaceValue(#"Cleaned Text",Character.FromNumber(8237),"",Replacer.ReplaceText,{"Description.4.2"}),
    #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1",Character.FromNumber(8236),"",Replacer.ReplaceText,{"Description.4.2"}),
    #"Added Custom" = Table.AddColumn(#"Replaced Value2", "Custom", each Text.Length([Description.4.2])),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Description.4.2", type number}})
in
    #"Changed Type1"