How to Check IF Text is NULL in Power BI

by

In this Power BI article, we will learn how to check if the text is null using the Power Query editor with examples. In addition, we also cover below mentioned points.

  • Power query replaces null in all columns
  • Power BI checks if the Text is null
  • Power query replace null with column value

Check if the text is null in Power BI

Let us see how to check if the text is null using the Power BI if function in Power BI.

In this example, we will use the below Stocks table data as a data source to check if the text is null and then returns the true value or else a false value in Power BI.

Power BI IF contains
Power BI IF contains
  • Log in to the Power Bi desktop and load data using the get data option. Once the data has been loaded, Click on the new column option under the modeling tab and apply the below-mentioned formula:
Text Null = IF(not(isblank(Stocks[Symbol])),"No","Yes")

Where,

  1. Text Null = Newly calculated column
  2. Stocks = Table Name
  3. Symbol = Existing Column Name

In the below screenshot, we can see that if the Symbol column has null values then it returns the value as true else it returns a false value based on the condition applied:

Power BI checks if the text is null
Power BI checks if the text is null

This is how to check if the text is null using the Power BI if function in Power BI.

Power query replaces null in all columns

Here we will see how we can replace null in all columns using the Power Query editor in Power BI.

In this example, we will use the Query editor to replace the null values in the columns using the replacement value option.

To achieve this follow the below steps:

  • Open the Power Bi desktop and load the data into it, and under the Home, tab selects the transform data option as shown below: It will automatically redirect to the Power Query editor.
Power BI checks If the Text contains
Power BI checks If the Text contains
  • Now in the power query editor, you can see that the column data presented in null values as highlighted below:
Power query replaces null in all columns
Power query replaces null in all columns

To replace all the null values in a column, right-click on the column and select replace values as shown below:

Power query replaces null in all columns example
Power query replaces null in all columns example
  • In the Replace values popup window, enter the Value To find as null and replace with as empty which takes as null value.
  • Click the ok button.
Example of Power query replaces null in all columns
  • In the below screenshot, you can see that the symbol column has been replaced with the null values as shown below:
  • Click on Close & Apply under the home tab, so that the changes will be reflected on the Power Bi desktop.
= Table.ReplaceValue(#"Removed Columns1",null,"",Replacer.ReplaceValue,{"Symbol"})
Replace null values from ALL columns into null values Power Query
Replace null values from ALL columns with null values Power Query

This is how to replace null in all columns using the Power Query editor in Power BI.

Power query replace null with column value

Let us see how we can replace null values with column values using the Power Query editor in Power BI.

In this example, we will use the Query editor to add a custom column, if the column has null values replace it with another column value. For example, if the symbol column contains a null value then we will display the stock name column value.

  • Load the data into the Power Bi desktop, and under the Home, tab selects the transform data it automatically redirects to the Power Query editor.
  • In the Power Query editor, Add a custom column by selecting Add Column-> Custom column as highlighted below:
Power query replace null with column value
Power query replace null with column value
  • In the custom column pop-up window, enter the new column name and add the below-mentioned formula and click on the ok button.
if([Symbol]=null)then [Stock Name] else [Symbol]
Power query replace null with column value example
Power query replace null with column value example
  • In the below screenshot, we can see that the newly added custom column displays the value based on the condition applied.
  • If the Symbol column contains the null value then it returns the stock name column value else it returns the default value.
  • Click on Close & Apply under the home tab, so that the changes will be reflected on the Power Bi desktop.
= Table.AddColumn(#"Removed Columns1", "Replace Column Values", each if([Symbol]=null)then [Stock Name] else [Symbol])
Example of Power query replace null with column value
Example of Power query replace null with column value

This is how to replace null values with column values using the Power Query editor in Power BI.

In this Power BI article, we have learned how to check if the text is null using the Power Query editor with different examples. In addition, we also covered below mentioned points.

  • Power query replaces null in all columns
  • Power query replace null with column value

You may like the following Power BI tutorials:

>