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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
LukeReds
Helper I
Helper I

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 I
Helper I

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

Top Solution Authors
Top Kudoed Authors