How to match events in same line using Power Query

%3CLINGO-SUB%20id%3D%22lingo-sub-1646749%22%20slang%3D%22en-US%22%3EHow%20to%20match%20events%20in%20same%20line%20using%20Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1646749%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20data%20set%20where%20i%20need%20match%20first%20Alarm%20and%20next%20Normal%20event%20into%20single%20line%20to%20find%20difference%20duration.%20i%20have%20attached%20a%20sample%20file%20with%20PQ%20uptill%20now.%20Need%20to%20get%20the%20output%20in%20one%20single%20line.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOutput%20should%20have%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlarm%20Time%20%26amp%3B%20Normal%20Time%20in%20one%20line.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1646749%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1647025%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20match%20events%20in%20same%20line%20using%20Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1647025%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F735618%22%20target%3D%22_blank%22%3E%40Jeev125%3C%2FA%3E%26nbsp%3BYou%20can%20Fill%20-%20Up%20column%20%22Normal%22%20and%20then%20filter%20out%20%22null%22%20from%20the%20%22Alarm%20column%22.%20Furthermore%2C%20I'd%20recommend%20that%20you%20remove%20unwanted%20columns%20via%20the%20query%2C%20rather%20than%20hiding%20and%20deleting%20them%20from%20the%20table%20loaded%20into%20Excel.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1647089%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20match%20events%20in%20same%20line%20using%20Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1647089%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20Riny%2C%20I%20cant%20use%20fill%20since%20the%20data%20is%20not%20consistent%20like%20Alarm%20%26amp%3B%20Normal.%20Also%20Fill%20copies%20UP%20or%20DN%20value%20to%20number%20of%20lines%20where%20my%20data%20doesn't%20follow%20the%20consistent%20patter.%26nbsp%3B%20Also%20condition%20with%20Raw%20Data%20i%20cant%20miss%20any%20line%20that%20the%20condition%20too.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attach%20Sample%20photo%20with%20the%20issue%20where%20i%20have%20t%20match%20colour%20coded%20cells%20respectively.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1647519%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20match%20events%20in%20same%20line%20using%20Power%20Query%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1647519%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F735618%22%20target%3D%22_blank%22%3E%40Jeev125%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20looks%20like%20you%20need%20to%20merge%20the%20table%20with%20itself%20using%20Merged%20column%2C%20plus%20I%20think%20you%20don't%20need%20Date%20%26amp%3B%20Time%20column%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

I have a data set where i need match first Alarm and next Normal event into single line to find difference duration. i have attached a sample file with PQ uptill now. Need to get the output in one single line. 

 

 

Output should have 

 

Alarm Time & Normal Time in one line.

6 Replies
Highlighted

@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.

Highlighted

@Riny_van_Eekelen 

 

Hi Riny, I cant use fill since the data is not consistent like Alarm & Normal. Also Fill copies UP or DN value to number of lines where my data doesn't follow the consistent patter.  Also condition with Raw Data i cant miss any line that the condition too.

 

I have attach Sample photo with the issue where i have t match colour coded cells respectively.

 

 

Highlighted

@Jeev125 

It looks like you need to merge the table with itself using Merged column, plus I think you don't need Date & Time column

Highlighted

@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. 

BeyondExcelLift.png

Highlighted

@Craig Hatmaker 

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.Capture 11.png

 

 

Highlighted

@Jeev125 , 

 

Change the merge join type from Inner to LeftOuter

 

= Table.NestedJoin(Alarms,{"Location", "Equipment", "ID"},Normals,{"Location", "Equipment", "ID"},"Normals",JoinKind.LeftOuter)