Extracting Substrings: The Power of the MID Function in Power BI

Extracting Substrings: The Power of the MID Function in Power BI

In today's post, I will explain how to use the MID function in Power BI, a powerful tool for extracting specific parts of text strings. Whether you're dealing with product codes, customer IDs, or formatted date strings, the MID function can help you efficiently parse and analyze your data. Join me as we explore the syntax, practical applications, and examples to understand how the MID function can simplify your text data manipulation tasks in Power BI.

The MID function is a powerful text function in Power BI that allows you to extract a substring from a string based on specified starting position and length. This function is particularly useful when you need to manipulate or analyze text data, such as parsing product codes, extracting specific parts of customer information, or formatting data for reporting purposes.

In this blog, I will explain how to use the MID function in Power BI with practical examples and scenarios.

Syntax of the MID Function

The basic syntax for the MID function in Power BI is as follows:

text: The original string from which you want to extract the substring.

start_position: The position in the string where you want to start extracting characters. The position is 1-based, meaning the first character in the string is at position 1.

num_chars: The number of characters you want to extract from the starting position.

Practical Examples

Let's dive into some practical examples to see how the MID function works.

Example 1: Extracting a Substring from a Product Code

Suppose you have a column named ProductCode in your dataset, and you want to extract the first three characters of each product code to identify the product category.

ProductCategory = MID([ProductCode], 1, 3)

In this example:

• ProductCode is the original string.
• 1 is the starting position.
• 3 is the number of characters to extract.

Example 2: Extracting a Specific Part of a Customer ID

Assume you have a column named CustomerID that contains IDs in the format "CUST-XXXX-YYYY". You want to extract the first four characters after the hyphen to analyze customer segments.

CustomerSegment = MID([CustomerID], 6, 4)

In this example:

• CustomerID is the original string.
• 6 is the starting position, right after "CUST-".
• 4 is the number of characters to extract.

Example 3: Formatting a Date String

Imagine you have a column named DateString in the format "YYYYMMDD" and you want to extract the year, month, and day as separate columns.

Extracting Year

Year = MID([DateString], 1, 4)

Extracting Month

Month = MID([DateString], 5, 2)

Extracting Day

Day = MID([DateString], 7, 2)

In these examples:

• DateString is the original string.
• The starting positions are 1, 5, and 7, respectively.
• The number of characters to extract are 4, 2, and 2, respectively.

Illustrative Example of MID Function 

Conclusion

The MID function in Power BI is a versatile tool for text manipulation and extraction. By understanding how to use this function, you can efficiently parse and format text data to meet your reporting and analysis needs. Whether you are dealing with product codes, customer IDs, or date strings, the MID function can help you extract and utilize the information you need.

For more detailed guidance and in-depth training, visit our training here.