Find Number of 0's at end of integer using POWER QUERY Power Bi

Question

I wanted to find out the number of 0's at end of integer. Eg for 2020 it should count 1 for 2000 it should count 3 for 3010000 it should count 4

I have no idea to do it without counting all the zeros and not just the ending ones!

someone please help :)

Answer 1

Go to Power Query Editor and add a Custom Colum with this below code-

if Number.Mod([number],100000) = 0 then 5
else if Number.Mod([number],10000) = 0 then 4
else if Number.Mod([number],1000) = 0 then 3
else if Number.Mod([number],100) = 0 then 2
else if Number.Mod([number],10) = 0 then 1
else 0

Considered highst possibility of trailing 0 is 5. You can add more if/else case following the above logic if you predict more numbers of consecutive 0 at the end.

Here is sample output using above logic-

Answer 2

Take advantage of the fact, that text "00123" converted to number will be 2 characters shorter.

= let
TxtRev = Text.Reverse(Number.ToText([num]))&"1", /*convert to text and reverse, add 1 to handle num being 0*/
TxtNoZeroes = Number.ToText(Number.FromText(TxtRev)) /*convert to number to remove starting zeroes and then back to text*/
in 
Text.Length(TxtRev)-Text.Length(TxtNoZeroes) /*compare length of original value with length without zeroes*/

This will work for any number of trailing zeroes (up to Int64 capacity of course, minus space for &"1"). Assuming that the column is of number type; if it's a text then just remove Number.ToText in TxtRev. If you have negative numbers or decimals, replace characters not being a digit after converting to text. For initial number being 0 it shows 1, but if it should show 0 just remove &"1".

Answer 3

You can do it as general string manipulation:

= Text.Length(Text.From([number])) - Text.Length(Text.TrimEnd(Text.From(number]), "0"))

We convert the column to string, strip of the zeroes, count take that away from the total length, giving you the amount of stripped zeroes.

Edit: I messed up my first answer, this one should in fact be correct