Power Query to fetch the earliest record based on timestamp

Copper Contributor

I have two tables to calculate a response time value. It works like this: A job (e.. job number 98712) gets created (timestamp A). Then this job undergoes various workflow status to closure with the first one being 'job status set to Connect: Onsite' - timestamp B. The response time is timestamp B - A.

 

Now it's possible that there are a few trips until this job gets closed and every trip has its own onsite timestamp as you can see in the example where the technician is onsite 4 times. So I need to load the very first entry (i.e. 1 May 9:31). The others 3 in this case are irrelevant. I create merge query to pull in this data based on job number.

 

How can I either update the load criteria or transform the data so that the calculation takes the first timestamp (timestamp B)

 

tfmei140_0-1717655991042.png

 

6 Replies
Hi,

Can you share the sample data excel sheet so i can see the both the table structure.

@tfmei140 

You may GroupBy job and name returning as result min time, max time, their difference, whatever.

@Tejas_shah ok here it is: Job activity report and Staff log report 

 

Also here's the current code that pulls the wrong record (as opposed to the earliest record for jobs with multiple entries)

let
    Filepath = Excel.CurrentWorkbook(){[Name="Filepath"]}[Content]{0}[Column1],
    Source = Csv.Document(File.Contents(Filepath & "Staff_Log_Report_reportTable.csv"),[Delimiter=",", Columns=4, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",1),
    #"Promoted Headers" = Table.PromoteHeaders(#"Removed Top Rows", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Logged Time", type datetime}, {"Employee", type text}, {"ID", type text}, {"Message", type text}}),
    #"Split Column by Position" = Table.SplitColumn(#"Changed Type1", "ID", Splitter.SplitTextByRepeatedLengths(8), {"ID.1", "ID.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Position",{{"ID.1", type text}, {"ID.2", Int64.Type}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"ID.2", "Job No"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns",{"ID.1"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Message] = "Created Job" or [Message] = "Job status set to Connect: Onsite" or [Message] = "Job status set to Connect: Completed")),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"Job No"}, {{"_nestedTable", each Table.SelectColumns(_, {"Message", "Logged Time"}), type table [Job No=nullable number, Logged Time=nullable datetime, Message=nullable text]}}),
    Custom1 = Table.TransformColumns(#"Grouped Rows", {{"_nestedTable", each Table.PromoteHeaders(Table.Transpose(_))}}),
    #"Expanded _nestedTable" = Table.ExpandTableColumn(Custom1, "_nestedTable", {"Job status set to Connect: Onsite", "Job status set to Connect: Completed", "Created Job"}, {"Job status set to Connect: Onsite", "Job status set to Connect: Completed", "Created Job"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Expanded _nestedTable",{{"Job status set to Connect: Onsite", type datetime}, {"Job status set to Connect: Completed", type datetime}, {"Created Job", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type3", "Custom", each [#"Job status set to Connect: Onsite"]-[Created Job]),
    #"Renamed Columns1" = Table.RenameColumns(#"Added Custom",{{"Custom", "Response Time"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns1", "Custom", each Duration.TotalHours([Response Time])),
    #"Renamed Columns2" = Table.RenameColumns(#"Added Custom1",{{"Response Time", "Response Time hh and mm"}, {"Custom", "Response Time"}}),
    #"Inserted Rounding" = Table.AddColumn(#"Renamed Columns2", "Round", each Number.Round([Response Time], 2), type number)
in
    #"Inserted Rounding"

 

@tfmei140 

Perhaps like this

let
    Filepath = Excel.CurrentWorkbook(){[Name="Filepath"]}[Content]{0}[Column1],
    Source = Csv.Document(File.Contents(Filepath & "Staff_Log_Report_reportTable.csv"),[Delimiter=",", Columns=4, Encoding=65001, QuoteStyle=QuoteStyle.None]),

    RemoveTopRows = Table.Skip(Source,1),
    PromotHeaders = Table.PromoteHeaders(RemoveTopRows, [PromoteAllScalars=true]),
    GetJobNo = Table.TransformColumns(
        PromotHeaders,
        { {"ID", each Text.AfterDelimiter(_, " ", {0, RelativePosition.FromEnd}),
            type text}
        }),
    RenameIt = Table.RenameColumns(GetJobNo,{{"ID", "Job No"}}),
    RemoveNames = Table.RemoveColumns(RenameIt,{"Employee"}),
    ReordereColumns = Table.ReorderColumns(
        RemoveNames,
        {"Job No", "Message", "Logged Time"}),
    DeclareType = Table.TransformColumnTypes(
        ReordereColumns,
        {
              {"Job No", Int64.Type}
            , {"Logged Time", type datetime}
        }),
    TrimMessage = Table.TransformColumns(
        DeclareType,
        {{"Message", Text.Trim, type text}}),
    FiletrOnStatus = Table.SelectRows(
        TrimMessage,
        each
               [Message] = "Created Job"
            or [Message] = "Job status set to Connect: Onsite"
            or [Message] = "Job status set to Connect: Completed"
        ),
    PivotByStatus = Table.Pivot(
        FiletrOnStatus,
        List.Distinct(FiletrOnStatus[Message]), "Message", "Logged Time", List.Min),
    ReorderAgain = Table.ReorderColumns(
        PivotByStatus,
        {"Job No", "Created Job", "Job status set to Connect: Onsite", "Job status set to Connect: Completed"}),
    AddResponseTime = Table.AddColumn(
        ReorderAgain,
        "Response Time",
        each [#"Job status set to Connect: Completed"] - [Created Job],
        type duration)
in
    AddResponseTime

to receive

image.png

@Sergei Baklan Thanks for that update. Had to make a few final changes but I was able to get it to work :)

@tfmei140 , good to know, thank you for the feedback