Forum Discussion
Get record with latest date by criteria from excel table and handle blanks
The purpose of Index column is to fix the table in memory. Alternatively you may wrap it by Table.Buffer(). The only small difference you may manipulate with Index from UI only, Table.Buffer() shall be added manually in editor. But result is the same.
Without fixing in memory Table.Distinct could give wrong result (could not) due to lazy evaluation performed by Power Query.
If you wanted a formula it is a question of having the right version of Excel.
= UNIQUE(T_Data[Name])
= XLOOKUP(distinct#,T_Data[Name],T_Data[Test Result],,,-1)
= XLOOKUP(distinct#,T_Data[Name],T_Data[Test date],,,-1)gives a solution in 3 parts. You could also return the entire table with one formula
= LET(
distinct, UNIQUE(T_Data[Name]),
r, XMATCH(distinct, T_Data[Name],,-1),
INDEX(T_Data, r, {1,2,3}) )Sorting can be introduced but it is not needed in this case.
HelloPeterBartholomew1
I am learning the LET formular on this example. I roughly understand the logic.
The target of this post is to find not the LAST row of each name, but the LATEST row of each name (means the row with max test date of each person). It doesn't need to be on the bottom of the table.
Would You implement the MAXIFS formula into the LET?
(a helper column can contain 31.12.2099 for the "not tested" records)
Thank You
Zdenek
Split it on parts to make easier in maintenance
= LET( noDate, 99999, namesWithNoDate, COUNTIFS(T_Data[Name],T_Data[Name], T_Data[Test date],""), namesActualMaxDates, MAXIFS(T_Data[Test date],T_Data[Name],T_Data[Name]), datesWithNoDate, IF(T_Data[Test date]="",noDate,T_Data[Test date]), addFakeDate, IF( namesWithNoDate, noDate, namesActualMaxDates ), conditionMaxDate, addFakeDate=datesWithNoDate, filterOnMaxDate, FILTER(T_Data,conditionMaxDate ), IF(filterOnMaxDate=0,"",filterOnMaxDate) )As variant
= LET( p, IF( COUNTIFS(T_Data[Name],T_Data[Name], T_Data[Test date],""), 99999, MAXIFS(T_Data[Test date],T_Data[Name],T_Data[Name]) )=IF(T_Data[Test date]="",99999,T_Data[Test date]), r, FILTER(T_Data,p ), IF(r=0,"",r))