Split a combined code at the leftmost delimiter in Power Query when the part after the first separator can itself contain separators. This keeps the remaining text together in a second column.
Try three source values
In Power BI Desktop use Enter data to create a column named RouteCode. Enter East-001, West-045 and East-010-A. Open Transform data and set RouteCode to Text. Select the column, choose Split Column, then By Delimiter. Enter a hyphen as the delimiter and choose the leftmost occurrence.
Verify the exact values
Rename the new columns Region and Route. The expected Region values are East, West and East. The expected Route values are 001, 045 and 010-A. If a generated Changed Type step converts route codes to numbers, edit or remove that step so the leading zeros survive. Add a fourth source row with no hyphen in a practice copy and check how it appears before deciding whether to correct or flag that input. Apply the changes after checking the output preview.
Version and exercise
The example uses Power Query Editor in Power BI Desktop. Power Query Online uses its own interface for the delimiter and split options. What happens to East-010-A when you choose each occurrence instead of the leftmost one?
