Split By Delimiter Without Changing New Column Type

[joes2007]

I am looking for a way to use power query to split column by delimiter without it automatically changing the new column type based on what it contains - I want it to remain as 'any' for data type.

The reason being, I have an input box that takes in bits of information separated by commas, and then I have power query pick up the data by way of the input box being linked to a one cell table. It can then split the data.

The trouble with it is sometimes the information doesn't come in the same order, so the second bit might be a date, or it might be a name. After I setup PQ to separate by comma it worked fine the first time. After I switched up the information it broke because there was not a date where there once was.

Thoughts?

Best,
Joe

 

[horseyride]

When you split, you usually get two steps -- Table.SplitColumn and Table.TransformColumnTypes

= Table.SplitColumn(Source, "a", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"a.1", "a.2", "a.3"})
= Table.TransformColumnTypes(#"Split Column by Delimiter",{{"a.1", type text}, {"a.2", Int64.Type}, {"a.3", type date}})

or

= Table.SplitColumn(Source, "a", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, false), {"a.1", "a.2
= Table.TransformColumnTypes(#"Split Column by Delimiter",{{"a.1", type text}, {"a.2", type text}})

Just delete the second step, and each split column will be default to type text

= Table.SplitColumn(Source, "a", Splitter.SplitTextByDelimiter("-", QuoteStyle.Csv), {"a.1", "a.2", "a.3"})
or

= Table.SplitColumn(Source, "a", Splitter.SplitTextByEachDelimiter({"-"}, QuoteStyle.Csv, false), {"a.1", "a.2