To get the value from a text column without a certain letter in Power BI, you can use the SUBSTITUTE function.
The SUBSTITUTE function takes three arguments: the original text, the letter you want to replace, and the replacement text. To remove a letter, you can replace it with an empty string, represented by double quotes with no space in between.
In this article, I will walk you through how to get value from a text column without a certain letter using the SUBSTITUTE function in Power BI.
Read to learn more.
Getting started
You can follow these simple steps to get the value from a text column without a certain letter in Power BI.
For example, I want to get the other values from the Item Range column without RANGE in the table below.
Get the data
The first thing to do is to import the data into Power BI Desktop.
Create a new column
After you have your data imported into Power BI, go to the Fields tab and select the data table. Then right-click and select New Column.
You can then enter a name for the new column and the formula for removing the letter.
Create the formula
After creating a new column, you will see a dialog box, where you will write the formula to get the value.
For instance, in the table above, I want to get a value (remove RANGE) from the Item Range column.
The formula for that will be:
New_Column = SUBSTITUTE(Sheet1[Item Range], "RANGE-", "")The resulting value of the new column created is to create a column named New_Column that contains the same text as the original Item Range column, but with all instances of the RANGE removed. (As you can see below).
FAQs
What does the text.remove function do?
Just as the name suggests, the Text.Remove function in Power BI is used to remove text from a string.
What function replaces a value in Power BI?
The function that replaces a value in a text string is Text.Replace, and takes three arguments: the original text, the value to replace, and the replacement text.
Example:
Text.Replace("Hello World","World","Power BI")This formula will replace the text World in the original string Hello World with Power BI, and the result of the text string will be Hello Power BI.
Conclusion: How to Get Value From a Text Column Without a Certain Letter
That is how to get value from a text column without a certain letter in Power BI.
Now, you can create a visual to display the values without the letters RANGE in the table and can as well format the visual as you like.
I hope you understood these steps.
Thanks for reading.