Identify number of special characters in a text string and amend

[nullpowerbi]

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.

[mussaenda]

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"

[v-piga-msft · Accepted 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