Power query formula help

[profilewatercli]

Hi all,

I have a set of data that requires me to set up personnel that is active/inactive.

The dataset is about a set of professional that took exam on certain timeframe.

The dataset sample are shown as follow

Exam Session	Name
Dec-23	A
Jun-23	A
Dec-22	A
Jun-23	B
Dec-22	B
Dec-22	C
Jun-23	D
Jun-22	E

The expected output are attached as image below.

As you can see, Candidate C and E are consider as inactive because they are not taking exam on dec 23 and jun 23. However, since this excel is going to be update constantly, the date of dec 23 will move to jun 24 when the next result comes in. hence there will be a new col and the condition will change according to that.

With the following condition, how should I create my formula for it in excel and even better, for power query instead?

[dufoq3 · Accepted solution]

Hi, it works. I've updated Source data:

Result:

But maybe I don't understand what do you really need. Could you upload your data to google drive for example and provide a link?

[dufoq3]

Hi @profilewatercli, check this.

Result

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcklN1jUyVtJRclSK1YlW8irNQ+aCZY0wZZ1QZdG4zqiKXRBckKyrUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Exam Session" = _t, Name = _t]),
    #"Grouped Rows" = Table.Group(Source, {"Name"}, {{"All", each _, type table [Exam Session=nullable text, Name=nullable text]}}),
    #"Added Helper" = Table.AddColumn(#"Grouped Rows", "Helper", each 1, Int64.Type),
    #"Expanded All" = Table.ExpandTableColumn(#"Added Helper", "All", {"Exam Session"}, {"Exam Session"}),
    #"Added DateHelper" = Table.AddColumn(#"Expanded All", "DateHelper", each Date.FromText([Exam Session] & "-1", [Format="MMM-yy-d", Culture="en-US"]), type date),
    #"Sorted Rows" = Table.Sort(#"Added DateHelper",{{"DateHelper", Order.Descending}}),
    #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",{"DateHelper"}),
    #"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"Exam Session"]), "Exam Session", "Helper"),
    #"Added Status" = Table.AddColumn(#"Pivoted Column", "Status", each 
        [ a = List.RemoveNulls(List.FirstN(Record.ToList(Record.RemoveFields(_, "Name")),2)),
          b = if List.Count(a) > 0 then "Active" else "Inactive"
        ][b], type text)
in
    #"Added Status"

[profilewatercli]

Hi there, I tried running your formula using an updated data as below.

Exam Session	Name
Dec-23	A
Jun-23	A
Dec-22	A
Jun-23	B
Dec-22	B
Dec-22	C
Jun-23	D
Jun-22	E
Jun-24	C
Jun-24	E

However, the formula doesnt sync as the updated version. It's still showing Dec-23, Jun-23, Dec-22 and Jun-22 as follows.

My final plan if to have a slicer selecting the session (datemonth) and showing the candidate if its active or non-active

[dufoq3 · Accepted solution]

Maybe you want something like this (I've attached pbix file):

Filtered Dec-23:

[dufoq3 · Accepted solution]

Hi, it works. I've updated Source data:

Result:

But maybe I don't understand what do you really need. Could you upload your data to google drive for example and provide a link?