Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
LukeReds
Helper II
Helper II

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

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

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

 

Thank you very much

m_dekorte
Super User
Super User

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!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors