In this Power Bi tutorial, we will discuss different types of Power Bi Text Functions with examples. And also we will discuss the below points:
- What is Power BI Text Function
- List of Power Bi Text Functions
- Power Bi Text Functions Example
Power BI Text Function
The set of Power Bi text functions is based on the library of strings function in Excel, but which have been modified to work in the tables and columns in tabular models.
List of Power Bi Text Functions
Here we will discuss the List of important Power Bi Text Functions with description and syntax.
Function | Description | Syntax |
---|---|---|
COMBINEVALUES | This function joins two or more strings into one string | COMBINEVALUES(<delimiter>,<expression><expression>[,<expression>]) |
CONCATENATE | This function joins two strings into one string | CONCATENATE(<text1>, <text2>) |
CONCATENATEX | This function concatenates the result of an expression evaluated for each row in a table | CONCATENATEX(<table>, <expression>[, <delimiter> [, <orderby expression> [, <order>]]) |
Exact | This function compares two text strings and returns true if they are exactly the same, or else it returns false | EXACT(<text1>,<text2>) |
FIND | This function returns the starting position of one text string within another text string | FIND(<find text>, <within text>[, <start num>][, <NOTFOUNDVALUE>]]) |
FIXED | This function rounds a number to the specified number of decimal and returns the result as text | FIXED(<number>,<decimals>, <no commas>) |
Format | This function converts a value to text according to the specified format | FORMAT(<value>, <format strings>[, <local name>]) |
LEN | This function returns the number of characters in a text string | LEN(<text>) |
LOWER | This function converts all letters in a text string to lower case | LOWER(<text>) |
REPLACE | This function replaces a part of a text string, based on the number of character you specify, with a different text string | REPLACE(<old text>, <start num>,<num_chars>,<new text>) |
REPT | This function Rept a text for a given number of times | REPT(<text>,<num times> |
SEARCH | This function returns the number of characters at which a specific character or text was first found, by reading left to right, | SEARCH(<find_text>, <within text>[, <start num>][, <Not found value>] |
SUBSTITUTE | This function replaces existing text within new text in a string | SUBSTITUTE(<text>, <old text>, <new text>,< instance num>) |
VALUE | This function converts a text string that represents a number to a number | VALUE(<text>) |
Read Power BI Information Functions
Power Bi Text Function Examples
Here we will discuss different types of Power BI text functions for example.
Example 1: Using Power Bi COMBINEVALUES Function
Here we will see how to use COMBINEVALUES() in Power Bi with an example.
So for this, we will use the below sample data to combine two values, you can download it from here.
We will combine the values Month name and year column using COMBINEVALUE(), with delimiter “,”.
To combine values create a calculated column, then write the below DAX formula.
Combine value = COMBINEVALUES(",",Sheet1[Month name],Sheet1[Year])
Read Power Bi count function – DAX Examples
Example 2: Using Power Bi CONCATENATE Function
Here we will see how to use CONCATENATE Function in power bi with an example.
We will use the below sample table, to concatenate the value of two columns.
So here we will concatenate the value of the first name and last name column using the CONCATENATE().
For this we will create a calculated column, then write the below DAX formula:
Concatenate = CONCATENATE(Sheet1[First name] , Sheet1[Last name])
Read Power BI average function with Examples
Example 3: Using Power Bi CONCATENATEX Function
Here we will see how to use CONCATENATEX() in power bi desktop with an example.
We will use the below sample table to concatenate the two values in the two columns.
So here we will concatenate the first name and last name present in the table and then the whole name is separated by a comma.
For this, we will create a measure, click on the new measure from the ribbon.
Then write the below measure:
Full name = CONCATENATEX(Sheet1, [First name] & " "&[Last name], ",")
Read Power BI Switch – DAX function
Example 4: Using Power Bi Exact function
Here we will see how to use the EXACT function in power bi with an example.
we will use the below sample table, to compare the two texts exactly the same or not.
So here we will check the Date column year is exactly the same as the year column or not, if it is exactly the same then it returns true or else returns false.
So, for this we will create a calculated column, then we will write the below DAX formula:
Exact = EXACT(Sheet1[Date].[Year], Sheet1[Year])
Read Power BI MAX and MIN function with Examples
Example 5: Using Power Bi Find Function
Here we will see how to use the FIND function in power bi.
We will use the below sample table to find the name within the first name.
So we will create a calculated column that will find the Alex from the First name column, if the function is found then return 1 or else return 0.
For this, click on the modeling tab then write the below measure:
Column = FIND("Alex",Sheet1[First name],,"0")
Read Power BI DAX Logical functions
Example 6: Using Power Bi FIXED Function
Here we will see how to use Fixed function in power bi.
So, we will use the below sample data, to calculate the fixed decimal places of columns.
We will use the Profit column to round the number to the 2 decimal places, so for this, we will create a Power BI calculated column.
Click on the Modelling tab -> New Column. Then write the below DAX formula:
Fixed Decimal = FIXED(Sheet1[Profit],2,1)
Read Power BI DAX SUM and SUMX function
Example 7: Using Power Bi FORMAT Function
Here we will see how to use the FORMAT function in the Power BI.
We will use the below sample data to change the format of the date column.
Now we will create a calculated column, to change the format of the date column using FORMAT().
For this click on the Modelling Tab-> New column, then write the below Dax formula:
Format date = FORMAT(Sheet1[Date],"mm-dd-yyyy")
Read Power bi date format
Example 8: Using Power Bi LEN function
Here we will see how to use the LEN function using the Calculated column in power bi.
We will use the below sample data to check the length of the first names.
Now we will create a calculated column, by clicking on the Modelling tab -> New column.
Then write the below DAX formula:
length = LEN( Sheet1[First name])
Read Power bi measure count with filter
Example 9: Using Power Bi LOWER Function
Here we will see how to use the LOWER Function in Power Bi Desktop with an example.
We will use the below sample data to convert the first name to the lower case letter.
Now we will create a calculated column, click on the modeling tab -> New column.
Then write the below DAX formula:
Lower = LOWER(Sheet1[First name])
Read How to create a table in Power bi
Example 10: Using Power BI REPLACE Function
Here we will see how to use REPLACE function in power bi.
We will use the below sample data to replace the old text of the Id column with the new text.
Now we will create a calculated column, click on the Modelling tab-> New column.
Then write the below DAX formula:
Replace = REPLACE(Sheet1[Id],1,0,"OD")
Read Power BI Aggregate Functions
Example 11: Using Power Bi REPT Function
Here we will see how to use the Power BI REPT function with an example.
We will use the below sample table, to repeat the text of the id column using REPT().
Now we will create a calculated column to repeat the text in the Id column, so click on the Modelling tab -> new column.
Then write the below DAX formula:
Repeatid = REPT(Sheet1[Id],2)
Read Microsoft Power BI KPI Visual
Example 12: Using Power Bi SEARCH Function
Here we will see how to use the SEARCH function in the Power Bi desktop with an example.
We will use the below sample data, to search names from the first name column using SEARCH().
Now we will create a calculated column to search the christ name from the first name column, click on the modelling tab -> New column.
Then write the below Dax formula:
Searchname= SEARCH("Christ" ,Sheet1[First name],,0)
Example 13: Using Power Bi SUBSTITUTE Function
Here we will discuss how to use the SUBSTITUTE function in the Power BI desktop with an example.
We will use the below sample table, to replace the id column with text using SUBSTITUTE().
Now we will create a calculate d column to replace the id column text with new text ODD, so click on the modelling tab -> new column. Then write the below Dax formula:
column = SUBSTITUTE(Sheet1[Id],Sheet1[Id],"ODD")
Read Microsoft Power BI Card – How to use
Example 14: Using Power BI VALUE Function
Here we will see how to use the VALUE function in power bi desktop with an example.
We will use the below sample table, to convert the number format to the text string.
Now we will create a calculated column, to change the text to the number format. So click on the Modelling tab -> New column.
Then write the below Dax formula:
Column = VALUE(MAX(Sheet1[Id]))
You may also like the following Power BI tutorials:
- How to set default value in Power BI Slicer
- Power bi treemap – How to use
- How to use Power bi ribbon chart
In this Power Bi tutorial, we discussed how to use various Power Bi Text functions with examples. And also discuss the below points:
- What is Power BI Text Function
- List of Power Bi Text Function
- Power Bi Text Function Example
Bhawana Rathore is a Microsoft MVP (3 times in Office Apps & Services) and a passionate SharePoint Consultant, having around 10 years of IT experience in the industry, as well as in .Net technologies. She likes to share her technical expertise in EnjoySharePoint.com and SPGuides.com