Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
nullpowerbi
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
v-piga-msft
Resident Rockstar
Resident Rockstar

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
mussaenda
Super User
Super User

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
Resident Rockstar
Resident Rockstar

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors