Forum Discussion
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 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!
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"
