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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors