calculating time from two different rows based on condition from different columns and removing rows

[Anonymous]

Hello PowerBi friends,

I would like to create a new column to calculate the time different ( duration ) between a "Fault" row and the "CRN_ATOEXEINS" row that is below the "Fault" row.

and to reduce data size,

removing all the "CRN_ATOEXEINS" rows that are not under "FAULT" rows.

only keeping the "Fault" rows and the "CRN_ATOEXEINS" that is underneath it.

PowerBi File Link:

https://drive.google.com/drive/folders/1uGwRe2irg58XoAEBtucQVpZwDfRAvmCb?usp=sharing

[Anonymous · Accepted solution]

this is how i did it.

go the transform data

step 1 add a Index column.

step 2 do a if statement , if the previous row value is same as the current row, label "keep" if not, label "delete"

try if [alarm class] = #"Added Index" {[Index] - 1} [alarm class]
then "delete"
else "keep"
otherwise null

Step 3 filter to show only the " keep" rows.

[lbendlin]

" the "CRN_ATOEXEINS" row that is below the "Fault" row."

Power BI has no idea what you mean when you say "below".  Please add an index column that allows for the proper referencing.

[Anonymous]

Hi Lbendlin,

Yes that's what i wanted to do. but i'm not sure how i can add an index column that only identified them.

would you show me how it is done?

Thanks in advance

[lbendlin]

You can either use a suitable column in your soure data or you can add an index column in Power Query