Hi, a couple of questions, is there a way to keep a leading zero if you convert to a number and have the month first so works ok; Table.AddColumn(Source, "YM ", each Number.From( Text.From( Date.Year( [Date] ) )& Text.PadStart( Text.From( Date.Month([Date]) ),2,"0") )) But if have the month first I lose the zero, and is it seems to work fine as text anyway but I get paranoid about formats as I've had problems.
Nice solution Rick, by using power query only I have the following question: I would like to have a solution that drops leading zero’s from decimal numbers (0.625 should become .625) without converting it into text as this leads to formula issues in other query steps. I could not find a native setting that supports this issue. It will be great if you or somebody else has a solution. Note: Please no scripting options and power BI solutions as the company prevents all of this due to security blockages set for end users.
Hi Vin, That's impossible. You can remove leading zeros from text values, but the decimal format is fixed. But, if you need the leading zero's not not show on the visual area of Power BI (front-end), that's an entirely different story. You can add a formatting string to your measures there. I'm suspecting that's what you need. Regards, Rick
Hi BI Gorilla, I am trying to do a vlookup onto some Sedols (which can sometimes be alphanumeric and sometimes purely numeric). The problem is the ones which are numeric sometimes have leading zeros it can be 1 zero or 2 zeros or more. So what I've done is have one column which is text for the alphanumeric sedols and one column which is Values/numbers so I can do an if error vlookup. However when I convert to values/numbers the leading 0s, drop off but I need these to be stay as they are. Is there any way to do this?
Hi Rick Do you know how can I count a specific character in a string? For example: the string "|Name|Address|PhoneNumber|" count how many "|" exist . Result = 4
@@BIGorilla One Last Doubt, please. |0150|Address|PhoneNumber|||||| In the string above, I have 5 pipes more at the end. And I have a custom column showing the number 5 (which explains that I have 5 more pipes at the end of the string) I need to delete or replace only the last Five pipes with nothing. How could I do that? Have some tips?
I did it, alone teacher. I used =Text.Range([String],0,[Custom.3]) where Custom.3 = count all characters from the string minus 5 The result is exactly the string with no 5 pipes in the end. Cheers from Brazil Help me a lot.
Sounds like an alternative Giuseppe! Great suggestion. If that's the formatting you like (200510 instead of 2005-10), that may be easiest. And who knows performance could be better too!
To learn more about functions and syntax make sure to check out: powerquery.how/
Enjoy!
Straight forward without explaining or talking too much, and still offered many solutions. Very good video! thank you
That's great, thanks for letting me know!
Now I know three ways to add leading zeros in PQ. Thank goodness I found your channel.
Perfect! Thanks Rick👍
No problem!
So helpful! ...thanks
Thank you for this excellent video !
Thank you very much, Rick! :)
This was awesome. Thank you
Thank you, Rick. That's very helpful. I prefer Method 2 🙂.
live saver, thanks
Good, thanks.
Appreciate the help!
My pleasure Adam 👏
Very good explanation! Excellent video! Thanks a lot, Mr. White!
Very welcome Marcelo. And thanks for dropping a comment :)
Thank you!
Thank you!
Would you please let me know how to put statistics of errors, validity, and emptiness in the respective columns?
Top vedio mate, keep posting. I need to watch other videos from your offerings.
Thanks for coming to the channel Shaf. More videos are scheduled. Every week a new video !
Thanks, thats what I was looking for,
I would ask you .. How can I add Leading Zeros to Numbers or texts at the same column without add custom column.
Great.
Amazing Gorilla!
Hi, a couple of questions, is there a way to keep a leading zero if you convert to a number and have the month first so works ok;
Table.AddColumn(Source, "YM ", each Number.From(
Text.From( Date.Year( [Date] ) )&
Text.PadStart(
Text.From( Date.Month([Date]) ),2,"0") ))
But if have the month first I lose the zero, and is it seems to work fine as text anyway but I get paranoid about formats as I've had problems.
Nice solution Rick, by using power query only I have the following question: I would like to have a solution that drops leading zero’s from decimal numbers (0.625 should become .625) without converting it into text as this leads to formula issues in other query steps. I could not find a native setting that supports this issue. It will be great if you or somebody else has a solution. Note: Please no scripting options and power BI solutions as the company prevents all of this due to security blockages set for end users.
Hi Vin,
That's impossible. You can remove leading zeros from text values, but the decimal format is fixed.
But, if you need the leading zero's not not show on the visual area of Power BI (front-end), that's an entirely different story. You can add a formatting string to your measures there. I'm suspecting that's what you need.
Regards,
Rick
Hi BI Gorilla, I am trying to do a vlookup onto some Sedols (which can sometimes be alphanumeric and sometimes purely numeric). The problem is the ones which are numeric sometimes have leading zeros it can be 1 zero or 2 zeros or more. So what I've done is have one column which is text for the alphanumeric sedols and one column which is Values/numbers so I can do an if error vlookup. However when I convert to values/numbers the leading 0s, drop off but I need these to be stay as they are. Is there any way to do this?
Hi Rick
Do you know how can I count a specific character in a string? For example: the string "|Name|Address|PhoneNumber|" count how many "|" exist . Result = 4
Hi Felipe. One thing you could do is.
Text.Length(
Text.Select( [ColumnName], "|"))
Best
Rick
@@BIGorilla That's it. Thank you very much.Teacher.
Cool, enjoy Power Query!
@@BIGorilla One Last Doubt, please.
|0150|Address|PhoneNumber||||||
In the string above, I have 5 pipes more at the end. And I have a custom column showing the number 5 (which explains that I have 5 more pipes at the end of the string) I need to delete or replace only the last Five pipes with nothing. How could I do that?
Have some tips?
I did it, alone teacher.
I used
=Text.Range([String],0,[Custom.3])
where
Custom.3 = count all characters from the string minus 5
The result is exactly the string with no 5 pipes in the end.
Cheers from Brazil
Help me a lot.
Weird - it's not working, I get the same column of US zip codes and can't get the leading 0, no matter how many times I try this.
What about something like: [Year]*100+[Month]
Sounds like an alternative Giuseppe! Great suggestion. If that's the formatting you like (200510 instead of 2005-10), that may be easiest.
And who knows performance could be better too!