How to translate Excel Functions to Power Query M for IF and XLOOKUP functions?

Hi,

I am new to Power Query Editor (is this abbreviated PQE?) and have been using it to automate the process of editing data from a *.csv.

I have been trying to add a custom column to PQE. However, in my naivety, I was unaware that Excel functions are different to PQE functions. I created the code in a test page of Excel and attempted to copy it over when it wouldn't work. I have/had been attempting to translate the functions to the equivalent M code but was unable to do so. If anyone can help inform me of the equivalent functions in M or help translate the whole code, I would appreciate it.

In PQE, I have 20 columns. Ten of these begin with "Period.", ten begin with "Attend_Status." such that all columns (from left to right) would read: Period.1, Period.2, ..., Period.10, Attend_Status.1, Attend_Status.2, ..., Attend_Status.10.

The code, ignoring references to the table, is:
=IF(XLOOKUP("Home Class",[PERIOD.1]:[PERIOD.10],[ATTEND_STATUS.1]:[ATTEND_STATUS.10],"",0,-1)="Absent",IF(COUNTIF([ATTEND_STATUS.1]:[ATTEND_STATUS.10],"Late Signed In")>0,"","!!!"),IF(XLOOKUP("EVENT",[PERIOD.1]:[PERIOD.10],[ATTEND_STATUS.1]:[ATTEND_STATUS.10],"",0,-1)="Absent",IF(COUNTIF([ATTEND_STATUS.1]:[ATTEND_STATUS.10],"Late Signed In")>0,"","!!!"),""))

As you can see, in the code are four nested IF functions, two similar XLOOKUP functions and two duplicate COUNTIF functions. The purpose of the code is to filter (if that is the correct nomecleture) the rows of data and inform me if a given row matches the given conditions - that is, the student is absent in the Home Class or Event and did not sign in later. Specifically, the formula reads:

IF the equivalently positioned value for "Home Class" (using XLOOKUP) is "Absent",
then IF (2nd nested IF) the text "Late Signed In" is present in the range (COUNTIF>0), then BLANK ("") is displayed, else display "!!!",
else IF (3rd IF in last value_if_false) the value in the equivalent position of "Event" (similar XLOOKUP) is "Absent",
then IF (4th IF) the text "Late Signed In" is present (same as above), then BLANK ("") is displayed, else display "!!!".

I tried to create the proper function using M with IF (then, else) and List.PositionOf, but I couldn't quite work it out and I wasn't sure if that was the best method/function to use anyway.

If anyone is able to point me in the correct direction or translate my code, I would greatly appreciate it.