Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
My sample data is
1234-567-AB$C#DEF-123
expectation is to remove bold special character in RED and provide this result "1234-567ABCDEF123"
Solved! Go to Solution.
Check this:
Result
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZdTJbtswEAbgdwly7FizcTtKdougtV0DElqgaZCnyPt3LFKKOT3q489tSOr19QkJsMSBEJiBnt6+dKSNNBITpJ/DPDOJNAuJIVSjXC0RxdX+fiBynJ+ZOOxNGajGP82GdVZstM2oThVCpAT0jpaqC0pIfJmhBIUbYyWi73RZgCLBtQmf9LJ8Xemm1VhOxVJrz410jGtHfKBv6FKi3GRZ4SS5lDIw1q3+mCTcrl1L/P38iZdMmXEQiNtnER3upT5iq14zBtQjYnCWRtuzN8slZ/fxtjlESYYME2I+Hnt7sdF+nZulQveltH7XcSYMaZHJVlLrfZNRVPEM9UAWwZBHiHb+GeHP1K4JkRIWOKSA8wxC0652pIfIAYzjuC2ENdus1uwheEgOwn+QHSSX4Ig9qMYeCooDQg/dLOxXaiC0VzmHtaTcg1XCgzpgn2DxUHrghwOv4Lsw+4SSB58IfpbkE8klyn5pd9grFmT9pSCnc0cZ7HJ2xF3IHuxgD9N+HuP10XJ+IUZpNiMfou357R8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
Ad_Transformed = Table.AddColumn(Source, "Transformed", each
[ numbers = {"0".."9"},
alphabets = {"a".."z", "A".."Z"},
a = Text.ToList([Column1]),
b = List.Select(a, each not List.Contains(alphabets & numbers, _)), //Characters to remove/replace
c = Text.Combine(List.Transform(a, each if List.Contains(b, _) then "-" else _)), //replace other special characters with "-"
d = Text.Combine(List.RemoveItems(Splitter.SplitTextByDelimiter("-")(c), {""}), "-"), //remove extra dashes
e = Splitter.SplitTextByCharacterTransition(each true, (x)=> List.Contains(alphabets & numbers, x))(d), //split any|text
f = Text.Combine(List.Transform(e, each if List.Contains(alphabets, _, (x,y)=> Text.StartsWith(y,x)) then Text.Start(_,1) else _)),
g = Splitter.SplitTextByCharacterTransition((x)=> List.Contains(alphabets & numbers, x), each true)(f), //split text|any
h = Text.Combine(List.Transform(g, each if List.Contains(alphabets, _, (x,y)=> Text.EndsWith(y,x)) then Text.End(_,1) else _))
][h], type text)
in
Ad_Transformed
Text.Combine(
List.Transform(
Splitter.SplitTextByEachDelimiter({"-"})("1234-567-AB$C#DEF-123"),
(x) => Text.Combine(List.RemoveItems(Text.ToList(x), {"-", "#", "$"}))
),
"-"
)
@AlienSx - Hi, I have multiple lines so how i would use the above to get the result...any alternate?
Table.TransformColumns to change existing column or Table.AddColumn to create a new one is totally up to you
let
lines = #table ({"lines"}, {{"1234-567-AB$C#DEF-123"}, {"1234-567-AB$C#DEF-123"}}),
rm = Table.TransformColumns(
lines,
{"lines", (x) => Text.Combine(
List.Transform(
Splitter.SplitTextByEachDelimiter({"-"})(x),
(y) => Text.Combine(List.RemoveItems(Text.ToList(y), {"-", "#", "$"}))
),
"-"
)}
)
in
rm
Hi @aqeel_shaikh, I remember that I helped you to solve such some similar situations. You have no # or $ in you new sample which is totally different from what you've asked in 1st post. Like last time - you started creating a mess. I recommend you to provide sample with as much examples as you can and expected result based on sample data (do not provide sample as screenshot - if you don't know how to provide sample data in usable format - read note below my post). Then we can help you to create a formula which covers your issue at once.
Hi @dufoq3, The data has various senario's. yes the data i have received is a mess hence needed help to develp a query which covers all the scenario's including which i have provided in my 1st post.
1st Scenario - if between Alphabet there is special character, Special character will need to be removed completely
2nd Scenario - If between Alphabet and Numeric there is special character, Special character will need to be removed completely.
3rd Scenario- If between Numeric there is special Character, will need to replace it with "-" hypen.
above scenario's are included in the huge data im processing. let me know if i can share this to you on as an email?.
Thanks,
Check this:
Result
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ZdTJbtswEAbgdwly7FizcTtKdougtV0DElqgaZCnyPt3LFKKOT3q489tSOr19QkJsMSBEJiBnt6+dKSNNBITpJ/DPDOJNAuJIVSjXC0RxdX+fiBynJ+ZOOxNGajGP82GdVZstM2oThVCpAT0jpaqC0pIfJmhBIUbYyWi73RZgCLBtQmf9LJ8Xemm1VhOxVJrz410jGtHfKBv6FKi3GRZ4SS5lDIw1q3+mCTcrl1L/P38iZdMmXEQiNtnER3upT5iq14zBtQjYnCWRtuzN8slZ/fxtjlESYYME2I+Hnt7sdF+nZulQveltH7XcSYMaZHJVlLrfZNRVPEM9UAWwZBHiHb+GeHP1K4JkRIWOKSA8wxC0652pIfIAYzjuC2ENdus1uwheEgOwn+QHSSX4Ig9qMYeCooDQg/dLOxXaiC0VzmHtaTcg1XCgzpgn2DxUHrghwOv4Lsw+4SSB58IfpbkE8klyn5pd9grFmT9pSCnc0cZ7HJ2xF3IHuxgD9N+HuP10XJ+IUZpNiMfou357R8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
Ad_Transformed = Table.AddColumn(Source, "Transformed", each
[ numbers = {"0".."9"},
alphabets = {"a".."z", "A".."Z"},
a = Text.ToList([Column1]),
b = List.Select(a, each not List.Contains(alphabets & numbers, _)), //Characters to remove/replace
c = Text.Combine(List.Transform(a, each if List.Contains(b, _) then "-" else _)), //replace other special characters with "-"
d = Text.Combine(List.RemoveItems(Splitter.SplitTextByDelimiter("-")(c), {""}), "-"), //remove extra dashes
e = Splitter.SplitTextByCharacterTransition(each true, (x)=> List.Contains(alphabets & numbers, x))(d), //split any|text
f = Text.Combine(List.Transform(e, each if List.Contains(alphabets, _, (x,y)=> Text.StartsWith(y,x)) then Text.Start(_,1) else _)),
g = Splitter.SplitTextByCharacterTransition((x)=> List.Contains(alphabets & numbers, x), each true)(f), //split text|any
h = Text.Combine(List.Transform(g, each if List.Contains(alphabets, _, (x,y)=> Text.EndsWith(y,x)) then Text.End(_,1) else _))
][h], type text)
in
Ad_Transformed
this worked thanks