How to match events in same line using Power Query

Jeev125,

Assuming I understand your need, the key to this problem is creating an ID to match Alarms and Normals to. For that we can use an Excel Formula. But before we add the formula, we should reduce the number of rows to just what we need. Oh, and to document things better I renamed Table1 to tblLift.

1. I created a new query over tblLift that:
   • Selects only Fault entries for Lift Fault 
   • Selects only Equipment entries that begin with LFT-
   • Removes all other columns except Date & Time, Location, Equipment, Fault Type 1
   • Sorts rows by Equipment, Location, and Date & Time
2. I loaded that back into Excel and with only the entries needed I added formulas to calculate an event ID. I:
   • Renamed the result table to tblFiltered
   • Added helper column Row: =ROW()-ROW(tblFiltered[#Headers])
   • Added ID column which matches Equipment, Location, and Alarms to Normals 
     =IF([@Row]=1,0,
       IF(INDEX([Equipment]&[Location], [@Row]-1)=[@Equipment]&[@Location],INDEX([ID],[@Row]-1), 0))+
       IF(TRIM([@[Fault Type 1]])="Alarm", 1, 0)

At this point we could just create a PivotTable but to keep things consistent I then created two more queries over tblFiltered

1. The first is called Alarms that selects only Fault Type 1 entries with Alarm
2. The second is called Normals selecting only Normal

Now I can join the two queries. So I created query Merge1 that:

1. Inner Joins Alarms and Normals on Equipment, Location, and ID
2. Expands Normals' Date & Time column
3. Renames Alarms' Date & Time to Alarm and Normals' to Normal

I closed and loaded Merge1 into worksheet PQ Pivot. Below is the sample output. Hopefully that is on target with what you want. 

Jeev125 You can Fill - Up column "Normal" and then filter out "null" from the "Alarm column". Furthermore, I'd recommend that you remove unwanted columns via the query, rather than hiding and deleting them from the table loaded into Excel.