Forum Discussion

kucluk_ok's avatar
kucluk_ok
Frequent Visitor
3 years ago
Solved

Change data duration to seconds from format d h m s

I Have raw data duration , where the format is not standard like this

i want to convert from Column "Down" to column "inSEC" using this query to manage the d=86400 seconds, h=3600seconds , m =60 seconds

inSEC = SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Report[Down],"d","*86400"),"h","*3600"),"m","*60"),"s","*1")," ","+")

 

and i want to the query automaticly calculate the "multiply" and "add"

example in 31d = 31*86400 = 2,678,400 seconds

 

 

 

 

Need Help
  • wdx223_Daniel's avatar
    wdx223_Daniel
    3 years ago
    inSec = 
SUMX (
    ADDCOLUMNS (
        DATATABLE (
            "n", STRING,
            "f", INTEGER,
            {
                { "d", 86400 },
                { "h", 3600 },
                { "m", 60 },
                { "s", 1 }
            }
        ),
        "@sec",
            VAR _p =
                SUBSTITUTE (
                    SUBSTITUTE (
                        SUBSTITUTE (
                            MID ( 'Report'[Down], 1, FIND ( [n], 'Report'[Down],, 1 ) - 1 ),
                            "m",
                            "|"
                        ),
                        "h",
                        "|"
                    ),
                    "d",
                    "|"
                )
            RETURN
                VALUE ( 0 & PATHITEM ( _p, PATHLENGTH ( _p ) ) ) * [f]
    ),
    [@sec]
)

4 Replies

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion
    3 years ago
    inSec = 
SUMX (
    ADDCOLUMNS (
        DATATABLE (
            "n", STRING,
            "f", INTEGER,
            {
                { "d", 86400 },
                { "h", 3600 },
                { "m", 60 },
                { "s", 1 }
            }
        ),
        "@sec",
            VAR _p =
                SUBSTITUTE (
                    SUBSTITUTE (
                        SUBSTITUTE (
                            MID ( 'Report'[Down], 1, FIND ( [n], 'Report'[Down],, 1 ) - 1 ),
                            "m",
                            "|"
                        ),
                        "h",
                        "|"
                    ),
                    "d",
                    "|"
                )
            RETURN
                VALUE ( 0 & PATHITEM ( _p, PATHLENGTH ( _p ) ) ) * [f]
    ),
    [@sec]
)
    • kucluk_ok's avatar
      kucluk_ok
      Frequent Visitor
      3 years ago

      I Got error- " cannot convert value 0 45 of type Text to type number"

       

      • kucluk_ok's avatar
        kucluk_ok
        Frequent Visitor
        3 years ago

        add one more subtitute to remove space from data.

         

    • kucluk_ok's avatar
      kucluk_ok
      Frequent Visitor
      3 years ago

      I Got it, why error, because thereis space between data, i add subtitute to remove space

       

      Column = 
SUMX (
    ADDCOLUMNS (
        DATATABLE (
            "n", STRING,
            "f", INTEGER,
            {
                { "d", 86400 },
                { "h", 3600 },
                { "m", 60 },
                { "s", 1 }
            }
        ),
        "@sec",
            VAR _p =
               SUBSTITUTE( SUBSTITUTE (
                    SUBSTITUTE (
                        SUBSTITUTE (
                            MID ( 'Report'[Down], 1, FIND ( [n], 'Report'[Down],, 1 ) - 1 ),
                            "m",
                            "|"
                        ),
                        "h",
                        "|"
                    ),
                    "d",
                    "|"
                )," ","")
            RETURN
                VALUE ( 0 & PATHITEM ( _p, PATHLENGTH ( _p ) ) ) * [f]
    ),
    [@sec]
)