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.
Hello PeterBartholomew1
I love Power Query, but I see, it is time to learn again. We have M365 business and I see all the formulas. I got a new task to create a summary for my manager, so this time I can try to use LET instead of Power Query.
Can You please send me an attachment? When I copy the formula, I get errors
But the formula returns a result, only different:
| Alan |
| positive |
| 0 |
Thank you
Zdenek
Zdenek, here is the file with PeterBartholomew1 formula, I only added substituting of blank date on empty string.
Formula shall be converted into your locale automatically.
After opening the attachment, I see my mistake. I copied the original formula from the @Peter Bartholomew post and translated it in an online web translator. But the web transformed "\" to ";" in the INDEX Formula.
So I learned by te way, that ";" returns columns array and "\" returns rows array.
The LET works OK now, I am going to play with it.
Thank You both!
Zdenek