Level 2 · Split a payment code, combine two columns

Exports from other systems rarely use your ids as they are. The marketplace wraps our order number in a transaction code, TX-2025-008514, and Amanda needs the plain number back to match the report against our orders. That is a Split Column job. Then she needs the opposite: two columns combined into one key.

Split a column by a delimiter

Split column: transaction_id by "-"

Power Query cuts the text at every hyphen and puts each piece in its own column, named after the original with .1, .2, .3:

transaction_id	→ transaction_id.1	transaction_id.2	transaction_id.3
TX-2025-008514	TX	2025	008514

The three new columns take the place of transaction_id, which is gone. Keep the piece you need and remove the others:

Remove columns: transaction_id.1, transaction_id.2
Rename column: transaction_id.3 to order_id

In Excel: select the column, Home → Split Column → By Delimiter, pick --Custom-- and type -, choose Each occurrence of the delimiter. Rename by double-clicking the header.

The number part is still text

008514 is text: the leading zeros give it away, because a number has none. Our order ids are numbers (8514), so text "008514" would never match them in a lookup or a merge. One more step fixes it:

Change type: order_id to whole number

Real Excel usually adds this one by itself: right after a split, Power Query inserts a Changed Type step that guesses the new columns’ types. Always look at what it guessed. A code like 00123 that must keep its zeros (a ZIP code, a SKU) has to stay text.

Combine two columns into a key

The chargeback list identifies an item as 153-C01247: the product id, a hyphen, the customer code. Build the same key with a custom column:

Add column: product_customer = Text.From([product_id]) & "-" & [customer_code]

& joins text. product_id is a number, and M, unlike an Excel cell formula, refuses to join a number to text: [product_id] & "-" is an error on every row. Text.From converts the number to text first. On the first row this gives 153-C01247.

In Excel the point-and-click version is Add Column → Merge Columns: select product_id, then customer_code (the click order is the order in the result), pick a custom separator -, and name the new column. Use the Transform tab’s Merge Columns instead if you want the two originals replaced rather than kept.

The M behind the steps

Table.SplitColumn(Source, "transaction_id",
    Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv),
    {"transaction_id.1", "transaction_id.2", "transaction_id.3"})
Table.TransformColumnTypes(#"Renamed Columns", {{"order_id", Int64.Type}})
Table.AddColumn(#"Changed Type", "product_customer",
    each Text.Combine({Text.From([product_id]), [customer_code]}, "-"), type text)

Int64.Type is a whole number; type number is a decimal.

Common mistakes

• Joining a number with &. Wrap it in Text.From( ) or change its type to text first.
• Keeping all three split columns. The checker, like the person reading your table, only wants the order number.
• Leaving order_id as text. It shows 008514, and a merge against the orders table finds nothing.
• Splitting with a delimiter that also appears inside the data. A product name like Oak-Leaf Vase split by - lands in two columns. Check the values before you pick a delimiter.