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.
Please accept my apologies for causing confusion. I had implemented a version of the formula that would accommodate entries out of date sequence but, wrongly, came to the conclusion that the situation was not likely to occur. Had I realised you were in a position to try the formula, including the workbook with its additional formula would have been helpful.
= LET(
distinct, UNIQUE(T_Data[Name]),
sorted, SORT(T_Data, 3),
r, XMATCH(distinct, INDEX(sorted, ,1),,-1),
INDEX(sorted, r, {1,2,3}) )
The n/a is achieved by number formatting and the blank test date will always appear as the last date.
Sorry to butt in (I don't mean to hijack the thread), but I have a clarification request:
I noticed that the return value of the LET function isINDEX(sorted, r, {1,2,3})
I imagine that the{1,2,3}is intended as an array covering all 3 columns in "sorted" as the [column_num] parameter of INDEX. Is there a way to achieve the same thing when the number of columns in the array is not known in advance or may dynamically change?
You could use SEQUENCE to generate the index array.
You might also need to account for the Date column moving by changing the sort.
= LET( distinct, UNIQUE(T_Data[Name]), sorted, SORTBY(T_Data, T_Data[Test date]), r, XMATCH(distinct, INDEX(sorted, ,1),,-1), c, SEQUENCE(1,COLUMNS(T_Data)), INDEX(sorted, r, c))Yes, I indeed forgot to account for the Date column moving about! Thanks!
Hello both SergeiBaklan and PeterBartholomew1
Thank You for the learning material, I could understand the LET function on my example data.
The question from the subject is now answered.
Zdenek Moravec
Glad it helped. Solution suggested by PeterBartholomew1 is definitely the best, the rest could be used mainly for education purposes.
