Power Bi Text Functions with Examples

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)

Read Power bi slicer checkbox

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