Latest value by key column

[TTS]

Tried to go through community cases and other help platforms but group by with fill down did not solve the issue.

The data I am receiving from source system:

- ID's can get values or nulls for certain columns by time

What is needed for reporting:

- Latest non-null value by ID and column - of course null if there is no value at any point in time.

[AlienSx · Accepted solution]

let
    Source = your_table,
    names = List.Buffer(List.Skip(Table.ColumnNames(Source), 2)),
    f = (tbl) => 
        [sorted = List.Skip(Table.ToColumns(Table.Sort(tbl, "Date")), 2),
        rec = Record.FromList(
            List.Transform(sorted, (x) => try List.Last(List.RemoveNulls(x)) otherwise null),
            names
        )][rec],
    group = Table.Group(Source, "ID", {"x", f}),
    xp = Table.ExpandRecordColumn(group, "x", names)
in
    xp

[dufoq3 · Accepted solution]

Hi @TTS, different approach here:

Enter column names you want to calculated MAX here:

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTIyMDLRNTAEIiAnLDGnNBWDjtWJVjLCpRSEQAqM0RVAEMIEZMuMDGBSRkhGGKGrQFYEZaBbhVNdLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Date = _t, Column1 = _t, Column2 = _t, Column3 = _t]),
    // You can probably delete this step.
    ReplaceBlankToNull = Table.TransformColumns(Source, {}, each if _ = "" then null else _),
    ChangedType = Table.TransformColumnTypes(ReplaceBlankToNull,{{"ID", Int64.Type}, {"Date", type date}}),
    __EnterColumnsHere__ = {"Column1", "Column2", "Column3"},
    GroupedRows = Table.Group(ChangedType, {"ID"}, {{"MaxValue", each 
        [ a = List.Transform(__EnterColumnsHere__, (x)=> List.Max(Table.Column(_, x))),
          b = Table.FromRows({ {[ID]{0}} & a }, {"ID"} & __EnterColumnsHere__)
        ][b], type table}}),
    CombinedMaxValue = Table.Combine(GroupedRows[MaxValue])
in
    CombinedMaxValue

[AlienSx · Accepted solution]

let
    Source = your_table,
    names = List.Buffer(List.Skip(Table.ColumnNames(Source), 2)),
    f = (tbl) => 
        [sorted = List.Skip(Table.ToColumns(Table.Sort(tbl, "Date")), 2),
        rec = Record.FromList(
            List.Transform(sorted, (x) => try List.Last(List.RemoveNulls(x)) otherwise null),
            names
        )][rec],
    group = Table.Group(Source, "ID", {"x", f}),
    xp = Table.ExpandRecordColumn(group, "x", names)
in
    xp

[TTS]

Thanks for this input!
Actually I got it working now!!! Great thanks!

[TTS]

Managed to do almost the same like this:

But same "issue" still to be solved; I just get the lates records; and in the case that the newest has null for certain column and there is a value in the history I would like to get that to same record.
By the way - I don't need the date for reporting purposes at all...so that's not needed for the end result.

[BA_Pete]

Ok, I see. Sorry, didn't read the requirements properly first time.

Can you provide a copyable example of your data in its 'before' state please?

Pete

[TTS]

Thanks! But still some issues... 🙂
I did as You mentioned; and everything seems to go without errors.

But how to get from here a single record that has values:
Column1 = New Contract. Valid from 1.5.2024
Column2 = 30 days net from invoice date
Column3 = Only EXW in use for 2024

[BA_Pete]

Hi @TTS ,

If you can provide a copyable example of your data I can provide you the exact code for your scenario but, in the absence of that, the process you want to follow will be as follows:

-1- Group By [ID]. Use the 'All Rows' operator for the aggregate column. I'll assume you've called the aggregate column [data].

-2- Add a new custom column and use the following code for the calculation:

Table.Max([data], "Date")

-3- Expand the resulting nested record column reinstating whichever columns you need back to the table.

Pete