Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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!

1 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"
Specializing in Power Query Formula Language (M)

View solution in original post

11 REPLIES 11
DiegoVillalvazo
New Member

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

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

 

Regards

Anonymous
Not applicable

Is it possible? Anyone?

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"
Specializing in Power Query Formula Language (M)

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

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

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

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



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Shelley
Continued Contributor
Continued Contributor

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

 

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"
Specializing in Power Query Formula Language (M)
Anonymous
Not applicable

Worked perfectly. Thanks for your help @MarcelBeug

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.