Sep 08 2020 06:02 PM
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.
Sep 08 2020 10:45 PM
@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.
Sep 08 2020 11:01 PM
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.
Sep 09 2020 03:20 AM
It looks like you need to merge the table with itself using Merged column, plus I think you don't need Date & Time column
Sep 09 2020 07:29 AM
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.
At this point we could just create a PivotTable but to keep things consistent I then created two more queries over tblFiltered
Now I can join the two queries. So I created query Merge1 that:
I closed and loaded Merge1 into worksheet PQ Pivot. Below is the sample output. Hopefully that is on target with what you want.
Sep 10 2020 06:11 PM
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.
Sep 11 2020 05:21 AM - edited Sep 11 2020 05:22 AM
@Jeev125 ,
Change the merge join type from Inner to LeftOuter
= Table.NestedJoin(Alarms,{"Location", "Equipment", "ID"},Normals,{"Location", "Equipment", "ID"},"Normals",JoinKind.LeftOuter)