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.

FunctionDescriptionSyntax
COMBINEVALUESThis function joins two or more strings into one stringCOMBINEVALUES(<delimiter>,<expression><expression>[,<expression>])
CONCATENATEThis function joins two strings into one stringCONCATENATE(<text1>, <text2>)
CONCATENATEXThis function concatenates the result of an expression evaluated for each row in a tableCONCATENATEX(<table>, <expression>[, <delimiter> [, <orderby expression> [, <order>]])
ExactThis function compares two text strings and returns true if they are exactly the same, or else it returns falseEXACT(<text1>,<text2>)
FINDThis function returns the starting position of one text string within another text stringFIND(<find text>, <within text>[, <start num>][, <NOTFOUNDVALUE>]])
FIXEDThis function rounds a number to the specified number of decimal and returns the result as textFIXED(<number>,<decimals>, <no commas>)
FormatThis function converts a value to text according to the specified formatFORMAT(<value>, <format strings>[, <local name>])
LENThis function returns the number of characters in a text stringLEN(<text>)
LOWERThis function converts all letters in a text string to lower caseLOWER(<text>)
REPLACEThis function replaces a part of a text string, based on the number of character you specify, with a different text stringREPLACE(<old text>, <start num>,<num_chars>,<new text>)
REPTThis function Rept a text for a given number of timesREPT(<text>,<num times>
SEARCHThis 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>]
SUBSTITUTEThis function replaces existing text within new text in a stringSUBSTITUTE(<text>, <old text>, <new text>,< instance num>)
VALUEThis function converts a text string that represents a number to a numberVALUE(<text>)
Power Bi text function

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.

Power Bi COMBINEVALUES Function
Power Bi COMBINEVALUES Function

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])
Power Bi COMBINEVALUES Function
Power Bi COMBINEVALUES Function

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.

Power Bi CONCATENATE Function
Power Bi CONCATENATE Function

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])
Power Bi CONCATENATE Function
Power Bi CONCATENATE Function

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.

Power Bi CONCATENATEX Function
Power Bi CONCATENATEX Function

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], ",")
Power Bi CONCATENATEX Function
Power Bi CONCATENATEX Function

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.

Power Bi Exact function
Power Bi Exact function

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])
Power Bi Text function
Power Bi Text function

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.

Power Bi Find Function
Power Bi Find Function

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")
Power Bi Find Function
Power Bi Find Function

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.

Power Bi FIXED Function
Power Bi FIXED Function

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)
Power bi text function
Power bi text function

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.

 Power Bi FORMAT Function
Power Bi FORMAT Function

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")
 Power Bi FORMAT Function
Power Bi FORMAT Function

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.

Power Bi Text Function
Power Bi Text Function

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])
 Power Bi LEN function
Power Bi LEN function

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.

Power Bi Text Function
Power Bi Text Function

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])
 Power Bi LOWER Function
Power Bi LOWER Function

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.

 Power BI REPLACE Function
Power BI REPLACE Function

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")
 Power BI REPLACE Function
Power BI REPLACE Function

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().

 Power Bi REPT Function
Power Bi REPT Function

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)
 Power Bi REPT Function
Power Bi REPT Function

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().

Power Bi SEARCH Function
Power Bi SEARCH Function

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)
Power Bi SEARCH Function
Power Bi SEARCH Function

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().

 Power Bi SUBSTITUTE Function
Power Bi SUBSTITUTE Function

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")
 Power Bi SUBSTITUTE Function
Power Bi SUBSTITUTE Function

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.

 Power BI VALUE Function
Power BI VALUE Function

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]))
 Power BI VALUE Function
Power BI VALUE Function

You may also like the following Power BI tutorials:

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
>