Forum Discussion
Max Date Values
OK, all you need to do is Group By System Number and Threshold Type, then filter each sub table in the Table.Group aggregation:
let //change next line to reflect your actual data source Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{ {"Date From", type date}, {"GG", type text}, {"GZ", type text}, {"Threshold Type", type text}, {"Target Avg Factor", type number}, {"Target Min Factor", type number}, {"System Description", type text}, {"System Number", type text}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Threshold Type", "System Number"}, { {"Latest", (t)=>Table.SelectRows(t, each [Date From] = List.Max(t[Date From])), type table [Date From=nullable date, GG=nullable text, GZ=nullable text, Threshold Type=nullable text, Target Avg Factor=nullable number, Target Min Factor=nullable number, System Description=nullable text, System Number=nullable text]}}), #"Expanded Latest" = Table.ExpandTableColumn(#"Grouped Rows", "Latest", {"Date From", "GG", "GZ", "Target Avg Factor", "Target Min Factor", "System Description"}) in #"Expanded Latest"Results from your data
Advocate IIHi,
to rephrase the question asked by ronrsnfld :
What is the business key of each record?
In other words, which columns together uniquely define the record?
Can you be more specific about which record you expect to keep in the sample data that you have provided?
The fields to uniquely identify a record are: (System Number, Threshold Type, Date From).
I need to capture the ones with the most current Date From.
- nickvanmaele
In your sample data, there are still two different records for (System Number = ABC-1234) and (Threshold Type = Level 1) and (Date = 2022-10-01).
Please explicitly define which records from your sample data you wish to see returned by the query. Do you want to keep both those records, or only one of them? If only one, then which one?
So far, I feel that we lack some information to understand what you are looking for. Please define your expected solution well - e.g., "in this sample table with X rows, I expect rows A, C, and F to be returned." (where X, A, C, and F are numbers)
Date From GG GZ Threshold Type Target Avg Factor Target Min Factor System Description System Number 10/1/2020 ABC UNITY LEVEL 1 0.258 0.241 UNITY MAX ABC-1234 10/1/2020 ABC UNITY LEVEL 2 0.258 0.241 UNITY MAX ABC-1234 10/1/2020 ABC UNITY LEVEL 3 0.299 0.288 UNITY MAX ABC-1234 10/1/2022 ABC UNITY LEVEL 1 0.2862 0.2673 UNITY MAX ABC-1234 10/1/2022 ABC UNITY LEVEL 3 0.3316 0.3194 UNITY MAX ABC-1234 10/1/2022 ABC UNITY LEVEL 4 0.3316 0.3194 UNITY MAX ABC-1234 The 3 fields in yellow create a unique record Based on these unique records I need to keep the System Number and Threshold Type with the most current date. In the example attached I would need to keep the ones in green. Notice not all Threshold Types have a record with 10/1/2022 Date From. In these cases the most current date is 10/1/2020 (see above in blue) I need to see these records
Does this help?
