Lookup duplicate records, bring the latest record

[Anonymous]

Hello

I am looking for a way, with Power Query, DAX, Measure or new Column, without eliminating duplicates, to bring me the latest record (the newest date) between two tables E.g.

Table 1		Table 2
ID	Date	Profile		ID	Test Result	Last Profile Result
XYZ	01/01/2022	Proactive		XYZ	Approved	Introvert
XYZ	02/01/2022	Proactive		ABC	Approved	Goal Oriented
XYZ	03/01/2022	Introvert		HIJ	Approved	Intuitive
ABC	03/01/2022	Goal Oriented
ABC	01/01/2022	Proactive
ABC	02/01/2022	Intuitive
HIJ	02/01/2022	Goal Oriented
HIJ	03/01/2022	Intuitive
HIJ	01/01/2022	Proactive

I need to retrieve the latest information from table #1, Profile Column (the newest date), to table #2 in the Column Last Profile Result.

Regards

[Anonymous]

Hi @Anonymous ,

You could first add a rank column in the Table 1 to get the earliest date. Then merge two tables.

Here's the steps.

1.Sort the Date column.

2.Group by the ID column

3.Add a custom column to get the ranking.

4.Expand the column, remove the unneeded column.

5.In the Table 2, merge with the Table 1.

6.Expand the columns, filter the row with rank 1.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[wdx223_Daniel]

in Table 2,

NewStep=let a=Table.Buffer(Table.Group(Table1,"ID",{"n",each Table.Max(_,"Date")[Profile]})) in Table.AddColumn(PreviousStepName,"Profile Result",each a{[ID=[ID]]}?[n]?)

[Anonymous]

I do not know if is my version of PBI or what but it is showing me an "Expression.Error" with 'previousStepName'

By the way tahnk you so much for your reply

[KT_Bsmart2gethe]

Hi @Anonymous ,

Power Query:

In Table 2, add a custom step (not add a custom column) and apply the formula below:

= Table.AddColumn(#"Changed Type", "Last Record", (x)=> List.Max(Table.SelectRows(Table1, each (x[ID]=[ID]) and (x[Last Profile Result]=[Profile]))[Date]))

Regards

KT

[Anonymous]

@KT_Bsmart2gethe It marks an "Expresion.Error" with the ´changed Type´ part, it did not recognize it.

Thank you for your Reply by the way

[KT_Bsmart2gethe]

Hi @Anonymous ,

I do apologise for not providing clear instructions. 

You must replace the #"Changed Type" with the previous step name. 

Say if your previous step name is "Source", then you will need to change it to Source; if the previous step name is "Added Column", then you will need to replace the provided name with #"Added Column" (You will need to use #"xxx" if the name contains space)

The same concept applies to the solution code provided by @wdx223_Daniel, and you will need to replace the"PreviousStepName" with the actual name from the previous step to have it works.

I hope it is clear for you and ensuring your challenge is resolved. 🙂

Regards

KT