Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Lookup duplicate records, bring the latest record

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
IDDateProfile IDTest ResultLast Profile Result
XYZ01/01/2022Proactive XYZApprovedIntrovert
XYZ02/01/2022Proactive ABCApprovedGoal Oriented
XYZ03/01/2022Introvert HIJApprovedIntuitive
ABC03/01/2022Goal Oriented    
ABC01/01/2022Proactive    
ABC02/01/2022Intuitive    
HIJ02/01/2022Goal Oriented    
HIJ03/01/2022Intuitive    
HIJ01/01/2022Proactive    

 

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

Need Help

6 Replies

  • KT_Bsmart2gethe's avatar
    KT_Bsmart2gethe
    Icon for Impactful Individual rankImpactful Individual
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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's avatar
        KT_Bsmart2gethe
        Icon for Impactful Individual rankImpactful Individual
        4 years ago

        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

  • wdx223_Daniel's avatar
    wdx223_Daniel
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.