BLANK
A
C
- CALCULATE
- CALCULATETABLE
- CALENDAR
- CALENDARAUTO
- CEILING
- CHISQ.DIST
- CHISQ.DIST.RT
- CHISQ.INV
- CHISQ.INV.RT
- CLOSINGBALANCEMONTH
- CLOSINGBALANCEQUARTER
- CLOSINGBALANCEYEAR
- COALESCE
- COLUMNSTATISTICS
- COMBIN
- COMBINA
- COMBINEVALUES
- CONCATENATE
- CONCATENATEX
- CONFIDENCE.NORM
- CONFIDENCE.T
- CONTAINS
- CONTAINSROW
- CONTAINSSTRING
- CONTAINSSTRINGEXACT
- CONVERT
- COS
- COSH
- COT
- COTH
- COUNT
- COUNTA
- COUNTAX
- COUNTBLANK
- COUNTROWS
- COUNTX
- COUPDAYBS
- COUPDAYS
- COUPDAYSNC
- COUPNCD
- COUPNUM
- COUPPCD
- CROSSFILTER
- CROSSJOIN
- CUMIPMT
- CUMPRINC
- CURRENCY
- CURRENTGROUP
- CUSTOMDATA
D
E
I
N
O
P
R
S
- SAMEPERIODLASTYEAR
- SAMPLE
- SEARCH
- SECOND
- SELECTCOLUMNS
- SELECTEDMEASURE
- SELECTEDMEASUREFORMATSTRING
- SELECTEDMEASURENAME
- SELECTEDVALUE
- SIGN
- SIN
- SINH
- SLN
- SQRT
- SQRTPI
- STARTOFMONTH
- STARTOFQUARTER
- STARTOFYEAR
- STDEVX.P
- STDEVX.S
- STDEV.P
- STDEV.S
- SUBSTITUTE
- SUBSTITUTEWITHINDEX
- SUM
- SUMMARIZE
- SUMMARIZECOLUMNS
- SUMX
- SWITCH
- SYD
T
U
Syntax of the BLANK Function
The basic syntax of the BLANK function is as follows:
BLANK()
This syntax simply returns a blank value or empty cell.
Applications of the BLANK Function
Replacing Null or Blank Values
The BLANK function is commonly used to replace null or blank values in a column or table. This is particularly useful when you’re working with data sources that contain missing values. By replacing these null or blank values with the BLANK function, you can ensure that your data is accurate and complete.
Filtering Data
The BLANK function can also be used to filter data in Power BI. For example, if you want to exclude rows that contain null or blank values in a particular column, you can use the BLANK function in a filter expression. This will ensure that only rows with non-blank values are included in your analysis.
Calculating Averages
When calculating averages in Power BI, the BLANK function can be used to ensure that null or blank values are excluded from the calculation. For example, if you have a column that contains sales data and some of the values are missing, you can use the AVERAGE function in conjunction with the BLANK function to calculate the average sales value without including the missing data.
Examples of Using the BLANK Function
Example 1: Replacing Null Values
Suppose you have a table that contains customer data and some of the values in the “Age” column are missing. You can use the BLANK function to replace these null values with a blank value. Here’s an example:
=IF(ISBLANK([Age]),BLANK(),[Age])
This formula checks if the “Age” column is blank. If it is, the formula returns a blank value. Otherwise, it returns the value in the “Age” column.
Example 2: Filtering Data
Suppose you have a table that contains sales data and you want to filter out rows that contain null values in the “Product” column. You can use the following filter expression:
=FILTER(SalesData,SalesData[Product]❰❱BLANK())
This expression returns a filtered table that only includes rows that have non-blank values in the “Product” column.
Example 3: Calculating Averages
Suppose you have a table that contains sales data and some of the values in the “Sales” column are missing. You can use the AVERAGE function in conjunction with the BLANK function to calculate the average sales value without including the missing data. Here’s an example:
=AVERAGEX(FILTER(SalesData,SalesData[Sales]❰❱BLANK()),[Sales])
This expression first filters out rows that contain null or blank values in the “Sales” column. Then, it calculates the average value of the remaining sales data.
The BLANK function is a powerful tool in Power BI that can be used to replace null or blank values, filter data, and calculate averages. By understanding the syntax and applications of this function, you can ensure that your data is accurate and complete, and that your analyses are based on the most relevant information.
