[Power Query] matching two tables with not quite same dates

[ursua]

Hi,

I have two sheets named sheet1 and sheet2 with same columns: Identificator (which could be duplicated many times), date column (which is always the same), and date column2 (which i=could be the same or could be different for every row). What I need to do is to add a column in sheet2 which will display "1" when in sheet1 there is a row with the same identificator and date column2 that is not quite same, it could be f.e. 5 minutes later or earlier, and 0 if there is no matching row for identificator+date column2+-5 mins. it would not be a problem if the date column2 would be the same in both tables.
How to achieve that using Power Query?

 

[felixstraube]

So if i understand you correctly you want this?:

A match from "Identificator" + "Date column 2" (+-5 min) from both tables and build a 3 (result) like this (without the colors)?:

Sheet 1				Sheet 2				Result
Identificator	Date column	Date column 2		Identificator	Date column	Date column 2		Identificator	Date column	Date column 2	Result
aaa	28/11/2023 11:00	28/11/2023 11:50		aaa	28/11/2023 11:00	28/11/2023 11:49		aaa	28/11/2023 11:00	28/11/2023 11:50	1
aab	29/11/2023 13:40	30/11/2023 13:46		aaa	28/11/2023 11:00	28/11/2023 13:00		aab	29/11/2023 13:40	30/11/2023 13:46	1
aab	29/11/2023 13:40	01/12/2023 13:46		aab	29/11/2023 13:40	30/11/2023 13:45		aab	29/11/2023 13:40	01/12/2023 13:46	1
aac	13/10/2023 14:35	14/10/2023 03:03		aab	29/11/2023 13:40	01/12/2023 13:45		aac	13/10/2023 14:35	14/10/2023 03:03	1
aad	23/11/2023 05:00	23/11/2023 17:49		aab	29/11/2023 13:40	03/12/2023 11:09		aad	23/11/2023 05:00	23/11/2023 17:49	0
aac	13/10/2023 14:35	14/10/2023 15:34		aac	13/10/2023 14:35	14/10/2023 03:03		aac	13/10/2023 14:35	14/10/2023 15:34	0
aaf	30/11/2023 12:44	08/12/2023 15:09		aaf	30/11/2023 12:44	09/12/2023 03:05		aaf	30/11/2023 12:44	08/12/2023 15:09	0
				aac	13/10/2023 14:35	16/10/2023 12:30
				aad	23/11/2023 05:00	24/11/2023 14:50

 

[felixstraube]

If you have the 2 tables named Sheet1 and Sheet2 like so:

Then you create a third ("Combinar1") with this code:

let
    Origen = Table.NestedJoin(Sheet1, {"Identificator"}, Sheet2, {"Identificator"}, "Sheet2", JoinKind.LeftOuter),
    #"Se expandió Sheet2" = Table.ExpandTableColumn(Origen, "Sheet2", {"Date column 2"}, {"Sheet2.Date column 2"}),
    #"Tipo cambiado" = Table.TransformColumnTypes(#"Se expandió Sheet2",{{"Date column 2", type number}, {"Sheet2.Date column 2", type number}}),
    #"Personalizada agregada" = Table.AddColumn(#"Tipo cambiado", "TimeDif", each Number.Abs([Date column 2]-[Sheet2.Date column 2])),
    #"Tipo cambiado1" = Table.TransformColumnTypes(#"Personalizada agregada",{{"TimeDif", type duration}}),
    #"Filas filtradas" = Table.SelectRows(#"Tipo cambiado1", each [TimeDif] <= #duration(0, 0, 5, 0)),
    #"Duplicados quitados" = Table.Distinct(#"Filas filtradas", {"Identificator", "Date column", "Date column 2", "TimeDif"}),
    #"Personalizada agregada1" = Table.AddColumn(#"Duplicados quitados", "Result", each 1),
    #"Tipo cambiado2" = Table.TransformColumnTypes(#"Personalizada agregada1",{{"Date column 2", type datetime}})
in
    #"Tipo cambiado2"

And then a fourth ("Combinar2"):

let
    Origen = Table.NestedJoin(Sheet1, {"Identificator", "Date column", "Date column 2"}, Combinar1, {"Identificator", "Date column", "Date column 2"}, "Combinar1", JoinKind.LeftOuter),
    #"Se expandió Combinar1" = Table.ExpandTableColumn(Origen, "Combinar1", {"Result"}, {"Combinar1.Result"}),
    #"Valor reemplazado" = Table.ReplaceValue(#"Se expandió Combinar1",null,0,Replacer.ReplaceValue,{"Combinar1.Result"})
in
    #"Valor reemplazado"

And you will get this result:

Identificator	Date column	Date column 2	Combinar1.Result
aaa	28/11/2023 11:00	28/11/2023 11:50	1
aab	29/11/2023 13:40	30/11/2023 13:46	1
aab	29/11/2023 13:40	01/12/2023 13:46	1
aac	13/10/2023 14:35	14/10/2023 3:03	1
aad	23/11/2023 5:00	23/11/2023 17:49	0
aac	13/10/2023 14:35	14/10/2023 15:34	0
aaf	30/11/2023 12:44	08/12/2023 15:09	0

Is that what you need?

 

[ursua]

@felixstraube looks nice and very promising. i will apply it to my query and let You know.

 

[ursua]

That was the solution i've been looking for. Thank You very much!

 

[felixstraube]

Happy to help. Thanks for the feedback.