How can I compare values fromdifferent records

[Ketil_F]

I have a table with 3 columns that contains

- name of a player

- date for a practice session

- a score for this practise session

I want to add a forth column to indicate which dates a personal best was achived. Example:

Name	Date	Score	New best score
A	Dec 12	90	X
A	Dec 13	85
A	Dec 15	88
A	Dec 26	94	X
A	Dec 27	83

I know how to compare a value with a value in the previous record using "Table.AddIndexColumn", but that will not solve this problem.
Any suggestions?

[dufoq3 · Accepted solution]

Hi @Ketil_F, check this:

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXJJTVYwNAIyFCwNlGJ1kASNQYIWpqiCpmBBCxRBIzOwdhNUQXOwSmOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Date = _t, Score = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Score", type number}}),
    GroupedRows = Table.Group(ChangedType, {"Name"}, {{"T", each 
        [ a = List.Buffer([Score]),
          b = List.Generate(
                    ()=> [ x = 0, y = a{x}, max = y, z = "X" ],
                    each [x] < List.Count(a),
                    each [ x = [x]+1, y = a{x}, max = List.Max({[max], y}), z = if y > [max] then "X" else null ],
                    each [z] ),
          c = Table.FromColumns(Table.ToColumns(_) & {b}, Value.Type(Table.FirstN(_, 0) & #table(type table[New best score=text], {})))
        ][c], type table}}),
    CombinedT = Table.Combine(GroupedRows[T])
in
    CombinedT

[Omid_Motamedise]

You can use this formula to solve your problem in the shortest way

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXJJTVYwNAIyFCwNlGJ1kASNQYIWpqiCpmBBCxRBIzOwdhNUQXOwSmOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Date = _t, Score = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Score", type number}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each if [Score]>(List.Max(Table.FirstN( #"Changed Type", (x)=>x<>_)[Score])??0) then "X" else null )
in
    #"Added Custom"

[Ketil_F]

Thanks a lot @Omid_Motamedise ☺️ I have to say I am very positive suprised with the support I got on my question. I am an "old timer" with experience in COBOL and other mainframe languages, but now I only do "visual basic" in Excel. Power query language is verfy different and no way I would be able to solve this myself.

[Ketil_F]

Thanks @Omid_Motamedise , I am a "old timer" with experience in main frame programming, but have to admit i am not always able to follow the logic in power query language. Really glad to get help with this 🙂 

[Ketil_F]

. @dufoq3 and @p45cal , both your queries worked great. Thanks a lot. 

I decided to proceeed with code from @dufoq3 's code because that was super quick ☺️ it executed in no time with 30.000 records. I manged to get the same result myself making a really inefficient code with multiple steps, but that was now more than 12 minutes to complete and fragile as data size get bigger 😅. Less that 10 sec's now.

@lbendlin , will provide better test data next time. Thanks for a good advice.

[dufoq3]

You're welcome.

[p45cal · Accepted solution]

Here's one without an index if the date column contains proper dates:

 Table.AddColumn(ChangedType, "New Best Score", each if [Score]>List.Max(Table.SelectRows(ChangedType, (x)=>x[Date] < [Date] and x[Name]=[Name])[Score] &{0}) then "X" else "")

where ChangedType is the previous step.

For pasting into Advanced Editor, includes extended source data with an extra name, conversion of textual dates by adding year data and converting to proper dates:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXJJTVYwNAIyDi2wNFCK1UESNQaLWpiiippCRC1QRI3MICaYoIqaQ9Qag0WTsNqWhGybpRGqGIpdSVjtSsK0KxYA", BinaryEncoding.Base64), Compression.Deflate)),{"Name","Date","Score"}),
    AddedPrefix = Table.TransformColumns(Source, {{"Date", each "2024 " & _, type text}}),
    ChangedType = Table.TransformColumnTypes(AddedPrefix,{{"Date", type date}, {"Score", Int64.Type}}),
    AddedCustom = Table.AddColumn(ChangedType, "New Best Score", each if [Score]>List.Max(Table.SelectRows(ChangedType, (x)=>x[Date] < [Date] and x[Name]=[Name])[Score] &{0}) then "X" else "")
in
    AddedCustom

The order of the source data doesn't matter.

It would probably better if there was a Table.Buffer in the query too.

[dufoq3 · Accepted solution]

Hi @Ketil_F, check this:

Output

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUXJJTVYwNAIyFCwNlGJ1kASNQYIWpqiCpmBBCxRBIzOwdhNUQXOwSmOl2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, Date = _t, Score = _t]),
    ChangedType = Table.TransformColumnTypes(Source,{{"Score", type number}}),
    GroupedRows = Table.Group(ChangedType, {"Name"}, {{"T", each 
        [ a = List.Buffer([Score]),
          b = List.Generate(
                    ()=> [ x = 0, y = a{x}, max = y, z = "X" ],
                    each [x] < List.Count(a),
                    each [ x = [x]+1, y = a{x}, max = List.Max({[max], y}), z = if y > [max] then "X" else null ],
                    each [z] ),
          c = Table.FromColumns(Table.ToColumns(_) & {b}, Value.Type(Table.FirstN(_, 0) & #table(type table[New best score=text], {})))
        ][c], type table}}),
    CombinedT = Table.Combine(GroupedRows[T])
in
    CombinedT

[lbendlin]

Yes, the index is still required.

Please provide sample data that completely covers your scenario.  Multiple Names, better date format.