Split By Delimiter Without Changing New Column Type

joes2007

New Member
Joined
Jan 10, 2018
Messages
18
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
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
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
 
Upvote 0

Forum statistics

Threads
1,224,845
Messages
6,181,298
Members
453,030
Latest member
PG626

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top