Reply
LukeReds
Helper II
Helper II
Partially syndicated - Outbound

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

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

tab.jpg

1 ACCEPTED SOLUTION
m_dekorte
Super User
Super User

Syndicated - Outbound

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"

m_dekorte_0-1683893291543.png

 

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

View solution in original post

2 REPLIES 2
LukeReds
Helper II
Helper II

Syndicated - Outbound

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

 

Thank you very much

m_dekorte
Super User
Super User

Syndicated - Outbound

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"

m_dekorte_0-1683893291543.png

 

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

avatar user

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)