Get the latest non null value of a serie of column based on another column

[Anonymous]

I have a table that looks like this:

Timestamp	Col1	Col2	Col3
T0	1	2	3
T1	null	3	3
T2	null	null	5

I want to get the latest (based on timestamp) non null value of each column. To at the end get the following one row table:

Col1	Col2	Col3
1	3	5

How can I do that only with M Formulas in Power Query?

[Anonymous · Accepted solution]

Hi @Anonymous 

Sorry, updated my original post - have not read your one correct from the first time.

This is the updated code, now doing what you need:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjFQ0lEyBGIjIDZWitUBCoG4eaU5OWARuKgRQhRKmSrFxgIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Timestamp = _t, Col1 = _t, Col2 = _t, Col3 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Timestamp", type text}, {"Col1", Int64.Type}, {"Col2", Int64.Type}, {"Col3", Int64.Type}}),
   
   Output = List.Accumulate(Table.ToColumns(#"Changed Type"), {}, (s, a) => s & {List.Last(List.RemoveNulls(a))}),
    #"Converted to Table" = Table.FromColumns(List.Zip({Output}), Value.Type(#"Changed Type"))

in #"Converted to Table"

Kind regards,

JB