Splitting, Merging, and Extracting Column Values in Power BI

Course: Microsoft Power BI Training

Topic: Splitting, Merging, and Extracting Column Values

Course Link: Microsoft Power BI Training

Demo Link: Register for a Course Demo

JustAcademy Website: Visit JustAcademy

1. Introduction to Splitting, Merging, and Extracting Column Values

Data transformation is an essential part of preparing data for analysis in Microsoft Power BI. Data imported from Excel files, CSV files, databases, and other sources may contain multiple values in a single column, information distributed across several columns, or text that needs to be extracted for reporting.

Power Query Editor provides transformation tools that help analysts split columns, merge columns, and extract specific portions of column values. These operations make data easier to organize, analyze, and use in Power BI reports and dashboards.

The three main operations covered in this topic are:

• Splitting columns: Dividing one column into multiple columns or rows based on a delimiter, character position, or other rule.
• Merging columns: Combining values from two or more columns into a single column.
• Extracting column values: Retrieving specific text, characters, or portions of a value from an existing column.

1.1 Example

Suppose a customer dataset contains a Full Name column with values such as "Rahul Sharma" and an Email column containing "[email protected]". You may need to split the full name into First Name and Last Name, merge address components into a complete address, or extract the domain from an email address.

2. Learning Objectives

After completing this topic, learners will be able to:

• Understand column transformation in Power Query Editor.
• Split a column using a delimiter.
• Split columns by character positions.
• Split columns into rows or columns.
• Merge multiple columns using separators.
• Extract text before or after a delimiter.
• Extract the first, last, or middle characters from a value.
• Extract text ranges and lengths.
• Use Power Query M functions for text transformations.
• Handle null values, blank strings, and inconsistent text.
• Apply column transformations to practical business datasets.
• Validate the results before loading data into Power BI.

3. Understanding Power Query Editor

Power Query Editor is the data preparation environment in Power BI Desktop. It allows users to connect to data sources, clean records, transform columns, and prepare data before loading it into the semantic model.

3.1 Steps to Open Power Query Editor

1. Open Power BI Desktop.
2. Choose Home from the ribbon.
3. Select Get Data to import a dataset, or open an existing query through Transform Data.
4. Select the required data source, such as Excel, CSV, or SQL Server.
5. Choose Transform Data.
6. Select the table or query that contains the columns to transform.

3.2 Important Areas of Power Query Editor

4. Understanding Column Splitting

4.1 What Is Column Splitting?

Column splitting is the process of dividing a single column into two or more columns or rows according to a defined rule. It is especially useful when one field contains multiple pieces of information.

For example, a column named Full Name might contain "Priya Patel". Splitting the column by a space can separate the name into two fields.

4.2 Why Is Column Splitting Important?

• Separates combined information into individual fields.
• Makes filtering and grouping easier.
• Supports better data modeling.
• Helps extract customer, product, and transaction details.
• Improves the usefulness of data in report visuals.
• Enables more accurate analysis of individual values.

4.3 Common Examples of Column Splitting

5. Splitting Columns by Delimiter

5.1 What Is a Delimiter?

A delimiter is a character or sequence of characters used to separate pieces of information. Common delimiters include spaces, commas, hyphens, underscores, semicolons, and symbols such as @.

For example, in the value "Mumbai, Maharashtra", the comma is the delimiter between the city and state.

5.2 Steps to Split a Column by Delimiter

1. Open Power Query Editor.
2. Select the column you want to split.
3. Open the Transform tab or the column's context menu.
4. Choose Split Column and select By Delimiter.
5. Choose the delimiter, such as a space, comma, or hyphen.
6. Select the splitting option, such as at each occurrence or at a specified occurrence.
7. Choose whether to split into columns or rows, where the selected option is available.
8. Confirm the transformation and inspect the output.

5.3 Example: Splitting Full Names

Before splitting:

Choose Split Column by Delimiter and select the space character.

After splitting:

Rename the resulting columns to First Name and Last Name.

Important: Splitting names by spaces can produce unexpected results for people with middle names, compound surnames, or inconsistent spacing. Choose a rule that matches the source data.

5.4 Example: Splitting Email Addresses

Consider the following email addresses:

Split the Email column at the @ delimiter.

The first column contains the local part of the email address, while the second contains the domain. In production data, validate email formats before relying on this split.

6. Splitting at Each Occurrence of a Delimiter

Power Query can split a column at every occurrence of a selected delimiter. This is useful when a value contains multiple pieces of structured information.

6.1 Example: Splitting Product Codes

Suppose a Product Code column contains values such as PROD-ELEC-101.

Splitting at each hyphen produces the following structure:

6.2 Procedure

1. Select the Product Code column.
2. Choose Split Column by Delimiter.
3. Select the hyphen as the delimiter.
4. Choose the option to split at each occurrence of the delimiter.
5. Confirm the transformation.
6. Rename the output columns to Product Prefix, Category Code, and Product Number.

Ensure that the product codes follow a consistent pattern. Values with additional or missing delimiters may produce different output structures or null values in some resulting columns.

7. Splitting at the Left-Most or Right-Most Delimiter

Sometimes a column contains multiple delimiters, but only one is needed to separate the required information.

7.1 Split at the Left-Most Delimiter

This option splits a value at the first occurrence of the delimiter.

Example: "Sales-West-2026" split at the left-most hyphen produces:

7.2 Split at the Right-Most Delimiter

This option splits a value at the final occurrence of the delimiter.

Example: "Sales-West-2026" split at the right-most hyphen produces:

7.3 When Should These Options Be Used?

• Use the left-most delimiter when the first section identifies a category or prefix.
• Use the right-most delimiter when the final section identifies a year, code, or sequence number.
• Inspect the data to confirm that the delimiter occurs in the expected positions.

8. Splitting Columns by Character Positions

8.1 What Is Fixed-Position Splitting?

Fixed-position splitting divides a value according to character positions rather than a delimiter. It is useful when source data follows a consistent fixed-width format.

For example, a code such as 20261021ABC might contain a date followed by a three-character category code.

8.2 Example

Suppose the column contains the values below:

If the first eight characters represent a date and the final three represent a category code, split after the eighth character.

8.3 Steps to Split by Character Positions

1. Select the column containing fixed-width values.
2. Choose Split Column.
3. Select By Number of Characters.
4. Specify the character count.
5. Choose the required split arrangement, such as once or repeatedly, where available.
6. Confirm and inspect the resulting columns.

This method is most suitable when every record follows the same format. If character lengths vary, use a delimiter or another rule-based transformation instead.

9. Splitting One Column Into Rows

Sometimes a single cell contains multiple items that should be represented as separate records rather than separate columns.

9.1 Example

Consider a customer table where the Products Purchased column contains comma-separated products.

Splitting the Products Purchased column into rows produces:

9.2 Steps to Split Into Rows

1. Select the column containing multiple values.
2. Choose Split Column by Delimiter.
3. Select the delimiter, such as a comma.
4. Open Advanced options if available.
5. Choose the option to split into rows.
6. Confirm the transformation.
7. Check that the original customer or transaction identifiers are repeated for each resulting row.

9.3 Business Applications

• Separating multiple product tags.
• Expanding lists of categories.
• Preparing survey responses with multiple selections.
• Analyzing keywords associated with products.
• Transforming multi-value attributes into a more analysis-friendly structure.

Important: Splitting a column into rows increases the row count. Review how this affects totals and measures so that the same original amount is not accidentally counted multiple times.

10. Understanding Column Merging

10.1 What Is Column Merging?

Column merging combines values from two or more columns into one new or existing column. A separator can be inserted between values to improve readability.

For example, First Name and Last Name can be combined into Full Name. Similarly, City and State can be combined into Location.

10.2 Why Merge Columns?

• Creates complete names or addresses.
• Combines related information for display.
• Builds readable labels from multiple fields.
• Creates composite text identifiers where appropriate.
• Prepares values for exports or downstream applications.

10.3 Steps to Merge Columns

1. Open Power Query Editor.
2. Select the columns to merge using Ctrl-click.
3. Open the Transform tab.
4. Choose Merge Columns.
5. Select a separator, such as a space, comma, hyphen, or custom separator.
6. Enter a name for the resulting column.
7. Confirm the operation.
8. Review the combined values.

The interface may offer Merge Columns from a context menu or another ribbon location, depending on the Power BI version.

11. Merging Columns With a Separator

11.1 Merging First Name and Last Name

Suppose a customer table contains the following columns:

Select both columns and use Merge Columns with a space separator.

11.2 Merging City and State

Suppose a location table contains City and State columns. Merge them using a comma and space separator.

11.3 Common Separators

12. Merging Columns Versus Adding a Custom Column

Power Query offers more than one way to combine values. The correct method depends on whether you want to replace selected columns or preserve the original fields.

If the original columns are needed for filtering, grouping, or further calculations, consider using Add Column > Custom Column rather than replacing them with a merged column.

13. Understanding Column Extraction

13.1 What Is Extracting Column Values?

Extracting column values means retrieving a specific portion of an existing value without necessarily splitting the entire column into multiple fields.

For example, a Product Code column may contain "PROD-2026-105". You may need only the year, the final identifier, or the prefix.

13.2 Common Extraction Operations

14. Extracting the First Characters

14.1 Purpose

Extracting the first characters is useful when the beginning of a value contains a prefix, region code, category identifier, or date component.

14.2 Example

Suppose the Product Code column contains:

Extracting the first four characters produces:

14.3 Steps

1. Select the required text column.
2. Open the Transform tab.
3. Choose Extract.
4. Select First Characters.
5. Enter the required number of characters.
6. Confirm the transformation and inspect the results.

If you need to preserve the original value, use the corresponding options from the Add Column tab or duplicate the column before applying the transformation.

15. Extracting the Last Characters

Extracting the last characters is useful when the end of a value contains a sequence number, short identifier, or code.

15.1 Example

Consider the following order codes:

Extracting the last three characters produces:

15.2 Steps

1. Select the column.
2. Choose Extract from the Transform tab.
3. Select Last Characters.
4. Specify the number of characters.
5. Apply the transformation.

If the extracted result represents a numeric identifier, change its data type only when numeric interpretation is appropriate. Keep identifiers as text when leading zeros must be preserved.

16. Extracting Text Before or After a Delimiter

16.1 Extracting Text Before a Delimiter

This operation returns the text appearing before a selected delimiter.

Example: Extract text before @ from [email protected].

16.2 Steps to Extract Text Before a Delimiter

1. Select the text column.
2. Open the Transform tab.
3. Choose Extract.
4. Select Text Before Delimiter.
5. Enter the delimiter, such as @ or a hyphen.
6. Choose any relevant occurrence settings offered by the dialog.
7. Apply the transformation.

16.3 Extracting Text After a Delimiter

This operation returns the text appearing after a selected delimiter.

Example: Extract text after @ from an email address.

16.4 Business Applications

• Extracting email usernames and domains.
• Separating product prefixes from identifiers.
• Retrieving department codes from employee IDs.
• Extracting order numbers from transaction references.
• Separating location codes from structured labels.

17. Extracting Text Between Delimiters

Text Between Delimiters is useful when the required information appears between two separators.

17.1 Example

Consider the following Product Reference values:

The year appears between the two hyphens.

17.2 Steps

1. Select the relevant column.
2. Open the Transform tab.
3. Choose Extract.
4. Select Text Between Delimiters if available in the interface.
5. Specify the starting delimiter and ending delimiter.
6. Configure occurrence settings if the value contains multiple delimiters.
7. Apply the transformation and validate the output.

If the interface does not offer the exact extraction option needed, use Split Column, a Custom Column, or a suitable M expression.

18. Extracting a Text Range

Extracting a text range retrieves a specified number of characters beginning at a particular position.

18.1 Example

Suppose a reference code is ABC123XYZ and the numeric identifier occupies the fourth, fifth, and sixth positions.

Power Query M text positions are zero-based. A starting position of 3 refers to the fourth character.

18.2 Steps

1. Select the text column.
2. Choose Extract from the Transform tab.
3. Select Range if available.
4. Enter the starting position and number of characters.
5. Confirm the transformation.
6. Check that the output matches the expected pattern.

19. Extracting the Length of Text Values

Text length is the number of characters in a text value. It can be useful for validating codes, checking identifiers, or identifying inconsistent data.

19.1 Example

19.2 Steps

1. Select the text column.
2. Choose Extract from the Transform tab.
3. Select Length if available.
4. Review the resulting numeric values.
5. Use filters to identify values that do not follow the expected length.

For example, if every product code must contain exactly six characters, values with other lengths can be flagged for review.

20. Splitting, Merging, and Extracting Using Power Query M

Power Query M allows users to perform text transformations through expressions. These transformations can be repeated during refresh, making them useful for regular data preparation.

20.1 Splitting a Column Into Multiple Columns

The Table.SplitColumn function can split a column using a specified splitter.

let
    Source = Excel.CurrentWorkbook(){[Name="CustomerData"]}[Content],
    SplitName = Table.SplitColumn(
        Source,
        "Full Name",
        Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv),
        {"First Name", "Last Name"}
    )
in
    SplitName

This example splits Full Name at each space and assigns the resulting columns the names First Name and Last Name. It assumes that each name contains exactly two parts. Names containing middle names, compound surnames, or inconsistent spaces require a more suitable rule.

20.2 Splitting a Column Into Rows

let
    Source = Excel.CurrentWorkbook(){[Name="CustomerData"]}[Content],
    SplitProducts = Table.ExpandListColumn(
        Table.TransformColumns(
            Source,
            {
                {
                    "Products",
                    each if _ = null then {} else List.Transform(Text.Split(_, ","), Text.Trim),
                    type list
                }
            }
        ),
        "Products"
    )
in
    SplitProducts

This example splits comma-separated values in Products into a list, trims spaces, and expands the list into separate rows. Null product lists are converted to empty lists, so the corresponding records may not remain in the expanded output. If every customer row must be retained, handle nulls according to the intended reporting requirements.

20.3 Merging Columns With Text.Combine

let
    Source = Excel.CurrentWorkbook(){[Name="CustomerData"]}[Content],
    AddedFullName = Table.AddColumn(
        Source,
        "Full Name",
        each Text.Combine(
            List.RemoveNulls({[First Name], [Last Name]}),
            " "
        ),
        type text
    )
in
    AddedFullName

This example creates a new Full Name column using First Name and Last Name, separated by a space. Null values are excluded from the combined list. The original columns remain in the table.

20.4 Extracting Text Before a Delimiter

let
    Source = Excel.CurrentWorkbook(){[Name="CustomerData"]}[Content],
    ExtractedUsername = Table.AddColumn(
        Source,
        "Username",
        each
            if [Email] = null then null
            else if Text.Contains([Email], "@")
            then Text.BeforeDelimiter([Email], "@")
            else null,
        type text
    )
in
    ExtractedUsername

This example creates a Username column by extracting the text before @ in an Email column. Missing or malformed email values return null.

20.5 Extracting Text After a Delimiter

let
    Source = Excel.CurrentWorkbook(){[Name="CustomerData"]}[Content],
    ExtractedDomain = Table.AddColumn(
        Source,
        "Email Domain",
        each
            if [Email] = null then null
            else if Text.Contains([Email], "@")
            then Text.AfterDelimiter([Email], "@")
            else null,
        type text
    )
in
    ExtractedDomain

This expression extracts the domain from an email address. Validate email formats before using the result for business decisions.

20.6 Extracting the First Characters

let
    Source = Excel.CurrentWorkbook(){[Name="ProductData"]}[Content],
    ExtractedPrefix = Table.AddColumn(
        Source,
        "Prefix",
        each if [ProductCode] = null then null else Text.Start([ProductCode], 4),
        type text
    )
in
    ExtractedPrefix

Text.Start returns the first four characters from ProductCode. Change the number to match the required prefix length.

20.7 Extracting the Last Characters

let
    Source = Excel.CurrentWorkbook(){[Name="ProductData"]}[Content],
    ExtractedSuffix = Table.AddColumn(
        Source,
        "Suffix",
        each if [ProductCode] = null then null else Text.End([ProductCode], 3),
        type text
    )
in
    ExtractedSuffix

Text.End returns the last three characters from ProductCode.

20.8 Extracting a Text Range

let
    Source = Excel.CurrentWorkbook(){[Name="ProductData"]}[Content],
    ExtractedCode = Table.AddColumn(
        Source,
        "Numeric Code",
        each if [ProductCode] = null then null else Text.Middle([ProductCode], 3, 3),
        type text
    )
in
    ExtractedCode

Text.Middle extracts three characters beginning at the zero-based position 3. In the value ABC123XYZ, this returns 123.

20.9 Calculating Text Length

let
    Source = Excel.CurrentWorkbook(){[Name="ProductData"]}[Content],
    AddedLength = Table.AddColumn(
        Source,
        "Code Length",
        each if [ProductCode] = null then null else Text.Length([ProductCode]),
        Int64.Type
    )
in
    AddedLength

This creates a numeric column showing the character length of each ProductCode value.

21. Handling Nulls, Blanks, and Inconsistent Text

Text transformations can produce errors or unexpected results when values are missing, contain additional spaces, or follow inconsistent formats.

21.1 Handling Null Values

Before splitting, merging, or extracting text, check whether the source column contains null values. Decide whether to preserve missing values, replace them with a justified placeholder, or flag them for review.

21.2 Removing Leading and Trailing Spaces

Text values may contain unnecessary spaces before or after the actual text. These spaces can affect splitting, comparisons, and grouping.

Use the Trim operation in Power Query to remove leading and trailing whitespace.

let
    Source = Excel.CurrentWorkbook(){[Name="CustomerData"]}[Content],
    TrimmedNames = Table.TransformColumns(
        Source,
        {
            {
                "Full Name",
                each if _ = null then null else Text.Trim(_),
                type text
            }
        }
    )
in
    TrimmedNames

21.3 Cleaning Non-Printing Characters

Some imported text contains non-printing characters that make values difficult to compare or display. Power Query's Clean transformation or Text.Clean function can help remove certain non-printing characters.

let
    Source = Excel.CurrentWorkbook(){[Name="CustomerData"]}[Content],
    CleanedNames = Table.TransformColumns(
        Source,
        {
            {
                "Full Name",
                each if _ = null then null else Text.Clean(_),
                type text
            }
        }
    )
in
    CleanedNames

Trimming and cleaning are different operations. Trim removes leading and trailing whitespace, while Clean removes certain non-printing characters. Depending on the source, both may be useful.

21.4 Safe Extraction With Missing Values

If a column contains null values, add conditional logic before applying text functions.

let
    Source = Excel.CurrentWorkbook(){[Name="CustomerData"]}[Content],
    ExtractedUsername = Table.AddColumn(
        Source,
        "Username",
        each
            if [Email] = null then null
            else if Text.Contains([Email], "@")
            then Text.BeforeDelimiter([Email], "@")
            else null,
        type text
    )
in
    ExtractedUsername

This example returns null when Email is missing or does not contain @. Invalid email values should still be reviewed separately if email quality is important.

22. Complete Practical Example: Customer Data Transformation

A company receives customer data from an online registration system. The source table includes Full Name, Email, Location, and Product Interests. The company wants to create separate name fields, extract email domains, separate city and state, and expand product interests for analysis.

22.1 Original Dataset

22.2 Step 1: Split Full Name

Select Full Name and split it using the space delimiter. Rename the resulting fields First Name and Last Name. Confirm that the naming pattern is consistent before applying the transformation to the entire dataset.

22.3 Step 2: Extract Email Domain

Create a new Email Domain column by extracting the text after @. This can be used to analyze registrations by email provider or organization domain.

22.4 Step 3: Split Location

Split Location using the comma delimiter. Rename the resulting fields City and State. Trim any spaces remaining around the extracted values.

22.5 Step 4: Expand Product Interests

Split Product Interests into rows using the comma delimiter. This creates a separate record for each customer-product interest combination.

22.6 Expected Output

The transformed dataset is now suitable for analyzing product interests by customer, city, state, and email domain. Since splitting Product Interests into rows creates multiple rows per customer, avoid counting repeated customer attributes as independent customer records.

23. Common Mistakes to Avoid

24. Best Practices for Professional Power BI Projects

• Inspect sample values before selecting a splitting or extraction rule.
• Use delimiters when they reliably separate the intended information.
• Use fixed-position splitting only for consistent fixed-width data.
• Rename generated columns immediately to make their meaning clear.
• Use Merge Columns when a single combined field is required.
• Use a Custom Column when original columns should remain available.
• Trim text after splitting when delimiters leave extra spaces.
• Handle nulls and malformed text explicitly.
• Preserve leading zeros in codes by retaining text data types where appropriate.
• Validate row counts after splitting values into rows.
• Review totals and aggregation logic after changing the grain of a table.
• Use Power Query M for repeatable transformations when the workflow requires it.
• Review Applied Steps and test the query with new source data before publishing reports.

25. Practical Exercises

Exercise 1: Split Customer Names

Create a customer table containing Full Name, Email, and City.

• Split Full Name into First Name and Last Name.
• Inspect names containing additional spaces or more than two parts.
• Rename the generated columns.
• Compare the original and transformed results.

Exercise 2: Merge Address Fields

Create a dataset containing House Number, Street, City, State, and Postal Code.

• Merge the required fields into a readable address.
• Choose suitable separators between address components.
• Decide whether to preserve the original columns.
• Check for null values and unnecessary spaces.

Exercise 3: Extract Product Codes

Create a Product Code column containing values such as ELEC-2026-101 and FURN-2025-205.

• Extract the prefix before the first hyphen.
• Extract the year between the hyphens.
• Extract the final product number.
• Validate the output for inconsistent code formats.

Exercise 4: Split Multiple Values Into Rows

Create a customer table containing a Product Interests column with comma-separated items.

• Split Product Interests into rows.
• Trim spaces around the product names.
• Verify that customer identifiers are preserved.
• Explain how the new row count affects customer counts and other measures.

Exercise 5: Use Power Query M

Use the Advanced Editor to perform the following tasks:

• Split Full Name into two columns.
• Create an Email Domain column.
• Extract a product prefix.
• Merge City and State into a Location column.
• Handle missing text values safely.

26. Interview Questions and Answers

Q1. What is column splitting in Power BI?

Answer: Column splitting divides a single column into multiple columns or rows using a delimiter, character position, or another defined rule.

Q2. What is a delimiter?

Answer: A delimiter is a character or sequence of characters that separates values, such as a comma, space, hyphen, or @ symbol.

Q3. How do you split a column by delimiter?

Answer: Select the column, choose Split Column by Delimiter, specify the delimiter and splitting options, and apply the transformation.

Q4. What is fixed-position splitting?

Answer: Fixed-position splitting divides a value according to character positions rather than a delimiter. It is useful for consistently formatted codes.

Q5. What is the difference between splitting into columns and splitting into rows?

Answer: Splitting into columns creates separate fields within the same record. Splitting into rows creates multiple records from one field containing multiple values.

Q6. What is column merging?

Answer: Column merging combines values from multiple columns into one column, optionally using a separator.

Q7. What is the purpose of a separator during merging?

Answer: A separator makes the combined result readable, such as a space between first and last names or a comma between city and state.

Q8. What is column extraction?

Answer: Column extraction retrieves a specified portion of a value, such as its first characters, last characters, or text before or after a delimiter.

Q9. What is the purpose of Text.BeforeDelimiter?

Answer: Text.BeforeDelimiter returns the text appearing before a specified delimiter.

Q10. What is the purpose of Text.AfterDelimiter?

Answer: Text.AfterDelimiter returns the text appearing after a specified delimiter.

Q11. What is the purpose of Text.Start?

Answer: Text.Start returns the specified number of characters from the beginning of a text value.

Q12. What is the purpose of Text.End?

Answer: Text.End returns the specified number of characters from the end of a text value.

Q13. What is the purpose of Text.Middle?

Answer: Text.Middle extracts characters from a specified zero-based starting position for a specified length.

Q14. What is the difference between Merge Columns and a Custom Column?

Answer: Merge Columns combines selected fields into a resulting column, whereas a Custom Column creates a new field using an expression and generally preserves the original columns.

Q15. Why should text be trimmed after splitting?

Answer: Trimming removes leading and trailing whitespace that can interfere with comparisons, grouping, and reporting.

Q16. Why is splitting a column into rows useful?

Answer: It converts multiple values stored in one cell into separate records, allowing each value to be analyzed independently.

Q17. What is the risk of splitting a transaction column into rows?

Answer: It may create repeated transaction-level amounts across multiple rows, which can inflate totals if the aggregation logic is not adjusted.

Q18. How should null values be handled during text transformations?

Answer: Check for null values before applying text functions and decide whether to preserve them, replace them appropriately, or flag them for review.

27. Quick Revision Checklist

• Understand splitting, merging, and extraction.
• Know how to split by delimiter and character position.
• Understand left-most and right-most delimiter options.
• Know when to split values into rows instead of columns.
• Use Merge Columns with an appropriate separator.
• Understand when to use a Custom Column to preserve source fields.
• Extract first characters, last characters, text before a delimiter, and text after a delimiter.
• Understand zero-based positions in Text.Middle.
• Handle nulls, whitespace, and inconsistent formats.
• Use Text.Split, Text.Combine, Text.Start, Text.End, Text.Middle, Text.BeforeDelimiter, and Text.AfterDelimiter appropriately.
• Validate row counts and totals after transformations.

28. Conclusion

Splitting, merging, and extracting column values are essential data transformation skills in Microsoft Power BI. Splitting separates combined information into usable fields, merging combines related values into readable columns, and extraction retrieves specific portions of text for analysis.

Power Query Editor provides convenient graphical tools for these operations, while Power Query M enables repeatable and more advanced transformations. Choosing the right method depends on the structure of the source data, the intended reporting requirements, and the need to preserve original values.

For reliable results, inspect source data, select appropriate delimiters, handle null values, validate transformed fields, and check the effect of row expansion on measures before creating reports.

To continue learning, explore the Microsoft Power BI Training course, register through the JustAcademy course demo registration page, or visit the JustAcademy website.

29. Additional Learning Resources

• Microsoft Learn: Split Columns in Power Query
• Microsoft Learn: Merge Columns in Power Query
• Microsoft Learn: Text Operations in Power Query
• Microsoft Learn: Power Query M Text Functions
• Microsoft Learn: Text.Split Function
• Microsoft Learn: Text.Combine Function