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

PhilipTreacy's avatar
PhilipTreacy
Icon for Super User rankSuper User
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

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

    PhilipTreacy 
    Well, your result seems okay.
    But, could you explain to me step by step, how you've done it?

    • PhilipTreacy's avatar
      PhilipTreacy
      Icon for Super User rankSuper User
      5 years ago

      Hi pawelj795 

      1. The query called Table1 loads the data.

      2. I duplicated this and the new query is called Table 1 (2). 

      3. I grouped the data by the parent_product_tmpl_id column and set the operation to pick the Max value (the latest date) from the create_date column

      This results in a table that has 1 row per parent_product_tmpl_id showing the latest date associated with that id.

       

      5. Merge the 2 queries (Merge as new to create a 3rd query) using these settings

      What this does is pull the rows from Table1 that have the (latest) date shown in the Table1 (2) query for each parent_product_tmpl_id.  All the other rows, the ones with earlier dates, are ignored.

       

      6. Expand the Tables in the merged query to show the other columns you want.

       

      Regards

      Phil