Creat a new clean table with conditional filtering from an existing table

[Orlando_Tuga]

Hi all

I am struggling to find a way to overcome this challenge. Would appreciate some help as I do not know if it’s possible or not.

I have the following Table (Item Costs) (Resumed view for1 item only “JDE Description”):

JDE Description	Short Item	TP1 Effective Date	TP1 Expiration Date	TP1 Base Price
19936-150F	701689	20240401	20250331	68,99
19936-150F	695421	20240401	20250331	37,8
19936-150F	701689	20230401	20240331	37,8
19936-150F	695421	20230401	20240331	37,8
19936-150F	701689	20220401	20230331	37,8
19936-150F	695421	20220401	20230331	37,8
19936-150F	701689	20210401	20220331	37,8
19936-150F	695421	20210401	20220331	37,8
19936-150F	695421	20200401	20210331	37,8

As an item code has many iterations through time we do have several but only one is the last one so the Correct base price if related to that one and not the old ones.

I need to generate a “clean table” where I will filter and have only the row that has the highest Short Item && highest TP1 Effective date && TP1 Expiration date. This will then keep the row with the right TP1 Base Price.

JDE Description	Short Item	TP1 Effective Date	TP1 Expiration Date	TP1 Base Price
19936-150F	701689	20240401	20250331	68,99
19936-150F	701689	20230401	20240331	37,8
19936-150F	701689	20220401	20230331	37,8
19936-150F	701689	20210401	20220331	37,8
19936-150F	695421	20240401	20250331	37,8
19936-150F	695421	20230401	20240331	37,8
19936-150F	695421	20220401	20230331	37,8
19936-150F	695421	20210401	20220331	37,8
19936-150F	695421	20200401	20210331	37,8

So the Clean table will be as following and nothing else is shown.

JDE Description	Short Item	TP1 Effective Date	TP1 Expiration Date	TP1 Base Price
19936-150F	701689	20240401	20250331	68,99

Maybe it’s a simple one but I am knocking with my head and did not found yet a solution.

Thank you all

[Orlando_Tuga]

Thank you @DataInsights  it worked perfectly. Sorry for the late reply but only today could go back to the work.
Many thanks

[DataInsights · Accepted solution]

@Orlando_Tuga,

Try this Power Query solution. ItemCosts is the original table.

let
  Source = ItemCosts, 
  GroupRows = Table.Group(
    Source, 
    {"JDE Description"}, 
    {
      {"Max Short ItemTP1", each List.Max([Short ItemTP1]), type nullable number}, 
      {"Max Effective DateTP1", each List.Max([Effective DateTP1]), type nullable number}, 
      {"Max Expiration DateTP1", each List.Max([Expiration DateTP1]), type nullable number}, 
      {
        "All Rows", 
        each _, 
        type table [
          JDE Description = nullable text, 
          Short ItemTP1 = nullable number, 
          Effective DateTP1 = nullable number, 
          Expiration DateTP1 = nullable number, 
          Base Price = nullable number
        ]
      }
    }
  ), 
  ExpandRows = Table.ExpandTableColumn(
    GroupRows, 
    "All Rows", 
    {"Short ItemTP1", "Effective DateTP1", "Expiration DateTP1", "Base Price"}, 
    {"Short ItemTP1", "Effective DateTP1", "Expiration DateTP1", "Base Price"}
  ), 
  FilterRows = Table.SelectRows(
    ExpandRows, 
    each [Short ItemTP1]
      = [Max Short ItemTP1] and [Effective DateTP1]
      = [Max Effective DateTP1] and [Expiration DateTP1]
      = [Max Expiration DateTP1]
  ), 
  RemoveColumns = Table.RemoveColumns(
    FilterRows, 
    {"Max Short ItemTP1", "Max Effective DateTP1", "Max Expiration DateTP1"}
  )
in
  RemoveColumns