Time issue.

[PeteMclagan]

Help!

I have an automated report from a warehouse management system that is generated showing the time as 7.45 meaning a duration of 7hrs and 45 minutes. I can manipulate this to change to the correct time format. However when the time is 7.10 (7hrs and 10 minutes) When I try to convert to a time I get 7 hours and 1 minute. I have tried splitting column and manipulating the minutes on their own but the ones with a full 10 minutes just won't modify correctly.

 

[p45cal]

H5 contains decimal value:
=VALUE(INT(H5)&":"&ROUND(MOD(H5,1)*100,0)&":00")
format result as time
1. There's probably a slicker way, especially if you have MS365 - do you?
2. What form does this automated report take? A txt file, csv maybe? Something else? (There'll be better ways of importing this report into Excel.)

 

[PeteMclagan]

[QUOTE="p45cal, post: 295171, member: 8300"]
H5 contains decimal value:
=VALUE(INT(H5)&":"&ROUND(MOD(H5,1)*100,0)&":00")
format result as time
1. There's probably a slicker way, especially if you have MS365 - do you?


I have 2019


2. What form does this automated report take? A txt file, csv maybe? Something else? (There'll be better ways of importing this report into Excel.)
[/QUOTE]

It comes in on an excel file straight from the system.

I was hoping to be able to manipulate automatically through power query steps. Can I put this formula in as an applied step? Forgive my ignorance, I have just started using power query and it is amazing but I have started finding limitations like this.

 

[p45cal]

Something like this:

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    SplitColumnbyDelimiter = Table.SplitColumn(Table.TransformColumnTypes(Source, {{"Decimal Time", type text}}, "en-GB"), "Decimal Time", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Decimal Time1", "Decimal Time2"}),
    ReplacedValue = Table.ReplaceValue(SplitColumnbyDelimiter,null,"0",Replacer.ReplaceValue,{"Decimal Time2"}),
    AddedSuffix = Table.TransformColumns(ReplacedValue, {{"Decimal Time2", each _ & "0", type text}}),
    ExtractedFirstCharacters = Table.TransformColumns(AddedSuffix, {{"Decimal Time2", each Text.Start(_, 2), type text}}),
    ChangedType = Table.TransformColumnTypes(ExtractedFirstCharacters,{{"Decimal Time2", Int64.Type}, {"Decimal Time1", Int64.Type}}),
    AddedCustom = Table.AddColumn(ChangedType, "Time", each #time([Decimal Time1],[Decimal Time2],0),type time),
    RemovedOtherColumns = Table.SelectColumns(AddedCustom,{"Time"})
in
    RemovedOtherColumns

Attach a workbook and I'll adapt it.

 

[p45cal]

You could create a function (second line below) and use it as a single step (third line below);

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    fnTimeConvert = (DT) => let split=Text.Split(Text.From(DT),"."), myTime=#time(Number.From(split{0}),try Number.From(Text.Start(split{1} & "0",2)) otherwise 0,0) in myTime,
    NewTime = Table.TransformColumns(Source, {{"Decimal Time", each fnTimeConvert(_), type time}})
in
    NewTime

or bring it completely in-line:

let
    Source = Excel.CurrentWorkbook(){[Name="Table5"]}[Content],
    NewTime = Table.TransformColumns(Source, {{"Decimal Time", each let split=Text.Split(Text.From(_),"."), myTime=#time(Number.From(split{0}),try Number.From(Text.Start(split{1} & "0",2)) otherwise 0,0) in myTime, type time}})
in
    NewTime