Number to text with leading zeroes and query folding

[Sergei-StPete]

Something like Number.ToText( 2, "00") works fine and returns "02" if only we don't keep query folding with SQL server. In such case above returns "2".  Text.PadStart( Number.ToText( 2, "00"), 2, "0") returns "02" but it breaks query folding.

Any way to return number-to-text with leading zeroes and keep query folding?

[Sergei-StPete]

That works, thank you @krishnakanth240 

[krishnakanth240]

Good to know. You are welcome @Sergei-StPete 

[krishnakanth240 · Accepted solution]

Hi @Sergei-StPete 

To preserve query folding, approach is to push the formatting logic to SQL compatible text operations instead using `Text.PadStart`.

#powerquery

RIGHT("00" & Number.ToText([Column]), 2)

#PowerqueryM

Text.End("00" & Number.ToText([Column]), 2)