Forum Discussion
Merging two tables according to Date/Time with some offset in time
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.
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 _mintimeThen 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.
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 = "[email protected]", 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))
2 Replies
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 _mintimeThen 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.
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 = "[email protected]", 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))