Search code examples
google-sheetsnullpivotunpivotisnull

Unpivot - Ignore if value is null

in Google Sheet, I use this formula to unpivot a table:

=arrayformula(SPLIT(FLATTEN(IMPORTRANGE("https://docs.google.com/spreadsheets/d/";"Foglio1!A2:A999")&"|"&IMPORTRANGE("https://docs.google.com/spreadsheets/d/";"Foglio1!AQ1:BB1")&"|"&IMPORTRANGE("https://docs.google.com/spreadsheets/d/";"Foglio1!AQ2:BB999"));"|"))

The data structure is like in the screenshot: the problem is that the unpivoting create row also when (for example) Alpha in Project A is null.

How can i fix this problem, creating unpivoted rows only if the value in the cell is > 0?

Many thanks mate!

Screenshot

Solution

  • Wrap the formula in a query(), like this:

    =query( arrayformula(...); "where Col3 is not null"; 1 )