Forum Discussion
Split column by data type
The simple way of doing this is to:
1. Duplicate the column.
2. Then change the duplicated column to the number type
3. Replace errors with nulls
4. Then add a conditional column with if the duplicated column is null then the origional column, else null.
Using a custom column mentioned above with try would be:
= let parsed = try Number.From([Origional Text]) in [Number = parsed[Value]?, Text = if parsed[HasError] then [Origional Text] else null]Then just expand the record column. If you want to avoid having to set the type of the column, then alter the Added column step to add a 3rd parameter: type [Number = number, Text = text]
waiting for a better or more elegant solution, try this:
Table.AddColumn(Table.AddColumn(yourtab, "num", each if Value.Is([yourMixedCol], type number) then [yourMixedCol] else null), "txt", each if Value.Is([yourMixedCol], type text) then [yourMixedCol] else null)
Hello! Sorry for my ignorance, I´m kind of a noob here. what would I need to write where you wrote your tab? I´m in PowerQuery, so I´ve tried writing the table name, even though I knew that wasn´t it.... I have it like this :
Table.AddColumn(Table.AddColumn(yourtab, "num", each if Value.Is([Name], type number) then [Name] else null), "txt", each if Value.Is([Name], type text) then [Name] else null)
Thanks for your help!!
hi batmanadk,
yourtab in my statemnent is just the result variable of previous step: usully somethink like #"expanded ...".
Then you have only to find this identifier and put in place of yourtab.
good luck
