Get first date from another dataset using the Query Editor

[Ashish_Mathur]

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.

[negi007]

@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

[Ashish_Mathur]

Hi,

Thank you for replying.  This does not return the first date from Table2.  @jdbuchanan71 has answered my question.

Thank you.

[jdbuchanan71 · Accepted solution]

@Ashish_Mathur 

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.

[Ashish_Mathur]

Thank you.  This works.