Ravindra BagaleCourses & study guides मराठी Track your progress

7. Data Cleaning A–Z in Power Query

7.11 Merge Columns

By Last updated:

In short: Merge Columns joins two or more text columns with a separator.

Start with a problem

The delivery app needs one address line like Kothrud, Pune instead of two columns.

Explain simply

Merge Columns joins two or more text columns with a separator. Transform replaces them; Add Column keeps them.

Show the data

Before (after 7.10):

Area City
Kothrud Pune
Gangapur Nashik
Rajarampuri Kolhapur
College Road Nashik

About this data: This table uses columns: Area, City. This table uses columns: Customer ID, Address. This table uses columns: Area, City, State. This table uses columns: Wrong result, Reason, Correction. Values shown are the lesson examples already on this page (not a new dataset).

Fictional sample data. Practice file: pq_customers_text.csv (source: src/practice/).

Demonstrate

  1. Select Area, then Ctrl-click City (order matters).

  2. Add Column › Merge Columns › Separator Custom , › New column name Address › OK.

    Your result should look like this:

    Address
    Kothrud, Pune
    Gangapur, Nashik
    Rajarampuri, Kolhapur
    College Road, Nashik

Show the result

Your result should look like this (fictional sample):

Customer ID Address
C01 Kothrud, Pune
C04 College Road, Nashik

Explain what happened

Merge Columns creates Text.Combine({[Area], [City]}, ", "). Nulls are skipped, so no ", null".

Explore further

  • For numbers, convert to text first (Merge Columns does it automatically).

Detailed reference for this lesson:

Merge Columns joins text from several columns into one.

Before

Area City State
Kothrud Pune Maharashtra
Dharampeth Nagpur Maharashtra
Nirala Bazar Sambhaji Nagar Maharashtra

After

Store Location
Kothrud, Pune, Maharashtra
Dharampeth, Nagpur, Maharashtra
Nirala Bazar, Sambhaji Nagar, Maharashtra

Steps in Power BI

  1. Hold Ctrl and click Area, City, State in the order you want them joined.
  2. Add Column › Merge Columns (keeps the originals) or Transform › Merge Columns (replaces them).
  3. Separator: --Custom-- , (comma + space); New column name: Store Location › OK.
Merged = Table.AddColumn(Source, "Store Location",
    each Text.Combine({[Area], [City], [State]}, ", "), type text)

Tip

Text.Combine skips null values. A Custom Column written as [Area] & ", " & [City] returns null if any part is null. Prefer Merge Columns or Text.Combine when blanks are possible.

Practice task

Create a Map Location column "City, Maharashtra, India" in DarkStore using Merge Columns. You will use it in the map chapter (Module 15).

Ravindra Bagale's Tip

A common mistake is merging columns that contain nulls, which can give unexpected blanks or stray separators. Replace nulls first, or use Text.Combine with a list that skips nulls. Also keep the original columns until you have checked the merged result. It's very simple – just make it a habit.

Fix common mistakes

Wrong result Reason Correction
Pune, Kothrud Selected City first Select in the order you want
Original columns gone Used Transform › Merge Use Add Column › Merge Columns
Double spaces Untrimmed source Trim first

Practise independently

Practice task

Merge First and Last (from 7.10) with a space. What is C04's result?

Hints
  • Last is null.
Solution

"Salman" – the null is skipped.

Think like a professional

Keep merged columns for display only; keep the parts for filtering.

Ravi Sir's Cards for this lesson

Merge columns. Reports often need one label made from two columns.Revision card

मराठी cards