How to convert next row of a database to null based on condition

[Anonymous]

I have next data in Excel:

If I read it into Power Bi, I would like to get something like:

Based on condition like:

If Hum. >= 80 and Temp. <= 2 ---> Next row fill it with nulls

Is it possible to apply conditions while loading data? Should I load the source data and, after that, apply conditions to the whole table? Is it possible to appply conditions to the whole table at once rather than column by column?

[v-easonf-msft · Accepted solution]

Hi , @Anonymous 

Try steps as below:

Enter the interface of "edit queries"/"transform data"

1.Create   index column

2. right click "Insert step after" to insert step "filter"

= Table.SelectRows(#"Added Index",each if [Index]=1 then true else  not(#"Added Index"{[Index]-2}[#"Hum."]>=80 and #"Added Index"{[Index]-2}[#"Temp."]<=2))

It will show as below:

3.  add step "AddNullrow"  :

= List.Generate( () =>
        [RowNumber = Table.RowCount(#"Added Index"), TableResult = FilterTable, Counter = 1],
        each [Counter] <= [RowNumber]+1,
        each [
            TableResult = if let c = [Counter] in Table.RowCount(Table.SelectRows(FilterTable, each [Index] = c)) = 0
                            then Table.InsertRows([TableResult],[Counter]-1, {[#"Hum." = null, #"Temp." = null, #"Vel." = null, Index = [Counter]]})
                            else [TableResult],
            Counter = [Counter] + 1,
            RowNumber = [RowNumber]
        ],
        each [TableResult]
    ){Table.RowCount(#"Added Index")}

It will show as below:

Here is a demo.

PS. Replacing with null will consume a lot of performance, and if your data source is excel,  method will not shorten the refresh time

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-easonf-msft,

Thank you for your worked answer. Nonetheless, I am quite newbie in DAX programming language and I would be grateful if you could ask some comments on step 3 when you add the "AddNullrow".

At the same time, when you say that this approach it is going to consume a lot of performance, is it because I want to convert that row to, specifically, nulls? Would it be the same consumption if that row is convert to 0, for example?

[amitchandak]

Refer, if these can help

https://community.powerbi.com/t5/Desktop/Filter-row-with-Power-query-m/td-p/571779

https://docs.microsoft.com/en-us/powerquery-m/table-selectrows

[Anonymous]

@amitchandak Thanks for links. Nonetheless, I want to convert to null next row based on condition checked on previous one.

I have seen that it is possible to filter rows but based on its own values. How can apply the same approach but using values from previous row? Is that possible?