Forum Discussion

paolomint's avatar
paolomint
Helper III
5 years ago
Solved

Convert decimal in hour

Dear all,

In my table there is a column with hour in decimal format:

example 6,57 = 6:57 AM

I tried to convert it in hours but it didn't work.

How can I do that?  May I create a new column with these values in hour format?

Thanks in advance

Paolo

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi paolomint ,

     

    Sorry for my late reply,

    let
    Source = #table(type table[Data = text],List.Zip({{"9,00","7,30","8,59","12,45","17,20"}})),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Data", Splitter.SplitTextByAnyDelimiter({","}, QuoteStyle.Csv), {"Hour", "Minute"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Hour", Int64.Type}, {"Minute", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Time.From(#datetime(1899, 12, 30, [Hour], [Minute], 12)))
in
    #"Added Custom"

     

    If it is in decimal format, there is no comma in Power BI, so I think it may be in text format.

     

    The original data is text similar to 9,00. I first divide the hour and minute into two columns based on the comma, then convert the hour and minute columns into whole number types, and then convert them to time.

     

    You can check more details from here.

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    try this

     

    Table.AddColumn(Origine, "hour", each DateTime.Time(#datetime(1,1,1,Number.From(Text.Split([Colonna1],","){0}),Number.From(Text.Split([Colonna1],","){1}),0)))
  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi paolomint ,

     

    Try this calculated column

    Time = TIME(LEFT([Number],1),RIGHT([Number],2),0)

     

     

     

    Best Regards,

    Stephen Tao

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi paolomint ,

    Could you tell me if your problem has been solved?
    If it is, kindly Accept it as the solution. More people will benefit from it.
    Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.


    Best Regards,
    Stephen Tao

    • paolomint's avatar
      paolomint
      Helper III
      5 years ago

      Dear Stephen, please apologize, 

      I tried your solution but it doesn't work anytime, just sometime and I can't understand why.

      I can't dowload any picture, anyhow here you are some results:

       

      9,00 = 09:09:00 (wrong)

      7,30 = 07:00:00 (wrong)

      8,59 = 08:59:00 (correct)

      7,45 = 07:45:00 (correct).

       

      Mover, I didnt say before, the format is 24h, so often I have data like:

      12,45

      17,20

       

      Please note I was asking for a Power query solution, anyhow also a power Bi solution could be usefull.

      thank for your patience

      Paolo

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi paolomint ,

         

        Sorry for my late reply,

        let
    Source = #table(type table[Data = text],List.Zip({{"9,00","7,30","8,59","12,45","17,20"}})),
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "Data", Splitter.SplitTextByAnyDelimiter({","}, QuoteStyle.Csv), {"Hour", "Minute"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Hour", Int64.Type}, {"Minute", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Time.From(#datetime(1899, 12, 30, [Hour], [Minute], 12)))
in
    #"Added Custom"

         

        If it is in decimal format, there is no comma in Power BI, so I think it may be in text format.

         

        The original data is text similar to 9,00. I first divide the hour and minute into two columns based on the comma, then convert the hour and minute columns into whole number types, and then convert them to time.

         

        You can check more details from here.

         

         

        Best Regards,

        Stephen Tao

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.