Forum Discussion

pawelj795's avatar
pawelj795
Icon for Post Prodigy rankPost Prodigy
5 years ago
Solved

Remove duplicates with grouping by, leave most recent value

Hi, I'm currently working on filtering out old BOM's which my company replaced with new ones. Below, there is a table with BOM lines. The same BOM always has the same parent_product_tmpl_i...
Need Help
Show and Tell
  • PhilipTreacy's avatar
    PhilipTreacy
    5 years ago

    Hi pawelj795 

     

    Download example PBIX file and data

     

    I'm not sure I've followed your logic totally as I end up with a LOT of rows for each parent_product_tmpl_id.

    But according to what you've described I soudl be grouping on the parent_product_tmpl_id field and not the bom_id field.

     

    In my file above, check the Merge1 query for this results table.  I had to recreate your data from your tables in the Data Model as I couldn't open your query and access your SQL source.  The data is with the PBIX file above.

     

    Regards

    Phil

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hello,


You can solve this problem in both DAX and Power Query in which grouping by is not neccesary.

You want to define the latest record as the current record and define records with an older date as history.

 

The solution in DAX would look like:

 

COLUMN = 
VAR LatestRecordParentID = 
    CALCULATE(
        MAX( table[create_date] ) ,
        ALLEXCEPT( table , [parent_product_tmpl_id] ) ,
    )

RETURN
    IF(
        table[create_date] = LatestRecordParentID ,
        "Current record" ,
        "History record"
    )

You can use this column to filter out previous records and only keep the current.

If you want to solve this problem in Power Query you can apply the same logic of the DAX formula using MAX and FOR EACH.

 

If you need any help, please let me know!