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

     

latimeria's avatar
latimeria
Solution Specialist
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:: 

 

  • Gabe_V's avatar
    Gabe_V
    Helper I
    3 years ago

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