Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
In the case study, i want to remove special character which is "-" hyphen if it is coming between Alphanumeric character.
if it is between numeric should not be removed.
can you please help me with the query
Sample:
1) XYZ-1-ACD - expectation is XYZ1ACD
2) 123-456-XYZ - expectation is 123-456XYZ.
Solved! Go to Solution.
Hi @aqeel_shaikh, check this.
Result
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WioiM0jXUdXR2UYrViVYyNDLWNTE10wWKKsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
Ad_Cleaned = Table.AddColumn(Source, "Cleaned", each
[ lst = {"0".."9"},
a1 = Splitter.SplitTextByCharacterTransition(each true, (x)=> not List.Contains(lst, x))([Column1]),
a2 = Text.Combine(List.RemoveItems(a1, {"-"})),
b1 = Splitter.SplitTextByCharacterTransition((x)=> not List.Contains(lst, x), each true)(a2),
b2 = Text.Combine(List.RemoveItems(b1, {"-"}))
][b2], type text)
in
Ad_Cleaned
Try this:
let
Source = Excel.Workbook(File.Contents("C:\Users\s441801\OneDrive - Emirates Group\General - AQEEL\Power query\POWER Q TEST SAMPLE.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
Ad_Cleaned = Table.AddColumn(Sheet1_Sheet, "Cleaned", each
[ lst = {"0".."9"},
a1 = Splitter.SplitTextByCharacterTransition(each true, (x)=> not List.Contains(lst, x))([Column1]),
a2 = Text.Combine(List.RemoveItems(a1, {"-"})),
b1 = Splitter.SplitTextByCharacterTransition((x)=> not List.Contains(lst, x), each true)(a2),
b2 = Text.Combine(List.RemoveItems(b1, {"-"}))
][b2], type text)
in
Ad_Cleaned
Hi @aqeel_shaikh, check this.
Result
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WioiM0jXUdXR2UYrViVYyNDLWNTE10wWKKsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
Ad_Cleaned = Table.AddColumn(Source, "Cleaned", each
[ lst = {"0".."9"},
a1 = Splitter.SplitTextByCharacterTransition(each true, (x)=> not List.Contains(lst, x))([Column1]),
a2 = Text.Combine(List.RemoveItems(a1, {"-"})),
b1 = Splitter.SplitTextByCharacterTransition((x)=> not List.Contains(lst, x), each true)(a2),
b2 = Text.Combine(List.RemoveItems(b1, {"-"}))
][b2], type text)
in
Ad_Cleaned
getting below error
let
Source = Excel.Workbook(File.Contents("C:\Users\General \Power query\POWER Q TEST SAMPLE.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(Sheet1_Sheet,{{"Column1", type text}}),
Ad_Cleaned = Table.AddColumn(Source, "Cleaned", each
[ lst = {"0".."9"},
a1 = Splitter.SplitTextByCharacterTransition(each true, (x)=> not List.Contains(lst, x))([Column1]),
a2 = Text.Combine(List.RemoveItems(a1, {"-"})),
b1 = Splitter.SplitTextByCharacterTransition((x)=> not List.Contains(lst, x), each true)(a2),
b2 = Text.Combine(List.RemoveItems(b1, {"-"}))
][b2], type text
in
#"Changed Type"
Try this:
let
Source = Excel.Workbook(File.Contents("C:\Users\s441801\OneDrive - Emirates Group\General - AQEEL\Power query\POWER Q TEST SAMPLE.xlsx"), null, true),
Sheet1_Sheet = Source{[Item="Sheet1",Kind="Sheet"]}[Data],
Ad_Cleaned = Table.AddColumn(Sheet1_Sheet, "Cleaned", each
[ lst = {"0".."9"},
a1 = Splitter.SplitTextByCharacterTransition(each true, (x)=> not List.Contains(lst, x))([Column1]),
a2 = Text.Combine(List.RemoveItems(a1, {"-"})),
b1 = Splitter.SplitTextByCharacterTransition((x)=> not List.Contains(lst, x), each true)(a2),
b2 = Text.Combine(List.RemoveItems(b1, {"-"}))
][b2], type text)
in
Ad_Cleaned
@dufoq3
If i want to add one more query,
e.g: between numeric there is a special character but to be replaced with "-" hypen.
how can i add that to the same above query?
Only between numeric replace Special character but with "-" hypen.
also remove special character between Alhpabets.
can this be combined with the query provided above
will this help me to remove special character between Alhpabets. if there is a different query can you please share?
@dufoq3 ,
My sample data is
1234-567-AB$C#DEF-123
expectation is to remove bold special character in RED and provide this result "1234-567-ABCDEF123"
RemoveHyphens =
try this and let me know if this works:
VAR inputText = [YourColumn]
VAR length = LEN(inputText)
VAR result =
GENERATESERIES(1, length, 1)
RETURN
VAR currentIndex = VALUE([Value])
VAR currentChar = MID(inputText, currentIndex, 1)
VAR prevChar = MID(inputText, currentIndex - 1, 1)
VAR nextChar = MID(inputText, currentIndex + 1, 1)
RETURN
IF(
currentChar = "-" &&
(ISALPHA(prevChar) && ISALPHA(nextChar) || ISALPHA(prevChar) && ISNUMERIC(nextChar) || ISNUMERIC(prevChar) && ISALPHA(nextChar)),
"",
currentChar
)
)
VAR modifiedText = CONCATENATEX(result, result, "")
RETURN modifiedText
My blog:
https://analyticpulse.blogspot.com/2024/03/superstore-sales-2022-vs-2023-year-on.html
https://analyticpulse.blogspot.com/
https://analyticpulse.blogspot.com/2024/04/case-study-powerbi-dashboard-developer.html
See my Pins :
https://pin.it/5aoqgZUft
https://in.pinterest.com/AnalyticPulse/
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
69 | |
61 | |
18 | |
16 | |
13 |