cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
nullpowerbi Frequent Visitor
Frequent Visitor

Identify number of special characters in a text string and amend

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Identify number of special characters in a text string and amend

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.

Capture.PNG

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Community Support Team
Community Support Team

Re: Identify number of special characters in a text string and amend

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.

Capture.PNG

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

mussaenda Senior Member
Senior Member

Re: Identify number of special characters in a text string and amend

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"

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)