Power BI Users can leverage a number of different DAX functions to extract text from a string or the Power Query Editor. A number of DAX functions are supported that let you select specific parts of a text string. We’ll explain the different formulas you can use and explain when it might be more appropriate to parse text using the Power Query editor in Power BI vs DAX functions.
Table of Contents
Extract Text from Columns with Power BI DAX
DAX supports a number of functions designed to help you extract text from a string. The syntax is generally the same but the application of it will depend on the pattern of text being extracted. Keep in mind that these formulas can be used in conjunction with each other and getting to the exact text you need can be a multi-step process.
When you use DAX to parse text, you will need to create a New Column, instead of a New Measure. Measures are meant for aggregations, such as SUM or AVERAGE. New Columns are assessed on a row by row basis in your dataset.
New Columns are created under the Table Tools Section of the Power BI Ribbon.
Herea the DAX formulas used to extract Text.
LEFT DAX Function
The LEFT function extracts a specified number of characters from the start of a text string.
Example: Extracting “Sales” from “Sales Report”.
ExtractedText = LEFT('Table'[ColumnName], 5)This extracts the first 5 characters, resulting in “Sales”.
RIGHT DAX Function
The RIGHT function extracts a specified number of characters from the end of a text string.
Example: Extracting “Report” from “Sales Report”.
ExtractedText = RIGHT('Table'[ColumnName], 6)This extracts the last 6 characters, resulting in “Report”.
MID DAX Function
The MID function extracts a substring from the middle of a text string, given a starting position and length.
Example: Extracting “Sales” from “Monthly Sales Report”.
ExtractedText = MID('Table'[ColumnName], 9, 5)This extracts 5 characters starting from position 9, resulting in “Sales”.
FIND DAX Function
The FIND function locates the starting position of one text string within another.
Example: Finding the position of “Sales” in “Monthly Sales Report”.
Position = FIND("Sales", 'Table'[ColumnName], 1, 0)This returns the starting position of “Sales” within the text string.
The variables at the end are to define the starting position of the found text, and the second argument is the value to return if the search value is not found.
SUBSTITUTE DAX Function
The SUBSTITUTE function replaces occurrences of a specified text string with another text string.
Example: Replacing “Report” with “Summary” in “Sales Report”.
UpdatedText = SUBSTITUTE('Table'[ColumnName], "Report", "Summary")This changes “Sales Report” to “Sales Summary”.
CONCATENATEX DAX Function
The CONCATENATEX function concatenates the result of an expression evaluated for each row in a table, separated by a specified delimiter.
Example: Concatenating product names with a comma.
ConcatenatedNames = CONCATENATEX('Table', 'Table'[ProductName], ", ")This creates a single string of product names separated by commas.
You can learn more about these different text extraction techniques using DAX here: Text functions (DAX) – DAX | Microsoft Learn
Using Power Query vs DAX for Text Extraction
DAX is very useful for quick text extraction and parsing but can be cumbersome when parsing complex strings. For more control over the extraction process, we recommend using the Power Query Editor. It also has the advantage of making the changes as part of your data model instead of calculating the text as needed in DAX, typically leading to better performance
To learn more about extracting text in Power Query, check out our in-depth article: How to Extract Text from a String in Power Query. We explain how to use Power Query M or the native text extraction features that do not require writing any formulas.