Filter table by / based on multiple entries

[Mathze03]

Hi all,

I have a rather complex (from my point of view) topic:

We have a table which holds the metrics for a user self assessment. The user can do a self-assessment for various competences. The proficiency level for these competences range from foundational, advance to master. 

We now want to know how many proficiency levels have been selected for each competence.

The problems: A user with his/her unique user ID can appear several times. + User sometimes click through the level within seconds (maybe he/she was unsure util the decision for the final level) which leads to multiple entires with different time stamps (only differences in seconds).

Here is a summarized screenshot from the table for one single user:

The table holds several columns. The most important from my point of view are:

• Date (UTC) - required to pick the latest user selection
• CompetencyTitle -  target column for which later the number of selected levels needs to be shown (I will later create a new column which shows the corresponding English name)
• SelectedLevelTitle - Column which shows the selected level (I will later create a new column which shows the corresponding English name)
• UniquieUserID - I'm not sure yet how to use this column but I guess it is needed to verify how many times this user has evaluated him/herself for the respective competence.

The table above holds 15 lines. The user did a self assessment for 10 different competencies, for which he/she changed the level multiple times for 4 of them. Therefore we have 15 lines. 

Here is the file with entries for two different users: https://docs.google.com/spreadsheets/d/1NpnJKbXIQqzCZ1opMxlzxZn6nf-VCodn/edit?usp=sharing&ouid=10272... 

What do I need to do to group / transform the table in a way that Power BI in the end shows a table with only 10 line items and only picks the latest selection (based on the info provided in the column Date (UTC) for each user for the respective comptence?

Thank you very much for your help in advance.

Mathias

[Mathze03]

Hi Ibendlin,

even though it's been a while since I've posted my question: Thank you very much for getting back and for providing help!!!

Kind reagrds,

Mathias

[lbendlin]

This is something you can quickly throw together in Power Query.  Grouping in Power Query does not force you to throw away the underlying data. A little bit of sorting before the grouping gets the data in the right order, and then you can use the {0} addressing to grab the first (newest) date.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("7ZrLbuM2FIZfRci2kYZ3idoZTWZQYKYoOkU3g1kcUZTDqSy6ugRNt32SLrvoougbtC/WQzmxnSYLRe60UygAbetCWxA//ec/h/S7d2cCmLUgSQxaZrEwksVaWxkTXZQVZGUKsjg7P2OE0ZiImJGIpjk2Jj8jJCcEz4kXjLwIHW77HbrjFrYL19k/f/rjNx/dRNDg1s+161wX2R+2tnUb2/RQ227Xd/U1vpW2+673290RbLaLL9/i53k0nsb2OWzhR/ykIiFMJyoV+877xkxhCqhEXAHelFBcxZngEFsipapKy7itsFvGGBU8NdQQqpVgFWMGKgLcghZEpsQYokoaLiCUVcQAjTlUOhZc6liXAHFFTKUlpZWs2P7yr1ffrN7s9yZ99f35CTz0DB42cpttbUcExuGx35uotNG2hsZVztw71vreG1/7p4F6Odi2t7ekOE/SVC2blM65mEzqle36AwIz1L279h3KaNsGGZkeGXURrFvf+A0e+RVmw6EyS5Ski4czPaytyo1rXNe3e5m0dj3U0PvWATIyA2rLbRyKy88MblTqRC5bMIzkgj2Bid/e0biJhs4H3VzZtoWRA4qldGv3dL85VgpjaUIEWzwVNd1wrEHD943tAg7XVL7d7EXT+aK19/KCAjooEZXFqIdqmp0ZME4SzZedGSAoyT9JUBe2g7b1de1HWoynCc0WzYozdKDJrL56mKTV+Lob1AAQMEG4diWU87NrzXgi9bItCLmw6Rq6/GDNcJQ47yHMRKDRbuSy3QYB8E9CGMdpgBY84UsHw3NGJ4P5Fk1jR8SGrLnH7V/WyGl2bMokvtJnAtPT484bh7oYHd74jW1xF3NhzJVDfVmHHPkEr0AenD3zmO4VH4HH39KqIJAkzZZd1wco0+v6fyRMPcSAlfzC574Chuy/cgutWUKWWV+omOL485xiLXiQgXpBHx//24f08hrqAQ6TKWFSeIxKvsAkaGjngchoQjChzR6zbTxREKlYrKgtYlGJDG8KylhyZrQoqKE8XFRyzhBYifcqK2FtxXSqbWGpBEFSoCqlIBUJY5ya8JPaxoUyZSxSZuIMecZUU+DcKEvY4ZF4DT1s9nuTvjofBc2lmIziQUZ7E0HtGns8P++LD3Y3OxwYQQP1bD6ZSvhjoWpJfIJaJvP50q53Pn5H5zrMNd6ummwHjF8wYkFF+XYsR04CRBVLJJXLBiRyoiYDeuu3Hgs3JHMFhavdvdWukcXcQhBxMJolUvIF40Br1zmfjuOLEvUxBrQ2GskMza4YDzjK48zp6frQaUJFlgi65AA2ApF6MpCVqaGFcUHe9IA2AicUHYFASpKMPTLzviACguQ0nUzg1TAu344KuDhdAVhtML348efTx/+lH/8E0UY1MmhtfVeUjwkvmCvXrE9SBCNY/4klmwTLWZpLOpnIm/F/QcEXAgTbVP77we6Sp928yJN47A07RW8QPMnUM4tshmEf5U2NNbbb+/Y8LHcxK1CRJJFkyTELqWisDD9m2XEaIsnCorpeNCKucja9Mlx1ruttg5SwYj91+LVkqJJlxy2e5nR6ofHvKOTgLToh6POMPbIq+D9k9P4v", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Publication = _t, #"Date (UTC)" = _t, Date = _t, Year = _t, YearMonth = _t, CompetencyNodeId = _t, CompetencyTitle = _t, Continent = _t, Country = _t, DeviceType = _t, EventName = _t, HourOfDay = _t, Language = _t, Locality = _t, SelectedLevelNodeId = _t, SelectedLevelTitle = _t, SessionDuration = _t, SessionID = _t, TargetLevelNodeId = _t, TargetLevelReached = _t, TargetLevelTitle = _t, TopicVersion = _t, TsSessionID = _t, UniqueUserID = _t, UserPrimaryRole = _t, #"UserRegião" = _t, UserRegion = _t, #"UserRegión" = _t, #"Userrégion" = _t, Userregione = _t, UserRoles = _t]),
    #"Sorted Rows" = Table.Sort(Source,{{"Date (UTC)", Order.Descending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"UniqueUserID", "CompetencyTitle"}, {{"Contents", each _, type table [Publication=nullable text, #"Date (UTC)"=nullable datetime, Date=nullable date, Year=nullable number, YearMonth=nullable date, CompetencyNodeId=nullable text, CompetencyTitle=nullable text, Continent=nullable text, Country=nullable text, DeviceType=nullable text, EventName=nullable text, HourOfDay=nullable text, Language=nullable text, Locality=nullable text, SelectedLevelNodeId=nullable text, SelectedLevelTitle=nullable text, SessionDuration=nullable text, SessionID=nullable text, TargetLevelNodeId=nullable text, TargetLevelReached=nullable text, TargetLevelTitle=nullable text, TopicVersion=nullable text, TsSessionID=nullable text, UniqueUserID=nullable text, UserPrimaryRole=nullable text, #"UserRegião"=nullable text, UserRegion=nullable text, #"UserRegión"=nullable text, #"Userrégion"=nullable text, Userregione=nullable text, UserRoles=nullable text]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Last Entry", each [Contents]{0}),
    #"Expanded Last Entry" = Table.ExpandRecordColumn(#"Added Custom", "Last Entry", {"Date (UTC)"}, {"Date (UTC)"})
in
    #"Expanded Last Entry"