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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.