Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
Solved! Go to Solution.
@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)
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
Proud to be a Datanaut!
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
Proud to be a Datanaut!
@BA_Pete I will try to explain in detail.
There are two data tables table A & Table B
I want to fetch data from table B to table A or Creating dashboard will also be fine.
While fetching data from Table B following logic need to be implemented.
First for every row in Table A it first Code no will be checked.
IF code No Matched then Date will be mate if Date Matched then
Time will be matched But time is not exact match in both table.
There is some time gap in time mentioned in table A.
So I want to match time with +-30 min span.
If time also matches then data from INCIDENT & ENTRY TYPE SHALL BE FETCHED IN TABLE A FOR THAT SPECIFIC ROW IN TABLE A.
@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)