Ravindra BagaleCourses & study guides

7. Data Cleaning A–Z in Power Query

7.11 Merge Columns

By Last updated:

In short: Merge Columns joins text from several columns into one.

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.