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

     

  • Gabe_V's avatar
    Gabe_V
    Helper I
    3 years ago

    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's avatar
      Gabe_V
      Helper I
      3 years ago

      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.