Menu
×
   ❮   
HTML CSS JAVASCRIPT SQL PYTHON JAVA PHP C C++ C# AWS W3.CSS HOW TO BOOTSTRAP REACT MYSQL JQUERY EXCEL XML DJANGO NUMPY PANDAS NODEJS DSA TYPESCRIPT ANGULAR ANGULARJS GIT POSTGRESQL MONGODB ASP AI R GO KOTLIN SWIFT SASS VUE GEN AI SCIPY CYBERSECURITY DATA SCIENCE INTRO TO PROGRAMMING HTML & CSS BASH RUST TOOLS MATH

Power BI Power Query Split and Merge Columns


Share

One Column, Two Facts

The Location column holds two things at once: the store and the region, for example Lilycove City - Hoenn.

That makes it hard to use.

You cannot add a slicer for the region, or compare the two stores in Hoenn, while both facts share one column.

The rule is simple: one fact per column.

In this chapter you split Location into Store and Region.

Then you see the opposite, Merge Columns.

This page continues the cleanup from Power Query Filter Rows.

Open pq_cleanup.pbix, the report you saved there, select Home > Transform data, and select the last step of the poke_mart_raw_2024 query, Removed Duplicates.

The query has 1,276 rows:

123OrderIDDATEOrderDateABCLocationABCProduct123Quantity123UnitPrice123Sales
10011/1/2024Lilycove City - HoennHyper Potion112001200
10021/1/2024Cerulean City - Kantopoke ball192003800
10031/2/2024Lilycove City - Hoenn  Great Ball 136007800
10041/2/2024Cerulean City - KantoAntidote9100900
10051/3/2024Violet City - JohtoPotion193005700
10061/3/2024Slateport City - HoennParalyze Heal1200200
.....................

Split Column by Delimiter

A delimiter is the text that separates the parts of a value.

In Location, the store and the region are separated by a space, a hyphen and another space: - .

  1. Select the Location column.
  2. On the Home tab, select Split Column > By Delimiter.
  3. The Split Column by Delimiter dialog opens. Power Query has already chosen Space as the delimiter. Under Select or enter delimiter, choose --Custom-- instead.
  4. In the box below it, type a space, a hyphen and a space: - .
  5. Under Split at, keep Each occurrence of the delimiter, which is already selected.
  6. Select OK.

The Split Column by Delimiter dialog in Power Query with Custom selected, space hyphen space typed as the delimiter, Each occurrence of the delimiter selected under Split at, and Advanced options expanded to show Split into Columns and 2 columns

You also find Split Column on the Transform tab and in the menu when you right-click the column header.

Location is replaced by two new columns, Location.1 and Location.2, in the same place:

123OrderIDDATEOrderDateABCLocation.1ABCLocation.2ABCProduct123Quantity123UnitPrice123Sales
10011/1/2024Lilycove CityHoennHyper Potion112001200
10021/1/2024Cerulean CityKantopoke ball192003800
10031/2/2024Lilycove CityHoenn  Great Ball 136007800
10041/2/2024Cerulean CityKantoAntidote9100900
10051/3/2024Violet CityJohtoPotion193005700
10061/3/2024Slateport CityHoennParalyze Heal1200200
........................

Power Query names the new columns after the old one, with a dot and a number.

Look at Applied Steps: there are two new steps.

Split Column by Delimiter does the split, and Changed Type2 sets the type of the new columns to Text.

The split step has a formula similar to this:

= Table.SplitColumn(#"Removed Duplicates", "Location", Splitter.SplitTextByDelimiter(" - ", QuoteStyle.Csv), {"Location.1", "Location.2"})

Note: Include the spaces in the delimiter.

If you split at the hyphen alone, the store becomes "Lilycove City " with a space at the end, and the region becomes " Hoenn" with a space at the start.

You can hardly see those spaces in the preview, but " Hoenn" and "Hoenn" are different values.

A region from this query would then not match the same region in another table.

Split At: The Three Options

The Split at options matter when a value contains the delimiter more than once.

Suppose a value had two delimiters, like Poke Mart - Lilycove City - Hoenn:

Split atResult
Left-most delimiter2 columns: Poke Mart | Lilycove City - Hoenn
Right-most delimiter2 columns: Poke Mart - Lilycove City | Hoenn
Each occurrence of the delimiter3 columns: Poke Mart | Lilycove City | Hoenn

Every Location value in the Poke Mart file has exactly one delimiter, so all three options give the same two columns here.

Under Advanced options you can split into Rows instead of columns, and see the Number of columns to split into.

Power Query fills in that number from the data it sees, here 2. The number is saved in the step.

If a later file had a value with more parts, the extra parts would be ignored.



Rename the New Columns

  1. Double-click the Location.1 header, type Store and press Enter.
  2. Double-click the Location.2 header, type Region and press Enter.

Power Query adds a step called Renamed Columns1.

It gets the number 1 because the query already has a step called Renamed Columns.

123OrderIDDATEOrderDateABCStoreABCRegionABCProduct123Quantity123UnitPrice123Sales
10011/1/2024Lilycove CityHoennHyper Potion112001200
10021/1/2024Cerulean CityKantopoke ball192003800
10031/2/2024Lilycove CityHoenn  Great Ball 136007800
10041/2/2024Cerulean CityKantoAntidote9100900
10051/3/2024Violet CityJohtoPotion193005700
10061/3/2024Slateport CityHoennParalyze Heal1200200
10071/3/2024Lilycove CityHoennPARALYZE HEAL92001800
10081/4/2024Goldenrod CityJohtoSuper Potion67004200
........................

Open the filter list of Region.

There are four values, not three: Hoenn, Johto, Kanto and Hoen.

Hoen is a typo in 11 rows.

Now that the region has its own column, the typo is easy to see.

You fix it in the next chapter.


Merge Columns

Merge Columns is the opposite of a split.

It joins two or more text columns into one, with a separator between the values.

Try it by turning Store and Region back into one Location column:

  1. Select the Store header, hold Ctrl, and select the Region header. The order matters: the column you select first comes first.
  2. On the Transform tab, select Merge Columns.
  3. The Merge Columns dialog opens with Separator set to --None--. Choose --Custom--, and in the box below it type a space, a hyphen and a space: - .
  4. In New column name (optional), replace Merged with Location.
  5. Select OK.

The Merge Columns dialog in Power Query with the Separator set to Custom, space hyphen space typed as the separator, and Location as the new column name

Store and Region are replaced by one text column, Location, and a step called Merged Columns is added.

The values are the same as in the original file:

StoreRegionLocation (merged)
Lilycove CityHoennLilycove City - Hoenn
Cerulean CityKantoCerulean City - Kanto
Violet CityJohtoViolet City - Johto

Merge Columns is on two tabs, and they do different things:

  • Transform > Merge Columns replaces the selected columns with the merged column.
  • Add Column > Merge Columns keeps the selected columns and adds the merged column as a new one.

You can only merge text columns.

In Excel, the CONCAT function does a similar job with a formula.

Delete the Merge Step

This was only a demo.

The report needs Store and Region as separate columns.

In Applied Steps, select the X next to Merged Columns.

Store and Region are back, and the last step is Renamed Columns1 again.


Where You Are Now

The query has 1,276 rows and eight columns:

ColumnType
OrderIDWhole Number
OrderDateDate
StoreText
RegionText
ProductText
QuantityWhole Number
UnitPriceWhole Number
SalesWhole Number

These are the Applied Steps so far:

  1. Source
  2. Promoted Headers
  3. Changed Type
  4. Removed Columns
  5. Renamed Columns
  6. Added Custom
  7. Changed Type1
  8. Filtered Rows
  9. Removed Duplicates
  10. Split Column by Delimiter
  11. Changed Type2
  12. Renamed Columns1

Two problems are left in this query: the typo Hoen in Region, and the product names that are written in different ways.

You fix both in the next chapter, Replace and Fill.

Select Close & Apply, then File > Save to save pq_cleanup.pbix.



Free account

Track your progress

XP earned 0 Day streak 0 Spaces 0 League —

New

Learn in Adventure Mode W3Schools Adventure App

Coding fundamentals as bite-sized lessons and challenges.

×

Contact Sales

If you want to use W3Schools services as an educational institution, team or enterprise, send us an e-mail:
sales@w3schools.com

Report Error

If you want to report an error, or if you want to make a suggestion, send us an e-mail:
help@w3schools.com

W3Schools is optimized for learning and training. Examples might be simplified to improve reading and learning. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. While using W3Schools, you agree to have read and accepted our terms of use, cookies and privacy policy.

Copyright 1999-2026 by Refsnes Data. All Rights Reserved. W3Schools is Powered by W3.CSS.