Time duration not able to covert to hh:mm

[krajivga]

we ahve the downloaded data for the operation time in text form example 03:45 , if we load the data in the Power query data not converted to duration if the value beyound 24:00 , shows error . if we convert in differnt form but not able to use in visuals to create bar graph/ pivot chart. able to use only as Card.

[v-nmadadi-msft]

Hi @krajivga 

May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.

Thank you

[v-nmadadi-msft]

Hi @krajivga 

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.

Thank you.

[vojtechsima]

hey, @krajivga ,

if you want actual code, how to split it, here it is:

= Table.AddColumn(#"Changed Type", "Custom", each let 
hours = Text.BeforeDelimiter([dur], ":"),
minutes = Text.AfterDelimiter([dur], ":"),
dur = #duration(0, Number.From(hours), Number.From(minutes), 0)
in dur, type duration)

click new Step and replace it with this, make sure you correctly change #"Changed Type" to your last step.

If you wanna go throught the GUI, click new custom column and paste this:

let 
hours = Text.BeforeDelimiter([dur], ":"),
minutes = Text.AfterDelimiter([dur], ":"),
dur = #duration(0, Number.From(hours), Number.From(minutes), 0)
in dur

This will then be converted to a number later in DAX:

[krajivga]

Thank u very much

[krajivga]

Hi ,

The Format of input which is downloaded report from SAP (its in Text form of hours) , while in excel we have sum after converting the data type thru delimiter to sum up even beyound 24 hrs, but in power query we have tried many steps, it won't work as per our requirment . 

1. data type should in duration also retain the format HH:MM even beyound 24 hrs.

2. This need to be summed up, also to be used in bar and pivot charts .

pls suggest solution for that 

[zenisekd · Accepted solution]

my suggestion would be to convert the HH:MM to a regular number ( split the value by ":" and then divide the minutes by 60. then add it again). This way you get from 26:40 number 26,66. Then you can sum it in power bi and work with it in visuals. Next you could probably convert the number in a measure to display the number in the format you want (26:40 again...)

[krajivga]

Ok thanks, i will work on this like that.

[zenisekd]

Hi, the reason is that the format you provide is not supported. 
Check out this: https://community.fabric.microsoft.com/t5/Power-Query/Data-Type-Duration/td-p/3134574