Forum Discussion
How to match events in same line using Power Query
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.
- 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
- 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
- The first is called Alarms that selects only Fault Type 1 entries with Alarm
- The second is called Normals selecting only Normal
Now I can join the two queries. So I created query Merge1 that:
- Inner Joins Alarms and Normals on Equipment, Location, and ID
- Expands Normals' Date & Time column
- 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.
I am learning new every single day.
Hi Craig, your result is quite close but how do we capture this part where its an single event is there any way, check the attach snap.
Below snap is from Raw Data. Because the Equipment was at fault from 7th to 10th.
- Craig HatmakerSep 11, 2020Iron Contributor
Jeev125 ,
Change the merge join type from Inner to LeftOuter
= Table.NestedJoin(Alarms,{"Location", "Equipment", "ID"},Normals,{"Location", "Equipment", "ID"},"Normals",JoinKind.LeftOuter)