Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

@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:

jdbuchanan71_1-1617113977904.png

This should give you the data you are lookin for in a query you can join back to your other data.

jdbuchanan71_2-1617114053460.png

 

 

 

View solution in original post

4 REPLIES 4
negi007
Community Champion
Community Champion

@Ashish_Mathur You can use merge queries option to merge values from both the tables like below

Table 1

negi007_0-1617114218918.png

 

Table 2

negi007_1-1617114237654.png

 

Merge Queries

 

negi007_2-1617114301131.png

 

click on expand button 

negi007_3-1617114325344.png

 

negi007_4-1617114358021.png

 

final output is below

 

negi007_5-1617114378972.png

 




Did I answer your question? Mark my post as a solution!
Appreciate your Kudos



Proud to be a Super User!


Follow me on linkedin

Hi,

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

Thank you.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
jdbuchanan71
Super User
Super User

@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:

jdbuchanan71_1-1617113977904.png

This should give you the data you are lookin for in a query you can join back to your other data.

jdbuchanan71_2-1617114053460.png

 

 

 

Thank you.  This works.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.