Split column into 2 - Query Editor

[Qotsa]

Hi,

Is it possible in Query Editor to split the below text column into 2.

I'd like 1 column to contain the numbers (highlighted in blue in pic below) & the other column to contain the text (highlighted in red).

[v-shex-msft · Accepted solution]

Hi @Qotsa ,

Did you mean extract text value from list? If this is a case, you can try to use following formula:

#"Added Custom" = Table.AddColumn(#"Changed Type", "Number", each try if Number.From([Column1]) <> null then null else [Column1] otherwise [Column1])

Full query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjI2MTJRitWJVjI0NtIztQAzjY1MjIxDXMHslFSQYCwA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Number", each try if Number.From([Column1]) <> null then null else [Column1] otherwise [Column1])
in
    #"Added Custom"

Regards,

Xiaoxin Sheng

[AlB]

Hi @Qotsa 

What exactly would your expected result look like?

You could add two custom columns, one with the rows with numbers  and nulls elsewhere and another one with the text rows and nulls elsewhere. Something like this. Note 'Column1' is the name of the column in the original table. Update that if required.  

let
    Source = PLACE HERE YOUR SOURCE ,
    #"Added Custom" = Table.AddColumn(Source, "Numbers", each if Text.Remove([Column1],{"0".."9", ","})="" then [Column1] else null),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Text", each if Text.Remove([Column1],{"0".."9", ","})<>"" then [Column1] else null),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom1",{{"Numbers", type number}, {"Text", type text}})
in
    #"Changed Type"

You could remove the original column at the end if you need to.