Skip to main content
Power BI Interview Questions

Extract Text in Power BI

Table of Contents

This function is same as the name Extract text in Power BI. We can pull out the favorable information from a column by using this function.

You can find Extract Text in the text column section of Transform Ribbon of Power Query Editor.

Extract Text In power BI ribbon

We have some following ways to extract text in power bi

  1. Length
  2. First Characters
  3. Last Characters
  4. Range
  5. Text Before Delimiter
  6. Text After Delimiter
  7. Text Between Delimiters

Note: – Power Query Turns the column into string data type even if it has integer values or any other data type, that is why we are using extract text even though a column has values other than string data type.

Let’s Look deep into the ways to extract text in Power BI: –

Extract Length

By using this function, we can extract the length of a string in the selected column.

    Let’s see an Example

    As you can see below, we have a HR data set in this we will extract the length of selected column values.

    Extract Length Sample Data

    Click on the Extract and then Length, let’s see the following output.

    Extract Length Output

    As you can see, we have 4 where the value was Male and 6 where we had Female.

    Note: only in the length function the data type of the column converts to integer/ Whole Number.

    First Characters

    It is used to pull out the first n characters from a string.

    Let’s Pull out the first character of gender because we can identify the gender by M or F as we have only 2 genders in this data set, it will reduce the size of our data.

    Extract First Characters Output
    Last Characters

    It is used to pull out the first n characters from a string.

    Let’s Extract the 4 digits of Date of Join column so only the year will remain in the column.

    Extract last Characters
      Range

      We can pull out the number of characters from between of a string. In the Emp Id Column we have “AC”, and every employee ID has 6 characters. Let’s Pull out the Range of 4 Characters Which Starts after 2nd Position.

      Extract Length Sample Data

        Select Extract Option from the Transform Column, a dialogue box will appear where we must input Starting Index and Number of Characters.

        Extract text Range Dialogue Box

        Below is the Output where “AC” is removed, and we have only 4 digits.

        Extract Text Range Output
        Text Before Delimiter

        We can pull out the characters or text which are before delimiter such as Commas (,), Full Stops(.), Colons (:), Semicolons (;), Slashes (/), Questions Marks (?), Asterisks (*), Backslashes (\), Quotation Marks (“), Hashtags (#).

        We have decimal values in the Age column, the more appropriate way is to use round off to pull out the age but for this example let’s use This function.

        Text Before Delimiter Sample data

        After selecting the text before delimiter, a new dialogue box will open where we have to input delimiter which here is Commas (.).

        Below is the following output of Text Before Delimiter.

        Extract Text Before Delimiter Output
        Text After Delimiter

        We can pull out the characters or text which are after delimiter. It is the vice versa of the Above function.

        Text Between Delimiters

        We can pull out the text or characters between two delimiters.

        By this we will extract the values between two delimiters. Let’s Pull out the day from the date of Join Column which has 2 slashes as delimiters. Let’s Input the delimiters in the dialogue box.

        Extract Text Between Delimiters Dialogue Box

        After clicking okay you can see the following output where Day from date of join has been pulled out.

        Extract text Between Delimiters Output

        Tags:

        Add comment