DataFormat.Error, Can't trim text

[Anonymous]

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 import data from excel file where there is a text column contains multiple values separated by Comma, Colon and line feed, then I used to split that text into rows/columns

The issue is there are values where is need to display as numbers and it works for almost all cases except some, where there is still some spaces were not trimmed at the beginning of the text so it gives an error,

I did try multiple solutions: 

1- Trim & clean
2- Clean Whitespace Function:       
                               (text as text, optional char_to_trim as text) =>
                                        let
                                        char = if char_to_trim = null then " " else char_to_trim,
                                        split = Text.Split(text, char),
                                        removeblanks = List.Select(split, each _ <> ""),
                                        result=Text.Combine(removeblanks, char)
                                        in
3- Splitter.SplitTextByDelimiter (Space)
4- Splitter.SplitTextByWhitespace

Not sure if I did explain my issue well, so HERE you can find the sample file with the query,

Thanks in advance!

[parry2k]

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

[Anonymous]

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

[parry2k]

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

[parry2k]

@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 · Accepted solution]

@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"

[Anonymous]

I got it and it's working perfectly! thanks a lot @parry2k