Remove leading Zero's in Query

[Anonymous]

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!

[DiegoVillalvazo]

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 

[v-ljerr-msft]

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. 

Regards

[Anonymous]

Is it possible? Anyone?

[MarcelBeug · Accepted solution]

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"

[banks334]

This returned for me a table with two values... one being the trimmed value and one being blank... 

I had to modify it as follows:

let
    Source = #table({"String"},{{[DRNum]}}),
    #"Trimmed Text" = Table.TransformColumns(Source,{{"String", each Text.TrimStart(_,"0")}})
in
    #"Trimmed Tex

[JaydipPatel81]

Use Columns from example if you have a column with Numbers, texts, and numbers with leading zeros.

[domi_versaix]

This worked for me perfectly and was the easiest solution. Thank you

[edhans]

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

[Shelley]

@MarcelBeug

Hi There, I'm wondering if you'd be so kind as to help me too. I am also trying to remove leading zeros, but not every record has them. Some records are alphanumeric. I have tried using your code below like this:

let
    Source = Sql.Database("CSMDataService.cloudapp.net", "CSM_Master"),
    dbo_RepairTransaction = Source{[Schema="dbo",Item="RepairTransaction"]}[Data],
    #"Trimmed Text" = Table.TransformColumns(dbo_RepairTransaction,{{"Contract_Number", each Text.TrimStart(_, "0")}}),
    #"Inserted Merged Column" = Table.AddColumn(dbo_RepairTransaction, "Key", each Text.Combine({[End_User_Key], [Contract_Number]}, "="), type text),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Merged Column",{{"Key", "%BPIDContractKey"}})
in
    #"Renamed Columns"

It appears to run without any errors or messages, but it also doesn't work.

Thanks in advance for any advice.

[MarcelBeug]

It doesn't work because you forgot to adjust the step reference in the next step.

let
    Source = Sql.Database("CSMDataService.cloudapp.net", "CSM_Master"),
    dbo_RepairTransaction = Source{[Schema="dbo",Item="RepairTransaction"]}[Data],
    #"Trimmed Text" = Table.TransformColumns(dbo_RepairTransaction,{{"Contract_Number", each Text.TrimStart(_, "0")}}),
    #"Inserted Merged Column" = Table.AddColumn(#"Trimmed Text", "Key", each Text.Combine({[End_User_Key], [Contract_Number]}, "="), type text),
    #"Renamed Columns" = Table.RenameColumns(#"Inserted Merged Column",{{"Key", "%BPIDContractKey"}})
in
    #"Renamed Columns"

[Anonymous]

Worked perfectly. Thanks for your help @MarcelBeug