Power BI Merge, Append, Replace & Split Columns – Step-by-Step with Examples

  Merge, Append, Replace & Split Columns – Step-by-Step with Examples

Introduction:

Power BI is a powerful tool for analyzing and visualizing data. But before we create reports, we need to prepare and clean the data using Power BI's Power Query Editor.

This article covers 4 essential transformation tools for beginners:

1. Merge Queries – Combine columns from different tables

2. Append Queries – Stack data from different tables

3. Replace Values – Clean or correct values

4. Split Columns – Break one column into multiple parts

Why You Need Data Transformation in Power BI:

Reason	Explanation
To combine data	You may have employee data in one table and salary data in another
To clean mistakes	Some columns may have spelling mistakes or inconsistent terms
To break values	Full names may need to be split into first and last name
To prepare for analysis	Clean data helps you build better visuals and dashboards

Power BI Tools You Will Use:

Tool	Menu Location
Merge Queries	Home → Merge Queries
Append Queries	Home → Append Queries
Replace Values	Right-click on column → Replace
Split Columns	Transform → Split Column

Sample Dataset for Practical

You need to prepare 3 Excel sheets and import them into Power BI:

Download Datasets for Practice

1. EmployeeDetails

2. EmployeeSalary

3. NewEmployees

Step-by-Step Practical Tutorials in Power BI

1. Merge Queries (Join Two Tables)

Goal:

Combine EmployeeDetails and EmployeeSalary using the EmpID column.

Steps:

1. Load both Excel tables into Power BI.

2. Click Home → Transform Data.

3. In Power Query Editor, click Home → Merge Queries → Merge Queries as New.

4. Select:

   • First table: EmployeeDetails

   • Second table: EmployeeSalary

5. Select EmpID column in both.

6. Join Kind: Inner Join.

7. Click OK.

8. Click on the new column header → Click Expand Icon (⏷).

9. Select BasicSalary and Bonus → Click OK.

10. Rename columns if needed.

Now your table has employee name, department, location, and salary.

2. Append Queries (Combine Rows)

Goal:

Combine EmployeeDetails and NewEmployees.

Steps:

1. In Power Query Editor → Click Home → Append Queries → Append Queries as New.

2. Select:

   • Primary table: EmployeeDetails

   • Second table: NewEmployees

3. Click OK.

✅ A new table is created with 20 rows (all employees).

3. Replace Values

Goal:

Change HR to Human Resources for clarity.

Steps:

1. In your combined employee table → Select Department column.

2. Right-click on column → Click Replace Values.

3. In dialog:

   • Value to Find: HR

   • Replace With: Human Resources

4. Click OK.

✅ Now “HR” is changed across the column.

4. Split Column

Goal:

Split the Name column into First Name and Last Name.

 Steps:

1. Select Name column.

2. Go to Transform → Split Column → By Delimiter.

3. Choose delimiter: Space.

4. Click OK.

5. Rename the new columns: First Name, Last Name.

✅ Name is now in two separate columns.

 Power BI Data Transformation – Cheat Sheet (In Sentences)
🔸 Merge Queries :

Use the Merge Queries tool from the Home menu to combine columns from two different tables based on a common column, such as EmpID. This helps bring related data (like salary and department) into a single table.

---

🔸 Append Queries : Use the Append Queries option from the Home menu to stack the rows of one table below another. It is useful when you want to combine two tables with the same structure, such as employee data from different months.

---

🔸 Replace Values : Right-click on a column and choose Replace Values to correct or clean data. For example, you can replace "HR" with "Human Resources" for better clarity in your reports.

---

🔸 Split Column : Go to the Transform menu and use Split Column to break one column into multiple columns using a delimiter such as a space, comma, or dash. This is helpful when separating full names into first and last names.