Forum Discussion
Solved
How can I get the second row's data
Dear all, I want to create the table on the right from the table on the left below. "Max Date and second-max content (not null) in Name with status Delete." I created an All Rows co...
Ah, the old moving target question.
If you are going to return all of the status's, then you would use a different algorithm.
Merely
- Group by name
- Extract the Max Date from each sub-table for Date
- Sort each sub-table by date descending
- extract the first Status for Status
- If Status=Delete then extract the second line for Content
- else extract the first line for content
let //read in the data and set data types //be sure to change table name in next line to actual table name Source = Excel.CurrentWorkbook(){[Name="Table13"]}[Content], #"Changed Type" = Table.TransformColumnTypes(Source,{ {"Name", type text}, {"Status", type text}, {"Date", type date}, {"Content", type text}}), //Group by name #"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, { //extract status for max date //if status "Delete" return content for next line // else return status for the same line {"Status", each Table.Sort(_,{"Date",Order.Descending})[Status]{0}, type text}, {"Date", each List.Max([Date]), type date}, {"Content", (t)=> let sorted=Table.Sort(t,{"Date",Order.Descending}) in if sorted[Status]{0}="Delete" then sorted[Content]{1} else sorted[Content]{0},type text} }) in #"Grouped Rows"
You can easily do this with some custom aggregation in the Table.Group function.
You'll need to go into the Advanced Editor.
Please read the code comments.
If your actual data is significantly different from what you've posted, modifications will be required, but I believe the below will work with the kinds of variations I can think of.
If the data is exactly like what you post, the code can be simplified.
let
//read in the data and set data types
//be sure to change table name in next line to actual table name
Source = Excel.CurrentWorkbook(){[Name="Table13"]}[Content],
#"Changed Type" = Table.TransformColumnTypes(Source,{
{"Name", type text}, {"Status", type text}, {"Date", type date}, {"Content", type text}}),
//Group by name
#"Grouped Rows" = Table.Group(#"Changed Type", {"Name"}, {
//Extract the date for the Delete status
//in your examples, there is only one.
// If there are multiple may need to extract the Max date
// If there is no status=delete, then will return an error which we
// adjust for with the try..otherwise
{"Date", (t)=> try List.Max(Table.SelectRows(t,each [Status]="Delete")[Date]) otherwise null, type nullable date},
//return Delete (or null if delete not present in subtable)
{"Status", each if List.Contains([Status],"Delete") then "Delete" else null },
// Return [Content] from the second line of the date-sorted subtable
{"Content", (t)=>
let
maxDt = List.Max(Table.SelectRows(t,each [Status]="Delete")[Date]),
filterAndSort =
Table.Sort(
Table.SelectRows(t,each [Date] <= maxDt and [Status]<>"Delete"),
{"Date", Order.Descending})[Content]{0}
in filterAndSort}
}),
#"Filtered Rows" = Table.SelectRows(#"Grouped Rows", each ([Status] = "Delete"))
in
#"Filtered Rows"
