Forum Discussion
Helper IIHow can I get the second row's data
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"
Super UserYou 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"
umekuroDear ronrsnfld,
Thank you very much. It's very helpful.
Please let me know if you can add more if you can make it this far.
If I want to add not only DELETE but also other statuses to the list,
How do I change the description in the advanced editor?
In that case, I want to list the maximum Status and Content of the Date.Thanks in advance for your help!
- ronrsnfld
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"- umekuro
Dear ronrsnfld,
I was able to execute it perfectly.
It was very helpful for me.Thank you very much!
BR
