How can i get only days,hours, minutes and seconds value from a list

[sandip]

Hi,

How can i get only days,hours, minutes and seconds value from the below list:

number	business_duration
CTAP0001260	39 Seconds
CTAP0003405	1 Day 10 Minutes
CTAP0003598	2 Hours 38 Minutes
CTAP0002718	4 Days 17 Hours 16 Minutes
CTAP0002349	11 Days 7 Hours 21 Minutes
CTAP0002774	1 Hour 43 Minutes
CTAP0002760	2 Hours 38 Minutes
CTAP0004456	1 Day 1 Hour 11 Minutes
CTAP0003110	16 Hours 23 Minutes
CTAP0003119	1 Hour 43 Minutes
CTAP0003212	1 Hour 43 Minutes
CTAP0004589	1 Hour 43 Minutes
CTAP0003715	16 Hours 23 Minutes
CTAP0003763	3 Days 7 Hours
CTAP0004221	1 Hour 43 Minutes

so from the above table cell, i want to convert each row to seconds value from like : 3 Days 7 Hours or 16 Hours 23 Minutes

so for 3 Days 7 Hours it should be 3*24*60*60 + 7*60*60....

and also I want to create a calculated for  this calculation and seconds value can be fitted into that new column.

Any idea how to achieve that?

[v-easonf-msft · Accepted solution]

Hi , @sandip 

Please check if the following  sample  helps.

Pbix attached .

Try following steps:

1.split column by deliliter "" 

2.select  the columns and replace value 
1)   change "Seconds","Minutes","" Hours ","Days"  as "Second","Minute","Hour","Day"

2）do the similar steps to change "Second","Minute","" Hour","Day"  as "1","60","3600","96400"

3.chage column types as "whole number" and create a custome column as below

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sandip]

Hi,

Can you help me to learn Poqwer query or do you have any PDF to learn from it?

Thanks,

Sandip

[ryan_mayu]

@sandip 

You can try to create a column.Please note that the second logic is very easy because of the data you shared. I can only see seconds without minutes ,hours and days. If you have the combination of day with seconds, hour, minute with seconds, minute with seconds. The calculation for find the seconds value should be the most complicated.

Column2 = 
VAR daypostion=FIND("D",Sheet8[duration],1,0)
VAR hourpostion=find("H",Sheet8[duration],1,0)
VAR minutepostion=FIND("M",Sheet8[duration],1,0)
VAR secondpostion=FIND("Se",Sheet8[duration],1,0)
VAR dayvalue=if(daypostion=0,blank(),trim(left(Sheet8[duration],daypostion-1)))
VAR hoursvalue=if(hourpostion=0,blank(),if(daypostion=0,trim(left(Sheet8[duration],hourpostion-1)),trim(mid(Sheet8[duration],daypostion+4,hourpostion-(daypostion+4)))))
VAR minvalue=if(minutepostion=0,blank(),if(daypostion=0&&hourpostion=0,left(Sheet8[duration],minutepostion-1),if(hourpostion=0,trim(mid(Sheet8[duration],daypostion+4,minutepostion-(daypostion+4))),trim(mid(Sheet8[duration],hourpostion+5,minutepostion-(hourpostion+5))))))
VAR secondvalue=if(secondpostion=0,BLANK(),trim(LEFT(Sheet8[duration],secondpostion-1)))
return dayvalue*24*60*60+hoursvalue*60*60+minvalue*60+secondvalue

.

[edhans]

Try this. It returns this table in Power Query. This is very tedious and not bullet proof. Any extra spaces will cause this to not work. You'd need a lot more if/then/else handeling to account for that.

NOTE: I had to make a slight tweak to the code. It seems to work fine. The image below is returning the wrong amounts on a few occassions, but the code works. (I think). I didn't bother redoing the screen cap.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZE9C8IwEIb/ypHZIfeRpBlFBxdB0K10EO3g0oK1g//epK1K4bDO9/C+z92Vpdmc1gdrLZK3ZmU4wrG+tM21M9XqM2SxLg0RtucnoIX9rekf9RxxsUgIwa7t7x1woUEUMEOSczrAMMHoVZgl5lIc6TdMqCcHGQwzBMI6M6z4W1HE+e+qYxyqlYyY45L8JKaWJiouiTEhLTHiiuWcgO4Po+A5f3p21lkVEepV1Qs=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [number = _t, business_duration = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"number", type text}, {"business_duration", type text}}),
    #"Added Days" = 
        Table.AddColumn(
            #"Changed Type", 
            "Days", 
            each
                if Text.PositionOf([business_duration], "Day") > 1
                then Number.From(Text.Middle([business_duration],0,Text.PositionOf([business_duration], "Day"))) * 86400
                else 0
            ),
    #"Added Hours" =
        Table.AddColumn(
            #"Added Days", 
            "Hours", 
            each
                if Text.PositionOf([business_duration], "Hour") > 1 then 
                    if Text.PositionOf([business_duration], "Hour") > 8
                    then Number.From(Text.Middle([business_duration],Text.PositionOf([business_duration], "Hour") - 3,2)) * 3600
                    else Number.From(Text.Start([business_duration],2)) * 3600
                else 0
            ),
    #"Added Minutes" =
        Table.AddColumn(
            #"Added Hours", 
            "Minute String", 
            each
                if Text.PositionOf([business_duration], "Minute") > 1 then
                    if Text.PositionOf([business_duration], "Minute") > 8
                    then Number.From((Text.Middle([business_duration],Text.PositionOf([business_duration], "Minute") - 3,2))) * 60
                    else Number.From((Text.Start([business_duration],2))) * 60
                else 0
            ),
    #"Added Seconds" =
        Table.AddColumn(
            #"Added Minutes", 
            "Seconds String", 
            each
                if Text.PositionOf([business_duration], "Seconds") > 1 then
                if Text.PositionOf([business_duration], "Seconds") > 8
                    then Number.From(Text.Middle([business_duration],Text.PositionOf([business_duration], "Seconds") - 3,2))
                    else Number.From(Text.Start([business_duration],2))
                else 0
            ),
    #"Inserted Sum" = Table.AddColumn(#"Added Seconds", "Total Seconds", each List.Sum({[Days], [Hours], [Minute String], [Seconds String]}), Int64.Type),
    #"Removed Other Columns" = Table.SelectColumns(#"Inserted Sum",{"number", "business_duration", "Total Seconds"})

in
    #"Removed Other Columns"

1) In Power Query, select New Source, then Blank Query
2) On the Home ribbon, select "Advanced Editor" button
3) Remove everything you see, then paste the M code I've given you in that box.
4) Press Done

I didn't create this in DAX because generally calculated columns perform worse for larger data sets.

In general, try to avoid calculated columns. There are times to use them, but it is rare. Getting data out of the source system, creating columns in Power Query, or DAX Measures are usually preferred to calculated columns. See these references:
Calculated Columns vs Measures in DAX
Calculated Columns and Measures in DAX
Storage differences between calculated columns and calculated tables
Creating a Dynamic Date Table in Power Query

and in my defense, if this were my project internally, I'd do a lot of cleanup of the code and use some variables, but that is a bit tedious in Power Query so went for the brute force approach. 😁