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 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
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 🙂
8 Replies
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 🙂
Suprised it has to be so complicated! you would think this would be an inbuilt merge function like index-match in Excel
Hi Fowmy, when I open your link it doesn't show the file for me. Could you please send it again? Thanks!
The file is dead. The answer is useless without the file.
