cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 215 members 2,408 guests
Please welcome our newest community members: