How to return the most recent value of a second column based on a first column

[gleiba]

Hello,

I am new to Power BI and have a slightly niche need that I cannot find an answer to online.

Specifically, I am looking to take a datatable of employee skills (where each time a specific skill is upadted, a new entry is recorded including an effective date) and return a new datatable of just the most recent update for each skill for each person.

To illustrate my point, I would like to take the following dataset below.

And then output this datatable.

All similar examples online seem to look at just counting one column based off the effective date. Any help would be much appreciated!

[yaak · Accepted solution]

So, one of the fairly easy methods would be to load the original dataset to a Connection Only Query (I called it Datasource). Then create another query which references the first one, groups by Employee ID, Skill and Date, so you get the latest assessment date for each employee and each skill. Then merge to the original query and bring the lever score based on Employee ID, Skill and Date.

Output query:

let
    Source = Datasource,
    #"Grouped Rows" = Table.Group(Source, {"Employee ID", "Skill"}, {{"Latest Date", each List.Max([Date]), type nullable date}}),
    #"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"Employee ID", "Skill", "Latest Date"}, Datasource, {"Employee ID", "Skill", "Date"}, "Datasource", JoinKind.LeftOuter),
    #"Expanded Datasource" = Table.ExpandTableColumn(#"Merged Queries", "Datasource", {"Level"}, {"Level"})
in
    #"Expanded Datasource"

Output:

Note: there could be a potential to do it much easier which the grouping and adding Level as aggregation using max but this would work only if the latest Level is higher or equal to the previous assessment. If this can't be guaranteed then you may get wrong results.

[gleiba]

Thanks for your help on this. I think I have managed to get your suggestion working!

It at least seems to be providing me with the correct output!