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 =...
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]
)
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 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]
)