Forum Discussion
Remove leading Zero's in Query
Hi,
I need to remove leading zeros from a character string, ideally at query level.
I have seen the solution:
http://community.powerbi.com/t5/Desktop/Remove-leading-zero-s-from-a-string/m-p/41596#M15772
This doesn't work for me because I also have values like "00A124X870", which need to be shown as "A124X870". The solution above doesn't do that. Also, it is DAX, I'd prefer to do it in the query only, although I'd make do with a DAX solution if nothing else is workable.
Help is appreciated, thanks!
You can just choose Transform - Foormat - Trim and then adjust the generated code to have zeroes trimmed from the start.
let Source = #table({"String"},List.Zip({{"000MarcelBeug","000Ol"}})), #"Trimmed Text" = Table.TransformColumns(Source,{{"String", each Text.TrimStart(_,"0")}}) in #"Trimmed Text"
11 Replies
Hi,
Just found a great Youtube video with a very easy solution and it worked for me.
I created a custom column as follows: Text.TrimStart([Column], "0")
Credit: Video by Gorilla BI, link: https://www.youtube.com/watch?v=NGE1EFOiguY
You can just choose Transform - Foormat - Trim and then adjust the generated code to have zeroes trimmed from the start.
let Source = #table({"String"},List.Zip({{"000MarcelBeug","000Ol"}})), #"Trimmed Text" = Table.TransformColumns(Source,{{"String", each Text.TrimStart(_,"0")}}) in #"Trimmed Text"The proposed solution works, but sadly cannot be sent to the server to handle in a SQL statement (query folding). I've not figured out a way to do this without braking query folding....
Hi Anonymous,
Have you tried the solution provided by MarcelBeug above? It should work in your scenario. If it works, could you accept it as solution to close this thread?
If you still have any question on this issue, feel free to post here. :smileyhappy:
Regards
