Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Number.ToText not working as expected

Hi all, 

trying to add leading Zeros in front of my live query in an extra column. The result must always 8 digits long including the leading zeros

I tried:

Number.ToText ( [Number], "0000000"))

not showning zeros in front of

Number.ToText ( [Number], "d8"))

not showning zeros in front of

Text.End("0" & Text.From([Number]),8)

shows only one leading zero instead of multiple to fill till 8 digits are reached

Text.PadStart (Text.From( [Number]),8,"0")

works, but is not allowed in live queries

 

Any idea how to fix this in a live query?

 

 

Need Help
  • slorin's avatar
    slorin
    2 years ago

    Hi,

     

    Text.End("00000000" & Text.From([Number]),8)

     

    Text.Repeat("0", 8-Text.Length(Text.From([Number]))) & Text.From([Number])

    Stéphane 

2 Replies

  • slorin's avatar
    slorin
    Super User
    2 years ago

    Hi,

     

    Text.End("00000000" & Text.From([Number]),8)

     

    Text.Repeat("0", 8-Text.Length(Text.From([Number]))) & Text.From([Number])

    Stéphane 

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlSK1YlWMjODUKZACGFZmgGRJUzY0gKqzBhMQ0hTCNtEB6rbyMICyARrigUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type number}}),
    NumberToText = Table.AddColumn(#"Changed Type", "NumberToText", each Number.ToText(Number.From([Column1]), "00000000")),
    TextPadStart = Table.AddColumn(NumberToText, "TextPadStart", each Text.PadStart(Text.From([Column1]), 8, "0"))
in
    TextPadStart