Merging Tables and picking the closest date

[Anonymous]

Hello,

I'd like to know if anyone knows how to do this using Powerquery?

Table1:

ID     Date                     SubID
1      7/22/2020 5:49:55 PM     111
2      7/22/2020 3:12:11 PM     222
3      7/22/2020 9:31:16 PM     333

 Table2:

SubID     Date                    ActivityNum     ActivityName
111      7/22/2020 5:49:50 PM     1               sometext1
111      7/22/2020 5:51:10 PM     2               sometext2
111      7/22/2020 5:47:43 PM     3               sometext3
222      7/22/2020 3:08:11 PM     1               sometext1
222      7/22/2020 3:12:07 PM     2               sometext2
222      7/22/2020 3:13:19 PM     3               sometext3
222      7/22/2020 3:20:20 PM     4               sometext4
333      7/22/2020 9:24:45 PM     1               sometext1
333      7/22/2020 9:25:56 PM     2               sometext2
333      7/22/2020 9:25:58 PM     3               sometext3
333      7/22/2020 9:31:14 PM     4               sometext4
333      7/22/2020 9:35:21 PM     5               sometext5
333      7/22/2020 9:37:15 PM     6               sometext6

 Essentially, I have two tables as seen above and I'd like to merge Table1 to Table2 using the SubID. The problem here is that I should only merge the row in Table2 that is closest to the Date being pointed in Table1 which would mean this should be the end result:

ID     Date                     SubID     ActivityNum
1      7/22/2020 5:49:55 PM     111       1
2      7/22/2020 3:12:11 PM     222       2
3      7/22/2020 5:49:55 PM     333       4

 I already achieve it by using DAX but I need it in powerquery format so that I can further merge more tables in my Table1

[Louis1911]

The file is dead. The answer is useless without the file.

[Fowmy]

@Louis1911 

I have attached the file, please check. It was 3+ years back 🙂

[Louis1911]

You are right! I didn't see the attachment and only clicked on the hyperlink in the text.

My apologies and thank you for pointing it out! I'll check it out.

[dufoq3]

This could be also the solution:

Result

let
    Table1 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VcqxCcAwDATAVczXBvtfUYK1QyC98P5rJMSV2+MyQVRcTWrq6sXjGOFenvtjkpg1oa1YUEGuIukvtpURxuC5iplhzhc=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t, SubID = _t]),
    ChangedTypeTable1 = Table.TransformColumnTypes(Table1,{{"Date", type datetime}}, "en-US"),
    Table2 = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdExDoMwDIXhq6DMSMTPNgHfoVJ3xMhYdShDj98UGIKIJSRPyTf8caYpEFFoQ+qADhGxUZPRNDbPRz7+X33er2VdviuFua1xJaODo+BwuCQT3jkXnDcO4MTZ4mBEXsyVEywmL6bC84z3YxDz7FwKLhtn5hMfDWKiXnuF5132XnudD177lXP+Jrnfzmo49q4FV48no+OpfcH7MM8/", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [SubID = _t, Date = _t, ActivityNum = _t, ActivityName = _t]),
    ChangedTypeTable2 = Table.TransformColumnTypes(Table2,{{"Date", type datetime}}, "en-US"),
    MergedQueries = Table.NestedJoin(ChangedTypeTable1, {"SubID"}, ChangedTypeTable2, {"SubID"}, "ChangedTypeTable2", JoinKind.LeftOuter),
    Ad_ActivityNum = Table.AddColumn(MergedQueries, "ActivityNum", each Number.From(Table.Min(Table.AddColumn([ChangedTypeTable2], "Nearest DateTime", (x)=> Number.Abs(Number.From([Date]-x[Date])), type number), "Nearest DateTime")[ActivityNum]), Int64.Type),
    RemovedColumns = Table.RemoveColumns(Ad_ActivityNum,{"ChangedTypeTable2"})
in
    RemovedColumns

[Fowmy · Accepted solution]

@Anonymous 

I have attached the file for you to download and check the steps. I created without using custom M coding, all from GUI so you can go through the steps

Download the file below

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[Zova]

Hi Fowmy, when I open your link it doesn't show the file for me. Could you please send it again? Thanks!

[RyanCatling]

Suprised it has to be so complicated! you would think this would be an inbuilt merge function like index-match in Excel

[nandic]

Great work @Fowmy 
I was unaware of this option "All rows" in group by and stuck there. Great solution!