Power Query: Latest date based on a condition

[DimasArend]

Hi,

I am struggling to solve a problem in Power Query. Could someone help me?

My table look like this:

DATE	XXX
05/01/2021	102
04/01/2021	115
04/01/2021	102
03/01/2021	102
01/01/2021	115

And i need that return this: The lastest date of xxx if they are in a sequence

DATE	XXX
05/01/2021	102
04/01/2021	115
01/01/2021	115

Thanks in advance.

[Payeras_BI · Accepted solution]

Hi @DimasArend,

This is what I understood, let's see if I got it right.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDVNzDUNzIwMlTSUTI0MFKK1QEKmiALGppiEYSpNMYmaIiuPRYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [DATE = _t, XXX = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"DATE", type date}, {"XXX", Int64.Type}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"XXX", Order.Ascending}, {"DATE", Order.Ascending}}),
    AddedIndex = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(AddedIndex, "Custom", each let 
     Check1 = try Duration.Days(AddedIndex[DATE]{[Index]} - AddedIndex[DATE]{[Index]-1}) otherwise 0,
     Check2 = try AddedIndex[XXX]{[Index]} = AddedIndex[XXX]{[Index]-1} otherwise false
     in 
     if Check1 = 1 and Check2 = true then null else [DATE], type date),
    #"Filled Down" = Table.FillDown(#"Added Custom",{"Custom"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"XXX", "Custom"}, {{"DATE", each List.Max([DATE]), type nullable date}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"DATE", "XXX"}),
    #"Sorted Rows1" = Table.Sort(#"Removed Other Columns",{{"DATE", Order.Descending}})
in
    #"Sorted Rows1"

[KNP]

I don't fully understand what you're trying to get to.

Why is 115 in your result twice and 102 not?