The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hey guys,
I have been struggling with this problem for a while now in Excel. Realized its easier to work in PBI but I can't figure out a way to do it.
As shown in the example picture, I have a big list of data with similar repetitions of same ULID. I need to exact match the ITMCOD, ULID, DATE and then return the nearest timestamp for that result.
Would anybody know how this can be done?
Best regards
@Fowmy Hey I saw you reply to a similar thread in the past. Unfortunately I couldn't understand the solution that you posted for this other solution. Could you please maybe have a look at mine?
Thank you!
@serpiva64thanks for the reply. Here is the sample dataset:
A
CODE | PALETTE | DATETIME |
91511352 | 0034260033126760377 | 9/15/2022 1:28:29 AM |
91511352 | 0034260033126760377 | 9/15/2022 1:35:05 AM |
91511352 | 0034260033126760377 | 9/15/2022 11:13:38 PM |
91511356 | 3,426E+16 | 9/17/2022 10:13:38 PM |
B
CODE | PALETTE | DATETIME |
91511352 | 0034260033126760377 | 15/09/2022 01:45:33 |
91511352 | 0034260033126760377 | 15/09/2022 02:48:48 |
91511352 | 0034260033126760377 | 15/09/2022 05:48:02 |
91511352 | 0034260033126760377 | 15/09/2022 23:48:05 |
91511352 | 0034260033126760377 | 16/09/2022 01:40:05 |
Sorry but i can't find 23:36:27 in your table A
Sorry about that, we can assume 9/15/2022 11:13:38 PM instead of 23:36:27. Either ways it should match to 15/09/2022 23:48:05 from B.
Hi @serpiva64 ,
Thank you so much for your effort. Unfortunately it doesn't work with my dataset which is fairly large. Was my mistake setting up the sample data.
Would you have a suggestion if I just want to match with a condition of a timeframe that it matches the nearest data within next 3 hours? I think that might work too in my case. Appreciate your time.
I realize the problem occurs at the last step, when I remove the duplicates. It sometimes removes the right one. Example:
We have two matches here. Second one would be the correct match. But when I remove duplicate on the last step, it removes the second one.
Hi,
can you post some data that cause this problem
Hi,
i thought there might be some problems with real data.
I foreseen the problem of sorting and i will try to solve it.
I don't know why it doesn't show the table border. Basically first eight digit is the CODE, then again the date starts from 9 on Table A and 15 on Table B
Hi,
please post your sample data not as an image and with some more rows if possible
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
137 | |
106 | |
103 | |
73 | |
59 |
User | Count |
---|---|
266 | |
127 | |
119 | |
100 | |
86 |