Level 3 · Split a SKU into the parts you can join on

Codes from other systems pack several facts into one string. LIG-PEN-0127 is Lighting, the Pendants subcategory, product 127. To line the file up with your products table you need the pieces as their own columns, and Luis wants the original kept.

Keep the original: Duplicate column

Most splitting steps replace the column they work on. When the original has to stay, copy it first:

Duplicate column: sku

In Power BI: right-click the sku header → Duplicate Column (or Add Column → Duplicate Column). The copy is called sku - Copy, and the M is Table.DuplicateColumn(Source, "sku", "sku - Copy").

Split by a delimiter

Split column: sku - Copy by "-"

In Power BI: select the column, Home → Split Column → By Delimiter, choose Custom and type -, and keep Each occurrence of the delimiter. The copy becomes three columns named after it with .1, .2, .3:

sku - Copy	→ sku - Copy.1	sku - Copy.2	sku - Copy.3
LIG-PEN-0127	LIG	PEN	0127

The same menu splits By Number of Characters, By Positions, by Lowercase to Uppercase and more, and under Advanced options it can split into Rows instead of columns, useful when one cell holds a list such as red;blue;green.

Name the parts, drop the rest, fix the type

Rename column: sku - Copy.1 to category_code
Remove columns: sku - Copy.2
Rename column: sku - Copy.3 to product_id
Change type: product_id to whole number

In Power BI, Split Column usually adds a Changed Type step by itself and guesses the new columns’ types. Check what it guessed: a code whose leading zeros matter, like a ZIP code, must stay text. Here the zeros are padding, and the product IDs in the model are numbers, so product_id must be a whole number or it will never match.

Extract: one piece without splitting

When you need a single piece, Extract is quicker. Transform → Extract replaces the column with the piece; Add Column → Extract puts it in a new column and keeps the original. The options are Length, First Characters, Last Characters, Range, Text Before Delimiter, Text After Delimiter and Text Between Delimiters. The category code is Text Before Delimiter -:

Add column: category_code = Text.BeforeDelimiter([sku], "-")

and the product number is the last four characters, converted to a number:

Add column: product_id = Number.From(Text.End([sku], 4))

These two lines give the same result as the six steps above, and the checker accepts either. Split is easier to read when you need several pieces; Extract is shorter for one. Both are recorded steps that replay on next month’s file.

Common mistakes

• Splitting the only copy. The SKU is gone and Luis’s support team can’t look anything up.
• Splitting a description by spaces. Dune Black Metal Pendant Light splits into five columns that mean nothing; split codes, not prose.
• Trusting positions forever. Text.End([sku], 4) works while every number has four digits; splitting at the delimiter keeps working if the partner adds a fifth one next year.