Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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.
Solved! Go to Solution.
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 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
Proud to be a Super User!
Hi,
Thank you for replying. This does not return the first date from Table2. @jdbuchanan71 has answered my question.
Thank you.
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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
64 | |
63 | |
52 | |
36 | |
36 |
User | Count |
---|---|
80 | |
71 | |
58 | |
45 | |
44 |