Convert text to number

[sd77000]

Hello all,

I am learning how to use Power Query so please be patient. I am creating a query, there is one column that I am unable to convert to a number. The numbers in the column are in text and represent time (hh:mm). When I change the data type to number, decimal or time, receive error in the column. I am unable to find a formula in power query that will convert it. Any help/guidance would be greatly appreciated.

Thanks in advance,
Steve

 

[MarcelBeug]

If you change the column type of a table (to Time in your case), then textual time representations of time are converted to Time values.

You can try and paste the following code in the Advanced Editor:

let
    Source = {"01:02","17:58"},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type time}})
in
    #"Changed Type"

So what are you doing exactly and what error do you get?
Does your data only consists of time representations and nothing else?

 

[sd77000]

Let me better explain, when I convert the column to Time, was before as 1:00, after change, 1:00:00 AM. If the hour is greater than 24 hrs, after the change, Error is displayed where the time was greater than 24 hrs. Sample is shown, with before and after.

Tot Hrs	Tot Hrs
1:00	1:00:00 AM
0:15	12:15:00 AM
60:00	Error

<tbody>
</tbody>

Thanks,
Steve

 

[MarcelBeug]

So your data represents durations, not times.

My suggestion is to copy the column, split it in hours and minutes, add a column with the hours and minutes converted to durations which are then converted to decimal values.

let
    Source = {"01:02","17:58", "60:00"},
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Tot Hrs"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Tot Hrs", type text}}),
    #"Duplicated Column" = Table.DuplicateColumn(#"Changed Type", "Tot Hrs", "Tot Hrs - Copy"),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Duplicated Column", "Tot Hrs - Copy", Splitter.SplitTextByDelimiter(":", QuoteStyle.Csv), {"Hours", "Minutes"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Hours", Int64.Type}, {"Minutes", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Duration", each Number.From(#duration(0,[Hours],[Minutes],0)), type number),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Hours", "Minutes"})
in
    #"Removed Columns"

 

[sd77000]

Thank you