Forum Discussion

Abi_W_981's avatar
Abi_W_981
Frequent Visitor
3 years ago
Solved

Finding First Result using Date & Unique ID

Hi, I am trying to find the first Grade value using Result Date and a Unique ID (Example table below)   Unique ID Grade Value Result Date 1596 5 01/10/2022 1596 6 20/12/2022 1596 ...
Need Help
  • ronrsnfld's avatar
    ronrsnfld
    3 years ago

    If your data is guaranteed to be sorted by Unique ID and Date (Ascending) as you show in your example, then:

    • Group by Unique ID
    • Extract the first Grade Value
    • Re-expand the table

     

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY7BDcAgDAN3yRspiUOomAWx/xptilSKlIdfZ588Bqn3RoX8iSirMASgWT4SgbAiJeosL7FFWg1TTWyLWGz6adsbtdN2IfpIbJv8v80b", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unique ID" = _t, #"Grade Value" = _t, #"Result Date" = _t]),
    
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Unique ID", Int64.Type}, {"Grade Value", Int64.Type}, {"Result Date", type date}}, "en-GB"),

//Add index value to force Table.Group to maintain sort order
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 0, 1, Int64.Type),
    
    #"Grouped Rows" = Table.Group(#"Added Index", {"Unique ID"}, {
        {"all", each _, type table [Unique ID=nullable number, Grade Value=nullable number, Result Date=nullable date, Index=number]},
        {"First Grade Value", each [Grade Value]{0}, Int64.Type}}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Grade Value", "Result Date"})
in
    #"Expanded all"

     

     

     

    If your data is NOT sorted, then you will need to add a sort step.

  • serpiva64's avatar
    serpiva64
    3 years ago

    Hi,

    select the column and FillDown

     

dufoq3's avatar
dufoq3
Community Champion
2 years ago

Hi Abi_W_981, another one:

 

Result:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bY7BDcAgDAN3yRspiUOomAWx/xptilSKlIdfZ588Bqn3RoX8iSirMASgWT4SgbAiJeosL7FFWg1TTWyLWGz6adsbtdN2IfpIbJv8v80b", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Unique ID" = _t, #"Grade Value" = _t, #"Result Date" = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Result Date", type date}}, "sk-SK"),
    GroupedRows = Table.Group(ChangedType, {"Unique ID"}, {{"All", each _}, {"Min Grade", each Table.Min(_, "Result Date")[Grade Value], type table}}),
    Ad_MinGrade = Table.AddColumn(GroupedRows, "t", each Table.AddColumn([All], "Min Grade", (x)=> Number.From([Min Grade]), Int64.Type)),
    CombinedT = Table.Combine(Ad_MinGrade[t])
in
    CombinedT