Tuesday, March 18, 2014

PowerPivot: BLANK() & ISBLANK()

Today I ran into an issue when writing a formula in the calculated column area.  I needed to calculate the difference in minutes between two date/time columns.  However, I first wanted to check to be sure both columns in the row had an entry to avoid some outrageous number appearing as the result of the formula.

In excel I would have simply handled this issue with an If() Statement check to be sure the cell wasn't blank and then returning "" if it was.

The formula in an Excel spreadsheet looks like this:


Unfortunately, when used in the data model this will return "#Error" for all rows in the column instead of a blank value.

After consulting with a coworker, he showed me how he has resolved this issue:


The ISBLANK() formula simply checks to see if the value in the column is blank.  If it is, BLANK() returns the value as a blank cell - Much the same as using "" in an Excel Sheet formula.

To Explain the formula, I will write it in words:

If the value on this row of column1 is blank then return blank, otherwise go check if the value on this row of column2 is blank.  If it is, return blank, otherwise calculate the difference between Date2 and Date1.  Multiply the result by 24 to get hours.  Multply the hours by 60 to get minutes.

Hope this helps someone else save some time!

No comments:

Post a Comment