Before watching this video I was trying to think about the solution but since I didn't know Text.TrimStart, I solved it by complicated following formula: Add Custom Column: [ a=Text.ToList([Values]), b=List.RemoveItems(a,{"0"}){0} , c=List.PositionOf(a,b), d=Text.Range([Values],c) ][d] But after watching this video it was mind-blowing to know Text.TrimStart formula. Thanks for sharing.
You are a lifesaver! I am pulling Data From Snowflake to populate a Material transaction report and the Materials had leading zeros that kept the data from having the ability to link to other Tables in the Dataset.... this had definitely changed the game!
Thanks for the Video! When able it would awesome to have the text needed posted in comments/description! I'll do that once I get it working here but I have to side by side and type lol But please don't let that overshadow how awesome it is for me to find this! Just what I was looking for! THANKS!
What a neat trick! The takeaway for me is to nest a function in an existing power query step. This is new. I always use the shortest number of steps to improve performance
That's right Lena. Combining these elements really allows you to keep the query clean. ☺️ Less steps doesn't always mean better performance, but sometimes it can. - Rick
Thanks so much for the solution. That's exactly what I was looking for. I have another problem and hope that you can make a video that helps cleanse/trim the delimiter of the name column in Power Query. Example: David, Smith ; egers davide/carol
Great tip, thanks ! Though the issue I am facing is that it's not applied to all of my records, some remain with leading 0. I added a step to really make sure it's of type Text but issue remains. What could be the reason for this?
@@BIGorilla drive.google.com/file/d/1Lx5Vz5Km7kFfCPhW1QR0HuuuwdOeZpeb/view?usp=sharing Hi, not sure if this link will work. There is a step in the query "Notes" on the data column Task. The Task column in the output shows e.g. both "0605" & "605"
@@claudinebeunens8721 I can open the file, but can't see any of its contents. The data comes from Azure Databricks. Some example data would help, in the current file I can't fiddle around. Perhaps you can create an example file using the 'add data' feature. Its difficult for me to tell without.
Hey undercover agent, If your value is of time format, you can have a look at docs.microsoft.com/en-us/powerquery-m/time-totext Time.ToText allows you to input a formatting string and return a different format. If it’s just text, you can use: docs.microsoft.com/en-us/powerquery-m/text-removerange Something like: if Text.Start( [Value], 1) = “0” then Text.RemoveRange( [Value], 0, 1 ) For more examples of text functions here’s my new blogpost: gorilla.bi/power-query/text-functions/#remove-text
To learn more about functions and syntax make sure to check out: powerquery.how/
Enjoy!
Even better than the remove zeros was the tip about transforming instead of creating a new column. Thank you.
Thanks Jonathan. That trick is useful for many transformations! 😁
Before watching this video I was trying to think about the solution but since I didn't know Text.TrimStart, I solved it by complicated following formula:
Add Custom Column:
[
a=Text.ToList([Values]),
b=List.RemoveItems(a,{"0"}){0} ,
c=List.PositionOf(a,b),
d=Text.Range([Values],c)
][d]
But after watching this video it was mind-blowing to know Text.TrimStart formula.
Thanks for sharing.
Simple, easy, and elegant solution. Thanks!
You are a lifesaver! I am pulling Data From Snowflake to populate a Material transaction report and the Materials had leading zeros that kept the data from having the ability to link to other Tables in the Dataset.... this had definitely changed the game!
THANK YOU, BI Gorilla !!
Thanks for sharing !! Simple and Useful !
exactly what i needed, thank you.
don't know why I had so much trouble... finally found this.. thanks :)
Enjoy!
Exactly the solution that I was looking for!!! Thank you!
I work with UPC’s all day long and this is a big issue. Thank you so much for
Exactly what Im looking for. Thanks alot
Thanks for the Video! When able it would awesome to have the text needed posted in comments/description! I'll do that once I get it working here but I have to side by side and type lol
But please don't let that overshadow how awesome it is for me to find this! Just what I was looking for! THANKS!
Thank you for making my queries cleaner :D
Unbelievable. Thanks for helping. God Bless you.
Yes, it was 💯 useful. Thanks a bunch.
Thanks man, you just save my day.
Wonderful. Thank you!
Nice. Thanks for this. PowerBI
Thanks so much, this really got me out of a pickle 👍🏽👍🏽
Very good Tricks for List.Sort. Thank you very much
Great, thanks Sir for sharing it works well👍👍
Thank you, very simple solution.
does this option still works? My formula can't find the column I need to remove zeros from...
Excellent! Exactly what I wanted to do Thank you
What a neat trick! The takeaway for me is to nest a function in an existing power query step. This is new. I always use the shortest number of steps to improve performance
That's right Lena. Combining these elements really allows you to keep the query clean. ☺️
Less steps doesn't always mean better performance, but sometimes it can.
- Rick
Legend!
Thank you!
Sensacional. Obrigado por compartilhar
Thanks so much for the solution. That's exactly what I was looking for. I have another problem and hope that you can make a video that helps cleanse/trim the delimiter of the name column in Power Query. Example: David, Smith ; egers davide/carol
thank you so much, almost had it right but didn't think of putting my 0 between quotation marks😅
Thank you good sir
Will this work for direct query?
Great tip, thanks ! Though the issue I am facing is that it's not applied to all of my records, some remain with leading 0. I added a step to really make sure it's of type Text but issue remains. What could be the reason for this?
Hi Claudine, can you share an example where that happens?
@@BIGorilla drive.google.com/file/d/1Lx5Vz5Km7kFfCPhW1QR0HuuuwdOeZpeb/view?usp=sharing Hi, not sure if this link will work. There is a step in the query "Notes" on the data column Task. The Task column in the output shows e.g. both "0605" & "605"
@@claudinebeunens8721 I can open the file, but can't see any of its contents. The data comes from Azure Databricks.
Some example data would help, in the current file I can't fiddle around. Perhaps you can create an example file using the 'add data' feature. Its difficult for me to tell without.
@@BIGorilla Issue got solved, there was another query interfering where we had to apply the same trimming, thanks for your help!
Super.. thanks
Thanks this is pretty good. Is there a way to only remove the FIRST 0. For example I'd like 00:35:58 to become 0:35:58 instead of :35:58
Hey undercover agent,
If your value is of time format, you can have a look at
docs.microsoft.com/en-us/powerquery-m/time-totext
Time.ToText allows you to input a formatting string and return a different format.
If it’s just text, you can use:
docs.microsoft.com/en-us/powerquery-m/text-removerange
Something like: if Text.Start( [Value], 1) = “0” then Text.RemoveRange( [Value], 0, 1 )
For more examples of text functions here’s my new blogpost:
gorilla.bi/power-query/text-functions/#remove-text
what if I have several preceding zeros, but I just only need to cut one zero out? please help
When column have leading two zeros then need to remove those zeros other then two zeroes let other values be same , could give me solution ?
Please can you share some example values?
thanks!
Great ! Thanks
very cool
Always a pleasure to see your support in the comments, thank you Felipe! 😁😁
@@BIGorilla your welcome. Soon I'll write a doubt for you to help me.
@@felipesignorellireis7839 look forward to it! 😁
Leading zeros were invented by the Devil to torment us BEFORE some of us even make it to hell. Not fair.
😂😂 That sure isn't fair Phoenixspin. I hope it comforts you knowing you now have the tools to battle them leading zeros!