Replace previous, current and next row with a blank in a column depending on score of other column

[CornelisV]

Dear all,

See here an example of a table:

ColumnA	ColumnB	ColumnC
30	0	30
35	0
45	1
15	0
13	0	13
50	0	50

Column A should be transfered into column C, depending omn the score of column B. If the score of column B is 1, then the previous, current and next row of column A must be replaced by a blank with Column C as result. 

Any idea how to solve: DAX or Power M Query?

Best regards,

Cornelis

[GeraldGEmerick · Accepted solution]

@CornelisV You would need an index column to define "previous" and "next". If you have an index column then you could do something like the following:

Column C = 
VAR _CurrentIndex = [Index]
VAR _ColumnA = [ColumnA]
VAR _ColumnB = [ColumnB]
VAR _PreviousColumnB = CALCULATE( SUM( [ColumnB] ), FILTER( 'Table', [Index] = _CurrentIndex - 1 ) )
VAR _NextColumnB = CALCULATE( SUM( [ColumnB] ), FILTER( 'Table', [Index] = _CurrentIndex + 1 ) )
VAR _Return = IF( _ColumnB + _PreviousColumnB + _NextColumnB > 0, BLANK(), _ColumnA )
RETURN _Return

[CornelisV]

Hi @GeraldGEmerick ,

Thank you for the solution, it works.

This is a smart and fast solution and works well if you want to apply 2 or more steps back or forward. For your information, the scores 0 and 1 in column B are the valves of a process equipment where 0 = close and 1 = open and we have > 100,000 observations.

Best regards,

Cornelis

[mickey64 · Accepted solution]

For your reference.

Step 0: I use this data below.

Step 1: I make 3 tables below in Power Query Editor.

<DATA_0>

<DATA_+1>

<DATA_-1>

Step 2: I merge queries below.

Step 3: I merge queries below too.

Step 4: I replace 'null' to '0' and add a custum column below.

Step 5: I chage type of ColumnC and delete 3 columns below.

<Before>

<After>

[CornelisV]

Dear @mickey64 ,

Thank you for providing M Query solution, it works!

It is very interesting to see that the solution from @GeraldGEmerick and ythat from ou are very different, all going to the same result. The solution from @GeraldGEmerick requires DAX knowledge, whereas from you more in Power M query. Have a great day,

Cornelis

[GeraldGEmerick · Accepted solution]

@CornelisV You would need an index column to define "previous" and "next". If you have an index column then you could do something like the following:

Column C = 
VAR _CurrentIndex = [Index]
VAR _ColumnA = [ColumnA]
VAR _ColumnB = [ColumnB]
VAR _PreviousColumnB = CALCULATE( SUM( [ColumnB] ), FILTER( 'Table', [Index] = _CurrentIndex - 1 ) )
VAR _NextColumnB = CALCULATE( SUM( [ColumnB] ), FILTER( 'Table', [Index] = _CurrentIndex + 1 ) )
VAR _Return = IF( _ColumnB + _PreviousColumnB + _NextColumnB > 0, BLANK(), _ColumnA )
RETURN _Return