Conditional removal of rows in importhtml data output-CodePudding

=ARRAYFORMULA(IFERROR(
substitute(IMPORTHTML("https://niftyinvest.com/option-chain/"&D2&"?expiry="&$B$2,"Table",1),"*","")*1,
substitute(IMPORTHTML("https://niftyinvest.com/option-chain/"&D2&"?expiry="&$B$2,"Table",1),"*","")))

where D2=MARUTI and B2=30Jun2022 let's say...

Now I want to remove the row in which all columns value is zero.

CodePudding user response：

Try

    =query(ARRAYFORMULA(IFERROR(substitute(IMPORTHTML("https://niftyinvest.com/option-chain/"&D2&"?expiry="&$B$2,"Table",1),"*",""), substitute(IMPORTHTML("https://niftyinvest.com/option-chain/"&D2&"?expiry="&$B$2,"Table",1),"",""))),
"where "&ARRAYFORMULA(TEXTJOIN(" and ",, "Col"&SEQUENCE(11,1,1,1)&" is not null")),1)

edit : (for AARTIIND)

=query(ARRAYFORMULA(IFERROR(substitute(IMPORTHTML("https://niftyinvest.com/option-chain/"&D2&"?expiry="&$B$2,"Table",1),"*",""), substitute(IMPORTHTML("https://niftyinvest.com/option-chain/"&D2&"?expiry="&$B$2,"Table",1),"",""))),
"where Col6='740' ",0)

CodePudding user response：

use:

=QUERY(ARRAYFORMULA(IFERROR(
 SUBSTITUTE(IMPORTHTML("https://niftyinvest.com/option-chain/"&D2&"?expiry="&$B$2,"table",1),"*",)*1,
 SUBSTITUTE(IMPORTHTML("https://niftyinvest.com/option-chain/"&D2&"?expiry="&$B$2,"table",1),"*",))),
 "where "&TEXTJOIN(" and ", 1, "Col"&SEQUENCE(11)&" <>0"))