Forum Discussion
Get first date from another dataset using the Query Editor
Hi,
My source data table looks like this
| Emp Code | DoJ | Report as at |
| A001 | 14-05-2020 | 01-06-2020 |
| A002 | 02-03-2019 | 01-07-2020 |
| A003 | 13-06-2019 | 01-08-2020 |
| A001 | 14-05-2020 | 01-07-2020 |
| A002 | 02-03-2019 | 01-07-2020 |
There can be repetitions in the Emp Code column but the DoJ will obviously remain the same for all instances of an Emp code. This report gets extracted every month. I have another table, which has only 2 columns
| Emp Code | DoJ |
| A001 | 14-05-2020 |
| A002 | 02-03-2019 |
| A003 | 13-06-2019 |
I would like to extrct into a third column, the first "Report as at" date from Table1. So the expected result is:
| Emp Code | DoJ | Report as at |
| A001 | 14-05-2020 | 01-06-2020 |
| A002 | 02-03-2019 | 01-07-2020 |
| A003 | 13-06-2019 | 01-08-2020 |
I want to solve this with the Query Editor. I do not want a DAX solution.
Thank you for your help.
In the query editor you can select your source, right click and pick reference. This will give you a new query that starts with your source. Then, on your new query, under transform pick group by and pick Emplyee Number with a new column of Min As At:
This should give you the data you are lookin for in a query you can join back to your other data.
4 Replies
In the query editor you can select your source, right click and pick reference. This will give you a new query that starts with your source. Then, on your new query, under transform pick group by and pick Emplyee Number with a new column of Min As At:
This should give you the data you are lookin for in a query you can join back to your other data.
Thank you. This works.
Ashish_Mathur You can use merge queries option to merge values from both the tables like below
Table 1
Table 2
Merge Queries
click on expand button
final output is below
Hi,
Thank you for replying. This does not return the first date from Table2. jdbuchanan71 has answered my question.
Thank you.
