Forum Discussion

boa's avatar
boa
Helper I
3 years ago
Solved

Time.FromText gives sometimes an error

Hello

I'm new to PowerQuery. I have a simple question.

I want to change a column with the time in (format is text) to a time format. I used "Time.FromText"-function in PowerQuery. This is not always working. How do I have to change the text format of the time column so that it displays also the 0 in the begin.

(625 --> 0625). Is there a function for?

 

Greetings

 

Need Help
  • AntrikshSharma's avatar
    AntrikshSharma
    3 years ago

    boa You can use this:

    let
    Source = 
        Table.FromRows (
            Json.Document (
                Binary.Decompress (
                    Binary.FromText (
                        "i45WMjQ2NlaK1QEyLEwNwQwDMyNTMMPSwABMm0IoIwMo3xCkIxYA",
                        BinaryEncoding.Base64
                    ),
                    Compression.Deflate
                )
            ),
            let
                _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
            in
                type table [ Time = _t ]
        ),
    AddedCustom = 
        Table.AddColumn (
            Source,
            "Custom",
            each 
            Time.FromText ( 
                Text.PadStart ( [Time], 4, "0" ) 
            ),
            Time.Type
        )
in
    AddedCustom

3 Replies

  • AntrikshSharma's avatar
    AntrikshSharma
    Community Champion
    3 years ago

    boa You can use this:

    let
    Source = 
        Table.FromRows (
            Json.Document (
                Binary.Decompress (
                    Binary.FromText (
                        "i45WMjQ2NlaK1QEyLEwNwQwDMyNTMMPSwABMm0IoIwMo3xCkIxYA",
                        BinaryEncoding.Base64
                    ),
                    Compression.Deflate
                )
            ),
            let
                _t = ( ( type nullable text ) meta [ Serialized.Text = true ] )
            in
                type table [ Time = _t ]
        ),
    AddedCustom = 
        Table.AddColumn (
            Source,
            "Custom",
            each 
            Time.FromText ( 
                Text.PadStart ( [Time], 4, "0" ) 
            ),
            Time.Type
        )
in
    AddedCustom
    • boa's avatar
      boa
      Helper I
      3 years ago

      Thank you for your help. Your syntax is too difficult for me 🤔 but I used a function from it and it works!!

      I added a custom column with this function: Text.PadStart ( [Time], 4, "0" ) and then used the function Time.FromText on the added column. So thanks for your help!

      • AntrikshSharma's avatar
        AntrikshSharma
        Community Champion
        3 years ago

        boa My syntax generates the data and fixes the issue, you don't need to use the source step, I wanted to show you how you can apply that on our data, also, I combined the code for 2 columns in just one 😎

         

        Since my code was useful in resolving the problem, please mark it as a solution so that others can find it useful as well.