Power Query Data types and excel time format issue

[indrajeet_rajput]

So, I have a column for time duration in my data from different excel files and tables which gets consolidated in power query...

But the data in the time duration column from these different files/tables is not the same, it's in these below 3 example formats:

00:24:02
00h 24m 02s
24m 02s

01h 24m 02s
01:24:02
01h 24m 02s


The first three examples represent 24 minutes and 2 seconds
The last three examples represent 1 hour, 24 minutes and 2 seconds.

The data is always in these three formats representing the time duration. And I have to do some conditional calculations and add measures using this time duration column but I can't do that unless I am able to convert it into a standard/default time format for my calculations.

Is there a way we can convert all three types in to one single time format

1)Either in excel before the data gets loaded in power query for consolidation?

2)Or in power query after its loaded for consolidation?

 

[GraH]

There might be different and better ways, but this one is "easy"

let
    Source = Excel.CurrentWorkbook(){[Name="Times"]}[Content],
    Fn = Table.AddColumn(Source, "Time", each [
      IsText = Value.Type([Timvalues]) = Text.Type
    , TimeIn = if IsText then null else Time.From([Timvalues])
    , ValIn = Text.Split([Timvalues], " ")
    , Hr = if IsText then Text.Remove(List.Select(ValIn, each Text.Contains(_, "h")){0}, "h") else Time.Hour(TimeIn)
    , Min = if IsText then Text.Remove(List.Select(ValIn, each Text.Contains(_, "m")){0}, "m") else Time.Minute(TimeIn)
    , Sec = if IsText then Text.Remove(List.Select(ValIn, each Text.Contains(_, "s")){0}, "s") else Time.Second(TimeIn)
    , ValOut = #time(try Number.From(Hr) otherwise 0, try Number.From(Min)otherwise 0, try Number.From(Sec) otherwise 0)
][ValOut], type time)
in
    Fn

Book1
	A	B
1	Timvalues	Time
2	0,016689815	0:24:02
3	00h 24m 02s	0:24:02
4	24m 02s	0:24:02
5	01h 24m 02s	1:24:02
6	0,058356481	1:24:02
7	01h 24m 02s	1:24:02
8	0,000243056	0:00:21
9	0h 00m 21s	0:00:21
10	21s	0:00:21
11	0,583958333	14:00:54
12	14h 00m 54s	14:00:54
13	14h 54s	14:00:54
Times

 

[alex78]

Assuming Table Name is "Table1" and Column Name is "Column1", try:

let
    fx = (x) => let a = List.Reverse (Splitter.SplitTextByAnyDelimiter({"d","h", "m","s"})(Text.Replace(x, "s", "")))
                in let b = List.Accumulate({0..3}, {}, (s,c)=> s & { try  Number.FromText(a{c}) otherwise 0 })
                in #duration(b{3}, b{2},b{1},b{0}),
    #"Duration" = Table.TransformColumnTypes(Table.TransformColumns(Excel.CurrentWorkbook(){[Name="Table1"]}[Content], {"Column1",
                  each try Duration.From(_) otherwise fx(_)}),{{"Column1", type duration}})
in
    #"Duration"

Regards,

 

[alex78]

Do not consider my previous script: it is wrong - Sorry

 

[alex78]

Updated function to convert in duration

let   
    fx_duration = (SourceTable as text, ColName as text) as table =>
      let x = Excel.CurrentWorkbook(){[Name=SourceTable]}[Content],
         fx = (x) =>
           let
            a = List.RemoveLastN(Splitter.SplitTextByAnyDelimiter({"d","h", "m","s"})(x),1),
            b = List.Select(Text.ToList(x), each List.ContainsAny({_}, {"a".."z"})),
            c = Table.FromRows(List.Zip({b,a}) &
                List.Transform(List.Difference({"d","h", "m","s"} , b), each {_, "0"})
                ),
            d = List.Transform(
                Record.FieldValues(
                Record.ReorderFields(Record.FromList(c[Column2], c[Column1]), {"d","h", "m","s"})
                ), Number.FromText),
            e = List.Accumulate({0..3}, {}, (s,c)=> s & {d{c}})
          in #duration(d{0}, d{1},d{2},d{3})
       in Table.TransformColumnTypes(
          Table.TransformColumns(x, {ColName, each try fx(_) otherwise _}), {{ColName, type duration}}
          ),
    Result = fx_duration("Table1", "Column1")
in
    Result

 

[Anand Sydney]

So, I have a column for time duration in my data from different excel files and tables which gets consolidated in power query...

But the data in the time duration column from these different files/tables is not the same, it's in these below 3 example formats:

00:24:02
00h 24m 02s
24m 02s

01h 24m 02s
01:24:02
01h 24m 02s


The first three examples represent 24 minutes and 2 seconds
The last three examples represent 1 hour, 24 minutes and 2 seconds.

The data is always in these three formats representing the time duration. And I have to do some conditional calculations and add measures using this time duration column but I can't do that unless I am able to convert it into a standard/default time format for my calculations.

Is there a way we can convert all three types in to one single time format

1)Either in excel before the data gets loaded in power query for consolidation?

2)Or in power query after its loaded for consolidation?

Inder,
If you can share a sample data I can attempt and come back to you with solution.
The time conversion is simple but needs a bit of attention while playing around.

Anand