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 IDGrade ValueResult Date
1596501/10/2022
1596620/12/2022
1596615/02/2023
1645401/10/2022
1645319/12/2022
1645413/02/2023
1723201/10/2022
1723220/12/2022

 

My aim is to create an additional column finding out for each unique ID what the first grade value is using both the Result Date and Unique ID.

 

I am in Power BI Query - is anyone able to help with a custom column calculation formula?

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.

7 Replies

  • serpiva64's avatar
    serpiva64
    Solution Sage
    3 years ago

    Hi,

    I don't know if i have really understood your problem

    you can obtain this

    by group by

    then expand

    and adding a calculated column

    If this post is useful to help you to solve your issue, consider giving the post a thumbs up and accepting it as a solution!

     

     

     

     

    • Abi_W_981's avatar
      Abi_W_981
      Frequent Visitor
      3 years ago

      Hi, thank you for your solutions so far

       

      That could work however, I would need the First Result to show for all of the rows for that Unique ID.


      The reason being is that I will then add another measure/column to calculate the difference between the first grade value to the subsequent grade values to identify what progress has been made

      • serpiva64's avatar
        serpiva64
        Solution Sage
        3 years ago

        Hi,

        select the column and FillDown

         

  • ronrsnfld's avatar
    ronrsnfld
    Super User
    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.

  • 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