7. Data Cleaning A–Z in Power Query
7.11 Merge Columns
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
- Hold Ctrl and click Area, City, State in the order you want them joined.
- Add Column › Merge Columns (keeps the originals) or Transform › Merge Columns (replaces them).
- 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
Ek common chuk mhanje 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. Ekdum simple aahe, fakt savay lavun ghya.