Forum Discussion
Split 1 column (Num+Text & Numbers Only) into 2 columns
Hi rphang ,
First, in your expression, one more word was written.
Then, try amitchandak 's latest method to create two columns like so:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjRU0lEyVYrVATKNgExDPQXn/Nzc1LySYoigMVDQCMI0ATJdMlMUSjIyixWSUVSZAqX88hXcUlNTkhKTsyGCZkBBC6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Questions = _t, Answer = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Questions", type text}, {"Answer", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Points", each try Number.FromText([Answer]) otherwise 0), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Open-ended", each if [Points]=0 then [Answer] else "-"), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Questions", type text}, {"Answer", type text}, {"Points", Int64.Type}, {"Open-ended", type text}}) in #"Changed Type1"Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super Userrphang , I think in power query Text.InferNumberType , should help
amitchandak , Thanks for the suggestion. Would you mind if you could show me an example of the function in use? I assume it is to create a custom Column from my "Answers".
Sorry as the documentation of "Text.InferNumberType" is too ambigous for me to take reference on.
https://docs.microsoft.com/en-us/powerquery-m/text-infernumbertype
- amitchandak
rphang , Try like a new column like
points = try Number.FromText([Answer]) otherwise null
- Icey
Community Support
Hi rphang ,
First, in your expression, one more word was written.
Then, try amitchandak 's latest method to create two columns like so:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCjRU0lEyVYrVATKNgExDPQXn/Nzc1LySYoigMVDQCMI0ATJdMlMUSjIyixWSUVSZAqX88hXcUlNTkhKTsyGCZkBBC6XYWAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Questions = _t, Answer = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Questions", type text}, {"Answer", type text}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Points", each try Number.FromText([Answer]) otherwise 0), #"Added Custom1" = Table.AddColumn(#"Added Custom", "Open-ended", each if [Points]=0 then [Answer] else "-"), #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom1",{{"Questions", type text}, {"Answer", type text}, {"Points", Int64.Type}, {"Open-ended", type text}}) in #"Changed Type1"Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.