Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

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/...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    8 years ago

    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"
MarcelBeug's avatar
MarcelBeug
Community Champion
8 years ago

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"
edhans's avatar
edhans
Community Champion
8 years ago

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....