Copy down data based on max of another column

[Gabe_V]

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

[slorin]

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_List

 Stéphane

[latimeria]

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
    Expanded

[Gabe_V]

Hi @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. 🙂

[Gabe_V]

@slorin I just tried out your code, and while short, which is great, it also appears to produce an ID Priority column with all null values.

[latimeria · Accepted solution]

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
    Expanded

Here is the result:: 

[Gabe_V]

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. 😀