Forum Discussion
Max Date Values
I am trying to access the records for the most current date on a very large table.
Below is a example of the data I am working with. The "System Number" and "Threshold Type" can have multiple "Date From" I need the most current.
New to Power BI , any help would be greatly appreciated
| Date From | GG | GZ | Threshold Type | Target Avg Factor | Target Min Factor | System Description | System Number |
| 2020-10-01 | ABC | UNITY | LEVEL 1 | 0.258 | 0.241 | UNITY MAX | ABC-1234 |
| 2020-10-01 | ABC | UNITY | LEVEL 2 | 0.258 | 0.241 | UNITY MAX | ABC-1234 |
| 2020-10-01 | ABC | UNITY | LEVEL 3 | 0.299 | 0.288 | UNITY MAX | ABC-1234 |
| 2022-10-01 | ABC | UNITY | LEVEL 1 | 0.2862 | 0.2673 | UNITY MAX | ABC-1234 |
| 2022-10-01 | ABC | UNITY | LEVEL 3 | 0.3316 | 0.3194 | UNITY MAX | ABC-1234 |
| 2022-10-01 | ABC | UNITY | LEVEL 4 | 0.3316 | 0.3194 | UNITY MAX | ABC-1234 |
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
11 Replies
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 ronrsnfld
Elegant solution, thanks.
Open question, and something that I did not test yet, but in terms of query performance, would your approach be faster on large tables? Any insights would be appreciated.
It might be worthwhile testing various methods of solving his problem. I think that, on a large database, the efficiency of the sort would depend more on how large each subgroup is. A sort has to load the entire table and is recommended to be done as a "last step". I have assumed that a sort within a Table.Group aggregation will only load the "sub-table". But I don't know for sure. Worth testing.
Since all the dates are the same, when you have multiple entries on the same date, how do you determine which is most recent?
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)
