Split Column by Delimiter but Retain Text Format

[dburke]

I am trying to split column text value '04-250-85152-000-00000-00' (an account string) using the Power Query split column function (delimiter is '-').

How can I retain the leading 0 characters for each new column (04 250 85152 000 00000 00) using a single formula?

Power Query interprets the newly created columns as numeric, not text and returns removes leading 0's (returns 4 250 85152 0 0 0)

what I want: 04 250 85152 000 00000 00
what I get: 4 250 85152 0 0 0

A work-around is to update each numeric column to get correct text values after splitting the original column but was hoping to avoid all those steps.

Any suggestions would be appreciated.

Thank you.

 

[MarcelBeug]

If you split the column, Power Query automatically adds a step that changes the column types to numbers.
All you need to do: remove this step.

 

[dburke]

Problem solved. Thank you for helping.

I had not noticed that a new applied step "Changed Type" was automatically added after step "Split Column by Delimiter".

 

[Ken Puls]

FYI, there is an option in the PQ options to disable the auto change type step application. It's unfortunately buried under PQ Options --> Current Workbook --> Data Load --> Automatically detect column types and headers for unstructured sources. (I say unfortunate, as it's a workbook level, not global, setting.)