Divide one table column in two tables (of one column each)

[LukeReds]

Hi to everyone,

is it possible with power query, starting from the "initial table" (see pic) to get the 2 tables in the pic (names and age), but without adding new columns (no  Table.AddColumn)?

Thank you in advance

[LukeReds]

hi @m_dekorte both solutions functions perfectly, now i have to choose one.. 😄

Thank you very much

[m_dekorte · Accepted solution]

Hi @LukeReds,

2 approaches for you to examine. First, duplicate your initial table query and use a filter, to illustrate:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RY47DoAwDEPv0pkhbhJoz1J16MAI9x+RMMJTnqX4M0a513Uaytw+rEJ/EcZTKd30EMIU7sJD2ISdQbQE84IqqZLK/TdBtdBacC0bm3bOBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    FilterNames = Table.SelectRows(Source, each try if Number.From([Column1]) is number then false else true otherwise true ),
    FilterNumbers = Table.SelectRows(Source, each try if Number.From([Column1]) is number then true else false otherwise false )
in
    FilterNames // FilterNumbers

Select the step FilterNames or FilterNumbers to see each result.

Alternatively you can use Table.Partition to return a list with 2 tables.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("RY47DoAwDEPv0pkhbhJoz1J16MAI9x+RMMJTnqX4M0a513Uaytw+rEJ/EcZTKd30EMIU7sJD2ISdQbQE84IqqZLK/TdBtdBacC0bm3bOBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    Partitions = Table.Partition( Source, "Column1", 2, each try if Number.From(_) is number then 1 else 0 otherwise 0)
in
    Partitions

Then you can right-click in the whitespace beside the nested table

and choose "Add as New Query"

Ps. If this helps solve your query please mark this post as Solution, thanks!