Forum Discussion

Gabe_V's avatar
Gabe_V
Helper I
3 years ago
Solved

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. Ess...
Need Help
  • latimeria's avatar
    latimeria
    3 years ago

    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:: 

     

slorin's avatar
slorin
Super User
3 years ago

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's avatar
latimeria
Solution Specialist
3 years ago

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