Forum Discussion
Latest value by key column
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.
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 xpHi 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
7 Replies
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
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 xpThanks for this input!
Actually I got it working now!!! Great thanks!
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 CombinedMaxValueThanks! 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 2024Managed 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.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
