Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I am trying to identify the number of special characters within a text string to highlight human error, and then amend the string to remove the human error. example below.
field type;
Money Value|unique ID|Reason|Date|Account|Name
"correct" example format;
100.00|AAAA1111|Utility Bills|01/01/2019|125124122|James
incorrect example format
100.00|AAAA1111|Utility|Bills|01/01/2019|125124122|James
The string "should" look like the example format with "|" appearing 5 times. However it is possible for a user to input the vertical bar manually and/or accidently.
Does anybody have any ideas in powerquery to locate and amend the extra vertical bar? my query already highlights the errors which i've confirmed are due to one extra vertical bar which is used as a delimiter.
Solved! Go to Solution.
Hi @nullpowerbi ,
For your requirement, you could refer to my M query below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQw0DMwqHEEAkMgqAktyczJLKmsccrMySmuMTDUByIjA0PLGkMjU0MjE0MjoxqvxNzUYqXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Money Value|unique ID|Reason|Date|Account|Name" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Money Value|unique ID|Reason|Date|Account|Name", type text}}), #"Split Column by Position" = Table.SplitColumn(#"Changed Type", "Money Value|unique ID|Reason|Date|Account|Name", Splitter.SplitTextByPositions({0, 23}, false), {"Money Value|unique ID|Reason|Date|Account|Name.1", "Money Value|unique ID|Reason|Date|Account|Name.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Money Value|unique ID|Reason|Date|Account|Name.1", type text}, {"Money Value|unique ID|Reason|Date|Account|Name.2", type text}}), #"Split Column by Positions" = Table.SplitColumn(#"Changed Type1", "Money Value|unique ID|Reason|Date|Account|Name.2", Splitter.SplitTextByPositions({1}), {"Money Value|unique ID|Reason|Date|Account|Name.2.1"}), #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Positions",{{"Money Value|unique ID|Reason|Date|Account|Name.2.1", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type2", "Custom", each [#"Money Value|unique ID|Reason|Date|Account|Name.1"]&" "&[#"Money Value|unique ID|Reason|Date|Account|Name.2.1"]) in #"Added Custom"
Here is the output.
Best Regards,
Cherry
Hi @nullpowerbi ,
you may want to try this:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQw0DMwqHEEAkMgqAktyczJLKmsccrMySmuMTDUByIjA0PLGkMjU0MjE0MjoxqvxNzUYqXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}), #"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "Column1", Splitter.SplitTextByDelimiter("|", QuoteStyle.Csv), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7"}), #"Merged Columns" = Table.CombineColumns(#"Split Column by Delimiter",{"Column1.3", "Column1.4"},Combiner.CombineTextByDelimiter(" ", QuoteStyle.None),"Merged"), #"Merged Columns1" = Table.CombineColumns(#"Merged Columns",{"Column1.1", "Column1.2", "Merged", "Column1.5", "Column1.6", "Column1.7"},Combiner.CombineTextByDelimiter("|", QuoteStyle.None),"Merged.1") in #"Merged Columns1"
Hi @nullpowerbi ,
For your requirement, you could refer to my M query below.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQw0DMwqHEEAkMgqAktyczJLKmsccrMySmuMTDUByIjA0PLGkMjU0MjE0MjoxqvxNzUYqXYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Money Value|unique ID|Reason|Date|Account|Name" = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Money Value|unique ID|Reason|Date|Account|Name", type text}}), #"Split Column by Position" = Table.SplitColumn(#"Changed Type", "Money Value|unique ID|Reason|Date|Account|Name", Splitter.SplitTextByPositions({0, 23}, false), {"Money Value|unique ID|Reason|Date|Account|Name.1", "Money Value|unique ID|Reason|Date|Account|Name.2"}), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Money Value|unique ID|Reason|Date|Account|Name.1", type text}, {"Money Value|unique ID|Reason|Date|Account|Name.2", type text}}), #"Split Column by Positions" = Table.SplitColumn(#"Changed Type1", "Money Value|unique ID|Reason|Date|Account|Name.2", Splitter.SplitTextByPositions({1}), {"Money Value|unique ID|Reason|Date|Account|Name.2.1"}), #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Positions",{{"Money Value|unique ID|Reason|Date|Account|Name.2.1", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type2", "Custom", each [#"Money Value|unique ID|Reason|Date|Account|Name.1"]&" "&[#"Money Value|unique ID|Reason|Date|Account|Name.2.1"]) in #"Added Custom"
Here is the output.
Best Regards,
Cherry
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.