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.
Zdenec, if Power Query is an option that could be
let
Source = Excel.CurrentWorkbook(){[Name="T_Data"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(
Source,
{{"Test date", type date}}
),
#"Fake date" = Table.ReplaceValue(
#"Changed Type",
null,#date(9000, 12, 31),
Replacer.ReplaceValue,{"Test date"}
),
#"Sorted Rows" = Table.Sort(
#"Fake date",
{{"Name", Order.Ascending}, {"Test date", Order.Descending}}
),
#"Fix in memory" = Table.AddIndexColumn(
#"Sorted Rows",
"Index", 0, 1, Int64.Type
),
#"Keep distinct names" = Table.Distinct(#"Fix in memory", {"Name"}),
#"Remove fake date" = Table.ReplaceValue(
#"Keep distinct names",
#date(9000, 12, 31),
null,Replacer.ReplaceValue,{"Test date"}
),
#"Remove Index" = Table.RemoveColumns(#"Remove fake date",{"Index"})
in
#"Remove Index"The idea is to temporary replace no date for not tested by far away date.
- Hello Sergei
Sort by name ascending, by date descending and remove duplicates - so easy is it!
The purpose of the Index column is to help Table.Distinct function to keep the first row? (I guess, because if I delete the index column from the process, the Table.Distinct function keeps the last row of each name ...). So how does the Table.Distinct work with the Index column?
Thank You
ZdenekThe 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.
HelloSergeiBaklan
The power query works OK.
I guess, it could be also possible with formulas similar to the Re: Count values with max date in filtered excel table topic, but I will go this way.
Thank You very much!
Zdenek
