Create column from last value of another column which is a list of record

[briceem]

Hi

I have a table (obtained from a custom api API) containing different columns. To simplify let's say we have the following:

hostname	ip	history
host1	1.1.1.1	List
host2	2.2.2.2	List
host3	3.3.3.3	List

Each List in the column history is list of Record. The records have three columns "date", "version" and "type". All types are currently string (and I'm not opposed to cast those but for performance reason, and because I'm new to Power BI, I'd prefer to do it later in the process). 

So for example the column history for the host1 object is like this:

date	type	version
2021-03-11T16:36:28.628Z	type1	1
2022-04-01T04:53:10.805Z	typerandom	2
2023-04-01T02:53:10.805Z	type8	3

My objective is to get a new column in my first table containing the last value of the column "date" based on the last "version" (for this particular host of course) I can find in this List.

NB: If that can help, this list is already ordered, but I would prefer to use something like List.Max instead of List.Last in case the API changes.

So the result should produce

hostname	ip	history	last_date
host1	1.1.1.1	List	2023-04-01T02:53:10.805Z
host2	2.2.2.2	List	2022-08-01T05:51:12.704Z
host3	3.3.3.3	List	2023-07-01T10:04:10.105Z

I already successfully found a way to extract the max version and get the date for one line, but I don't know how to use this in the function that I found to add column :

Table.ExpandTableColumn

Here is my current code for this array

let
   Source = GetMyLines(),
   headers =
       let
           allHeaders = List.Combine(List.Transform(Source, Record.FieldNames)),
           uniqueHeaders = List.Distinct(allHeaders)
       in
           uniqueHeaders,
   myTable = Table.FromRecords(Source, headers, MissingField.UseNull),
   //test on one line
   history = myTable{0}[history],
   // get date for this line with the highest version
   maxvalue = Table.Max(Table.FromList(history,Record.FieldValues,{"date","type","version"}),"version")[date]
in
   maxvalue

Thanks in advance

[rubayatyasmin · Accepted solution]

Hi, @briceem 

Your objective is to find the most recent date for each version for each hostname. You need to iterate over each row of your initial table and apply your logic to each history.

try updating from this line

myTable = Table.FromRecords(Source, headers, MissingField.UseNull),

-->// Add column
WithLastDate = Table.AddColumn(myTable, "last_date", each let history = [history],
historyTable = Table.FromList(history,Record.FieldValues,{"date","type","version"}),
maxvalue = Table.Max(historyTable,"version")[date]
in maxvalue)
in
WithLastDate

[briceem]

Hi @rubayatyasmin,

You're amazing !
I will have to dig into some Table.AddColumn definition because I don't fully understand your syntax but your solution is working perfectly and I'm pretty sure I will be able to adapt it for other use cases.

Thanks again