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
Hi,
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.
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?
hi Gryph87
Thanks for providing more details. Based on that, here is my proposed solution.
First, this is an overview of the input data and the result I have obtained:
overview of input data and solutionSolution approach
I based myself on the principle of an inner join. First I created a table with system number, threshold level, and the max(Date From) for that combination. This table, which I call 'MostRecentKeys', can then be joined with an inner join on the original table. The result of this join will be that only the records from both tables are kept where the business keys are identical. This is exactly what you want.
In more detail:
1. I created the table and used "from table/range" in Excel to obtain a Power Query
2. I used the "Group by" function with aggregations on "System number" and "Threshold Type", with a max on column "Date From" to obtain a table with 3 columns. Using the Advanced Editor or the rename functionality, I call this step 'MostRecentKeys'
3. Then I used the self-join functionality to join the query on itself. In other words, I pressed the 'Merge Queries' button and joined the query with itself with an inner join type, using the three business columns at the same time in both sides of the join
4. In the code obtained in step 3, I used the Advanced Editor to manually change the joined queries to 'MostRecentKeys' and '#"Changed Type" respectively, and I ensured that the three listed keys of each had correct column names.
5. Then I expanded the merged query column to reveal all necessary data
6. To finish off, I reordered the columns
Power Query Code
The Power Query code is as follows:
let
Source = Excel.CurrentWorkbook(){[Name="tblInputData"]}[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}}),
MostRecentKeys = Table.Group(#"Changed Type", {"System Number", "Threshold Type"}, {{"MaxDate", each List.Max([Date From]), type nullable date}}),
#"Merged Queries" = Table.NestedJoin(MostRecentKeys, {"System Number", "Threshold Type", "MaxDate"}, #"Changed Type", {"System Number", "Threshold Type", "Date From"}, "AdditionalData", JoinKind.Inner),
#"Expanded AdditionalData" = Table.ExpandTableColumn(#"Merged Queries", "AdditionalData", {"GG", "GZ", "Target Avg Factor", "Target Min Factor", "System Description"}, {"GG", "GZ", "Target Avg Factor", "Target Min Factor", "System Description"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded AdditionalData",{"System Number", "System Description", "Threshold Type", "MaxDate", "GG", "GZ", "Target Avg Factor", "Target Min Factor"})
in
#"Reordered Columns"If you like this approach, please mark this post as solution.