Forum Discussion

profilewatercli's avatar
profilewatercli
Regular Visitor
2 years ago
Solved

Power query formula help

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 SessionName
Dec-23A
Jun-23A
Dec-22A
Jun-23B
Dec-22B
Dec-22C
Jun-23D
Jun-22E

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?

Tips and Tricks
  • dufoq3's avatar
    dufoq3
    2 years ago

    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?

4 Replies

  • dufoq3's avatar
    dufoq3
    Community Champion
    2 years ago

    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's avatar
      profilewatercli
      Regular Visitor
      2 years ago

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

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

      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's avatar
        dufoq3
        Community Champion
        2 years ago

        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?