Merging two tables according to Date/Time with some offset in time

[Anonymous]

Hello,

I have two tables, we will call one "Table A" and "Table B." Table A only has 3 columns, Date/Time, Machine, and a reason (this column does not matter). Table B has many many columns, but we will only care about Date/Time, Machine, and Aborted_Builds (value = 1/0). Table A is tracking a reason for an event that is occuring and Table B is tracking that an event occured but has no reason, so that is why Table A was needed to be created. Table A can only be "MACHINE1" or "MACHINE2", but table B can be "MACHINE1" -> "MACHINE20". I want to compare Table B with Table A but only the values in table B that are either "MACHINE1" or "MACHINE2" and only when Aborted_Builds column is = "1"

I want to merge these two tables based off of the Date/Time. But the transaction of these two tables occuring is not the same. There is always some amount of time between the both, and it switches which Table A or Table B occurs first. Based off of some data analysis, the delay is between +10 seconds of -60 seconds based off of Table B as zero. 

I have included a screen shot of some sample data, I would like to connect the Date/Time column together. There is obvisouly many values that may be close to the date/time, but I would like the quierie to automatically select the value that is closest. For example 6:19 pm is close to 6:20 and 5:58, but it is clearly closer to 6:20 so I would like it to match with that date/time column. 

Thanks for any help/guidance anyone can give me. 

[v-xinruzhu-msft · Accepted solution]

Hi @Anonymous 

You can create a custom column in table a first.

=let _time=[#"Date/Time"],
_mintime=List.Min(Table.SelectRows(#"Table B",each [#"Date/Time"]>=_time)[#"Date/Time"]),
_maxtime=List.Max(Table.SelectRows(#"Table B",each [#"Date/Time"]<=_time)[#"Date/Time"]),
_diffmin=_mintime-_time,
_diffmax=_time-_maxtime
in if _diffmin>_diffmax then _maxtime else _mintime

Then merge two tables based on the custom column.

Output

And you can refer to the attachment.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Andreas_Killer · Accepted solution]

Example tables with random data:

Result:

Invoke the custom function below.

Andreas.

// fnMatchNearest
let
  fnMatchNearest = (Date as date, Time as time, Table2 as table, optional DateName as text, optional TimeName as text) =>
    let
      // Prepare
      DN = if DateName=null then "Date" else DateName,
      TN = if TimeName=null then "Time" else TimeName,
      // Prefilter the table to find the relevant days
      PrevDay = Date.AddDays(Date.From(Date), - 1),
      NextDay = Date.AddDays(Date.From(Date), 1),
      RelevantDays = Table.SelectRows(Table2, each Record.Field(_, DN) >= PrevDay and Record.Field(_, DN) <= NextDay),
      // We need to find the closest to this time point
      ThisDateTime = Number.From(Date) + Number.From(Time),
      // Temporary name for the difference
      DiffName = Text.NewGuid(),
      // Calculate the difference in each row
      DiffTable = Table.AddColumn(
        RelevantDays,
        DiffName,
        each Number.Abs(Number.From(Record.Field(_, DN)) + Number.From(Record.Field(_, TN)) - ThisDateTime)
      ),
      // Find the closest
      MinDiff = List.Min(Table.Column(DiffTable, DiffName)),
      // Return the result table
      Result = Table.RemoveColumns(
        Table.SelectRows(DiffTable, each Record.Field(_, DiffName) = MinDiff),
        DiffName
      )
    in
      Result,
documentation = [
  Documentation.Name =  "fnMatchNearest",
  Documentation.Description = "Find the nearest Date/Time from Table2",
  Documentation.LongDescription = Documentation.Description,
  Documentation.Category = "Data",
  Documentation.Source = "andreas.killer@gmx.net",
  Documentation.Version = "1.0",
  Documentation.Author = "Andreas Killer",
  Documentation.Examples = {[
    Description = "= Table.AddColumn(Source, ""fnMatchNearest"", each fnMatchNearest([Date], [Time], Table2))

", 
    Code = "
Designed to be called as custom function.

Date     : The source date
Time     : The source time
Table2   : A table that contains at min. a date and time column
DateName : Name of the date column in Table2, ""Date"" if omitted
TimeName : Name of the time column in Table2, ""Time"" if omitted
", 
    Result = ""]}]
in  
  Value.ReplaceType(fnMatchNearest, Value.ReplaceMetadata(Value.Type(fnMatchNearest), documentation))

[v-xinruzhu-msft · Accepted solution]

Hi @Anonymous 

You can create a custom column in table a first.

=let _time=[#"Date/Time"],
_mintime=List.Min(Table.SelectRows(#"Table B",each [#"Date/Time"]>=_time)[#"Date/Time"]),
_maxtime=List.Max(Table.SelectRows(#"Table B",each [#"Date/Time"]<=_time)[#"Date/Time"]),
_diffmin=_mintime-_time,
_diffmax=_time-_maxtime
in if _diffmin>_diffmax then _maxtime else _mintime

Then merge two tables based on the custom column.

Output

And you can refer to the attachment.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.