Forum Discussion
DATE AND TIME MATCHING WITH ERROR
I have following two tables with date & time table
I want to fetch data in table A as shown below
| TABLE B | |||
| CODE | DATE & TIME | INCIDENT ID | ENTRY TYPE |
| H12345 | 01/10/2021 08:00 | 75769338 | A |
| H76793 | 02/10/2021 08:00 | 79879893 | B |
| H87974 | 03/10/2021 08:00 | 987979721 | C |
| H89448 | 04/10/2021 08:00 | 3628643 | E |
| TABLE A | |
| CODE | DATE & TIME |
| H12345 | 01/10/2021 08:15 |
| H76793 | 02/10/2021 08:30 |
| H87974 | 03/10/2021 08:20 |
| H89448 | 04/10/2021 08:05 |
Final result required
| TABLE A (FINAL) | DATA MATCHING WITH ERROR OF 30 MINS | ||
| CODE | DATE & TIME | incident | ENTRY TYPE |
| H12345 | 01/10/2021 08:15 | 75769338 | A |
| H76793 | 02/10/2021 08:30 | 79879893 | B |
| H87974 | 03/10/2021 08:20 | 987979721 | C |
| H89448 | 04/10/2021 08:05 | 3628643 | E |
visittokiran you can create a measure like this and utilize this a visual level filter
_filter = VAR _a =CALCULATE(MAX('table A'[DATE & TIME])) VAR _b =CALCULATE(MAX('table B'[DATE & TIME])) VAR _c = ABS(DATEDIFF(_a,_b,MINUTE)) RETURN switch(true(),_c=30||_c=0,1)
6 Replies
Hi visittokiran ,
I'm not sure I fully understand your requirements here.
It looks like [CODE] is a unique field in both tables, so why not just merge tableA with tableB on tableA[CODE] = tableB[CODE]?
Pete
Sorry for late reply BA_Pete , you are right but sorry i did not mentioned that in that data there are multiple entried in a days for same code. In that case how to match data ?
OK, no worries.
Can you provide a more representative example of your data please?
If you can create an example of tableA and tableB in Power Query (Enter Data) then just post the M code for each that would be ideal.
Pete
BA_Pete in both data tables there are multiple entried for same code ...
