Forum Discussion
Merging Tables and picking the closest date
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...
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 stepsDownload 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 🙂
The file is dead. The answer is useless without the file.
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.
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
