DUARTION

[prabhu_rathnam]

Hi

Question:

Table

Job_name, starttime, endtime

ods, 5/4/2018 01:01:23, 5/4/2018 01:02:40

ods, 5/4/2018 01:15:10, 5/4/2018 01:15:40

edw, 5/4/2018 01:04:18, 5/4/2018 01:06:54

edw, 5/4/2018 01:18:21, 5/4/2018 01:20:26

Answer:

i want to get report as:

ods and edw starttime and endtime in a same row with duration between (edw_starttime -ods_starttime)

5/4/2018 01:01:23, 5/4/2018 01:02:40, 5/4/2018 01:18:21, 5/4/2018 01:20:26, Duration

Can someone please help me..

[v-yulgu-msft · Accepted solution]

Hi @prabhu_rathnam,

First, add an index column with below M code.

let
    Source = Excel.Workbook(File.Contents("C:\Users\xxxx\Desktop\Sample Data.xlsx"), null, true),
    DUARTION_Sheet = Source{[Item="DUARTION",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(DUARTION_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Job_name", type text}, {"starttime", type datetime}, {"endtime", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Job_name"}, {{"New Col", each Table.AddIndexColumn(_, "Index",1,1), type table}}),
    #"Expanded New Col" = Table.ExpandTableColumn(#"Grouped Rows", "New Col", {"starttime", "endtime", "Index"}, {"New Col.starttime", "New Col.endtime", "New Col.Index"}),
    #"Renamed Columns" = Table.RenameColumns(#"Expanded New Col",{{"New Col.starttime", "starttime"}, {"New Col.endtime", "endtime"}, {"New Col.Index", "Index"}})
in
    #"Renamed Columns"

Then, in data view, add a calculated column.

edw_starttime -ods_starttime =
IF (
    DUARTION[Job_name] = "edw",
    DATEDIFF (
        CALCULATE (
            MAX ( DUARTION[starttime] ),
            FILTER (
                DUARTION,
                DUARTION[Index] = EARLIER ( DUARTION[Index] )
                    && DUARTION[Job_name] <> EARLIER ( DUARTION[Job_name] )
            )
        ),
        DUARTION[starttime],
        SECOND
    )
)

New a calculated table.

DUARTION2 =
UNION (
    SELECTCOLUMNS (
        DUARTION,
        "Job_name", DUARTION[Job_name],
        "Index", DUARTION[Index],
        "starttime", DUARTION[starttime],
        "endtime", DUARTION[endtime]
    ),
    SELECTCOLUMNS (
        DUARTION,
        "Job_name", "duration",
        "Index", DUARTION[Index],
        "startname", BLANK (),
        "endtime", DUARTION[edw_starttime -ods_starttime]
    )
)

Insert a matrix to display data from 'DUARTION2'.

Best regards,

Yuliana Gu