Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi There,
I have scenario where I need to remove leading zero in between the string, Could you please kindly look into and solve?
Current Text
25-EX000250B-2A
7-LMC045600A-A000045B
256-Z00256-00021A
Expected Result
25-EX250B-2A
7-LMC45600A-A45B
256-Z256-21A
Solved! Go to Solution.
@k_mathana , you might want to try
let
fn = (txt as text) =>
let
#"First 0" = Text.PositionOf(txt, "0"),
#"Last Number" = Text.PositionOfAny(txt, {"0".."9"}, Occurrence.Last),
#"Dropped Leading 0" = if #"First 0" = -1 then txt else Text.Range(txt, 0, #"First 0") & Text.From(Number.From(Text.Remove(txt, {"A".."Z", "a".."z"}))) & Text.Range(txt, #"Last Number"+1)
in
#"Dropped Leading 0",
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLVdY0wMDAwMjVw0jVyVIrViVYy1/XxdTYwMTUzMHDUdQRKAtlOYBkjUzPdKJBiM12QHkOg+lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Unprocessed = _t]),
#"Added Custom" = Table.AddColumn(Source, "Processed", each Text.Combine(List.Transform(Text.Split([Unprocessed], "-"), fn), "-"))
in
#"Added Custom"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@k_mathana - I see @CNENFRNL has already replied, but I had started working on this so thought I would post.
This looks to be a similar approach.
The function fnRemoveLeadingZeros splits the text whenever there is a transition from non-digit to "0", then trims leading zeros from the resulting substrings and joins back together.
let
fnRemoveLeadingZeros =
(string as text) =>
let
Split = Splitter.SplitTextByCharacterTransition ( each not List.Contains({"0".."9"},_), {"0"})(string),
TrimZeros = List.Transform(Split, each Text.TrimStart(_,"0") ),
Join = Text.Combine(TrimZeros)
in Join,
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLVdY0wMDAwMjVw0jVyVIrViVYy1/XxdTYwMTUzMHDUdQRKAtlOYBkjUzPdKJBiM12QHkOg+lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Input = _t]),
AddOutput = Table.AddColumn(Source, "Output", each fnRemoveLeadingZeros([Input]), type text )
in
AddOutput
Regards,
Owen
Hi @k_mathana
Sure, here is a PBIX containing the queries but split up so that the function is separate from the source data.
Actually, you could just copy the function fnRemoveLeadingZeros into Power Query in your PBIX file and use it to add a column applying this function, using Add Column > Invoke Custom Function.
Hopefully that helps apply the function in your scenario.
Regards,
Owen
@k_mathana - I see @CNENFRNL has already replied, but I had started working on this so thought I would post.
This looks to be a similar approach.
The function fnRemoveLeadingZeros splits the text whenever there is a transition from non-digit to "0", then trims leading zeros from the resulting substrings and joins back together.
let
fnRemoveLeadingZeros =
(string as text) =>
let
Split = Splitter.SplitTextByCharacterTransition ( each not List.Contains({"0".."9"},_), {"0"})(string),
TrimZeros = List.Transform(Split, each Text.TrimStart(_,"0") ),
Join = Text.Combine(TrimZeros)
in Join,
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLVdY0wMDAwMjVw0jVyVIrViVYy1/XxdTYwMTUzMHDUdQRKAtlOYBkjUzPdKJBiM12QHkOg+lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Input = _t]),
AddOutput = Table.AddColumn(Source, "Output", each fnRemoveLeadingZeros([Input]), type text )
in
AddOutput
Regards,
Owen
Hey @OwenAuger ,
love the inline function approach.
Dear @OwenAuger, Thanks a lot for solution given, When I try to apply this in Excel Table, I am getting some error, Could you please kindly guide me the how to change the source to excel table instead of Json Documen.
Hi @k_mathana
Sure, here is a PBIX containing the queries but split up so that the function is separate from the source data.
Actually, you could just copy the function fnRemoveLeadingZeros into Power Query in your PBIX file and use it to add a column applying this function, using Add Column > Invoke Custom Function.
Hopefully that helps apply the function in your scenario.
Regards,
Owen
@k_mathana , you might want to try
let
fn = (txt as text) =>
let
#"First 0" = Text.PositionOf(txt, "0"),
#"Last Number" = Text.PositionOfAny(txt, {"0".."9"}, Occurrence.Last),
#"Dropped Leading 0" = if #"First 0" = -1 then txt else Text.Range(txt, 0, #"First 0") & Text.From(Number.From(Text.Remove(txt, {"A".."Z", "a".."z"}))) & Text.Range(txt, #"Last Number"+1)
in
#"Dropped Leading 0",
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjLVdY0wMDAwMjVw0jVyVIrViVYy1/XxdTYwMTUzMHDUdQRKAtlOYBkjUzPdKJBiM12QHkOg+lgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Unprocessed = _t]),
#"Added Custom" = Table.AddColumn(Source, "Processed", each Text.Combine(List.Transform(Text.Split([Unprocessed], "-"), fn), "-"))
in
#"Added Custom"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Dear @CNENFRNL,
Thanks a lot for the solution given, I was trying to apply this in excel table but I couldn't do, How would I apply this in the excel table.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |