Getting the Latest Record to Merge Queries

[newpbiuser01]

Hello,

I am trying to merge two queries in Query Editor. The first query has the employee records that shows the employee status over the previous years (so we could have multiple records for the same employee if their status changes), and the second query is the sales table. I am trying to get the latest status for each employee in the sales table. How do I flag the Latest Status for each employee in the first table within Power Query and then merge the tables to only pull those records? I'd really appreciate any help! 

Employee Records Table:

Date	Status	Name
09-Nov-20	Active	A
01-Nov-20	Inactive	A
06-Mar-20	Active	A
03-Dec-21	Active	B
12-Mar-20	Inactive	B
08-May-19	Active	B
09-Dec-21	Inactive	C
08-May-19	Active	C

 Sales Table: 

Name	Sales	Status
A	$1	Active
B	$2	Active
C	$1	Inactive

Thank you!

[Hariharan_R · Accepted solution]

Hi,

You can group the first table and get employee with latest status and then join with 2nd table to get the status easily.

1. Group the table by Name. Group by will generate a syntex and need to update like below.

= Table.Group(#"Changed Type", {"EmpID"}, {{"All", each  Table.AddIndexColumn(
        Table.Sort(_,{{"Date", 
        Order.Ascending}}), "Row Rank",1,1),  type table [Date=nullable date, EmpID=nullable text, Status=nullable text, Row Rank=nullable number]}})

2. Expand teh table and then filter for Rank =1

3. You can now merge with another table

Thanks

Hari

Did I answer your question? Then please mark my post as the solution.
If I helped you, click on the Thumbs Up to give Kudos.

My Blog :: YouTube Channel :: My Linkedin

[newpbiuser01]

Thanks @Hariharan_R ! Works like a charm!