The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
36 | |
24 | |
23 | |
21 | |
16 |