Forum Discussion
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 column in PowerQuery's GroupBy and added a Table.Max expression in the custom column,
but I only get the row for the largest day and the content is null.
Using MaxN, I get two rows.MaxN will give me 2 rows.
I guess I need to apply more GroupBy, but I don't want to make it too complicated.
How can I get the right side of the table?
Thank you in advance.
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"
13 Replies
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"Oops. Thanks. I'll edit my response
Dear 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!
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"
Hi umekuro !
Suppose that yout left table is named "Tabella", you have to create two other Tables, like this:
let
Origine = Tabella,
#"Filtrate righe" = Table.SelectRows(Origine, each ([Content] <> "null")),
#"Raggruppate righe" = Table.Group(#"Filtrate righe", {"Name"}, {{"Date", each List.Max([Date]), type nullable date}, {"Content", each List.Max([Content]), type nullable text}})
in
#"Raggruppate righe"and the second one, which is your expected result (right table):
let
Origine = Tabella,
#"Filtrate righe" = Table.SelectRows(Origine, each ([Status] = "Delete")),
#"Raggruppate righe" = Table.Group(#"Filtrate righe", {"Name", "Status"}, {{"Date", each List.Max([Date]), type nullable date}}),
#"Merge di query eseguito" = Table.NestedJoin(#"Raggruppate righe", {"Name"}, #"Tabella (3)", {"Name"}, "Tabella (3)", JoinKind.LeftOuter),
#"Tabella Tabella (3) espansa" = Table.ExpandTableColumn(#"Merge di query eseguito", "Tabella (3)", {"Content"}, {"Tabella (3).Content"})
in
#"Tabella Tabella (3) espansa"Try and tell me!
B.
Dear bf,
Thank you for your help!
I tried it. It worked.
But it would be nice if it could be done in one table,is that not possible with this kind of data source?
BR
Anonymous it doesn't work.
B.
Can you not create a table that contains the status you are interested and perform a Right Anti join to give you records on the left table that exists on the right table.
