Forum Discussion
Copy down data based on max of another column
Hello!
I have spent almost a day and a half trying to get a column in my table to copy to all cells in that column based upon if they are associated with the most recent date in another column.
Essentially I have a table that looks like this:
| Dataset Date | Project ID | Priority | Stack Rank |
| 11/1/2022 | ABC123 | 3a | 51 |
| 10/1/2022 | ABC123 | 3b | 48 |
| 12/1/2022 | ABC123 | 2 | 37 |
What I would like is to look at the Dataset Date, find the most recent date (in this case 12/1/2022), and then fill the associated values in the Priority and Stack Rank columns (2 and 37 respectively in this case), to all rows in those columns, so the end result would look like:
| Dataset Date | Project ID | Priority | Stack Rank |
| 11/1/2022 | ABC123 | 2 | 37 |
| 10/1/2022 | ABC123 | 2 | 37 |
| 12/1/2022 | ABC123 | 2 | 37 |
I've had no luck with anything I've tried so far, and all the forums don't seem to cover this particular scenario.
Does anyone have any suggestions, or can at least point me in the right direction?
Cheers,
Gabe_V
Hi Gabe_V ,
You can try this code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ3NNQ3MjAyUtJRcnRyNjQyBjKME4GEqaFSrA5YgQGmgiQgYWIBU2CEoQDEMTaHySPbYGJqht8GqAKYDbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Dataset = _t, #"Project ID" = _t, #"ID priority" = _t, #"Stack Rank" = _t]), #"Grouped Rows" = Table.Group(Source, {"Project ID"}, { {"AllRows", each let // get max value by group Priority = Table.SelectRows(_, (r)=> r[Dataset] = List.Max(_[Dataset]) )[ID priority]{0}, Stack = Table.SelectRows(_, (r)=> r[Dataset] = List.Max(_[Dataset]) )[Stack Rank]{0}, // replaced values in the Group ReplacedValue = Table.TransformColumns( _, { { "ID priority", (r)=> Priority }, { "Stack Rank", (r)=> Stack } } ) in ReplacedValue } } ), Expanded = Table.Combine(#"Grouped Rows"[AllRows]) in ExpandedHere is the result::
6 Replies
Hi Gabe_V ,
You can try this code:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ3NNQ3MjAyUtJRcnRyNjQyBjKME4GEqaFSrA5YgQGmgiQgYWIBU2CEoQDEMTaHySPbYGJqht8GqAKYDbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Dataset = _t, #"Project ID" = _t, #"ID priority" = _t, #"Stack Rank" = _t]), #"Grouped Rows" = Table.Group(Source, {"Project ID"}, { {"AllRows", each let // get max value by group Priority = Table.SelectRows(_, (r)=> r[Dataset] = List.Max(_[Dataset]) )[ID priority]{0}, Stack = Table.SelectRows(_, (r)=> r[Dataset] = List.Max(_[Dataset]) )[Stack Rank]{0}, // replaced values in the Group ReplacedValue = Table.TransformColumns( _, { { "ID priority", (r)=> Priority }, { "Stack Rank", (r)=> Stack } } ) in ReplacedValue } } ), Expanded = Table.Combine(#"Grouped Rows"[AllRows]) in ExpandedHere is the result::
Thank you very much latimeria! I only had to do a tiny tweak (column name) and it works great, and I am very happy that it groups by Project ID as well. I had actually forgot to add that piece into the request, but I see by the nature of the grouping that it accommodates that. Very helpful!
I hope this helps others down the road. 😀
Hi,
I find that there is a simpler function to answer your question
Group by ID, use Table.Max and a list
Then expand the record and the list
let
Source = YourSource,
Group = Table.Group(
Source,
{"Project ID"},
{{"Max", each Table.Max(_,"Dataset Date"), type record},
{"Dataset Date", each [Dataset Date], type list}}
),
Expand_Record = Table.ExpandRecordColumn(Group, "Max", {"ID Priority", "Stack Rank"}, {"ID Priority", "Stack Rank"}),
Expand_List = Table.ExpandListColumn(Expand_Record, "Dataset Date")
in
Expand_ListStéphane
Hi slorin ,
You are right, is shorter tu use table.max.
But i prefer to use table.transform with table.combine, it is more robust in my mind as you don't need to specify fields names to expand.
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ3NNQ3MjAyUtJRcnRyNjQyBjKME4GEqaFSrA5YgQGmgiQgYWIBU2CEoQDEMTaHySPbYGJqht8GqAKYDbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Dataset Date" = _t, #"Project ID" = _t, #"ID priority" = _t, #"Stack Rank" = _t]), #"Grouped Rows" = Table.Group(Source, {"Project ID"}, { {"AllRows", each let // get max value by group MaxRecordDate = Table.Max(_,"Dataset Date"), // replaced values in the Group ReplacedValue = Table.TransformColumns( _, { { "ID priority", (r)=> MaxRecordDate[ID priority] }, { "Stack Rank", (r)=> MaxRecordDate[Stack Rank] } } ) in ReplacedValue } } ), Expanded = Table.Combine(#"Grouped Rows"[AllRows]) in ExpandedHi slorin I'll give that a shot as well. Shorter is always nicer, especially considering this is part of a multi-step process whereby I am appending multiple historic spreadsheets. With each historic spreadsheet I add, I have to first run an ETL query on the new spreadsheet, append it to the main query, sort rows, remove duplicates from the previous load, and then do this grouping and expanding before repeating the process all over again. Is it sustainable? No, but I suppose that's a problem for another day. 🙂
