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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
LostInWoods
New Member

Merge splitted cells in new cloumn

Hello,

 

I imported a table form .txt file but I would like to modify table. I woud like to concatenate values form same cloumn because cell values are splitted in half.

 

Current table:

SampleText spl
 itted in half 

 

Desired table:

SampleText spl Text splitted in half
 itted in half 

 

How to perform such manipulation in power query editor?

1 ACCEPTED SOLUTION
Vijay_A_Verma
Super User
Super User

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk7MLchJVdJRCkmtKFEoLshRitWJVgLyM0tKUlMUMvMUMhJz0sCCEKWGULWJScnIylNS09KzgOpiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if Number.Mod([Index],2)=0 then [Column2]&#"Added Index"[Column2]{[Index]+1} else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Custom] <> null))
in
    #"Filtered Rows"

 

View solution in original post

4 REPLIES 4
Vijay_A_Verma
Super User
Super User

See the working here - Open a blank query - Home - Advanced Editor - Remove everything from there and paste the below code to test

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCk7MLchJVdJRCkmtKFEoLshRitWJVgLyM0tKUlMUMvMUMhJz0sCCEKWGULWJScnIylNS09KzgOpiAQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each if Number.Mod([Index],2)=0 then [Column2]&#"Added Index"[Column2]{[Index]+1} else null),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Index"}),
    #"Filtered Rows" = Table.SelectRows(#"Removed Columns", each ([Custom] <> null))
in
    #"Filtered Rows"

 

Hello,

 

Thank you for quick help.

 

I got error msg, in every 2nd row (where conditon is true):

 

Expression.Error: "XXX" value can not convert to: List.
Details:
Value=XXX
Type=[Type]

 

I think it would be OK if I could convert text.

Can you post few lines of your dataset capturing column names as well so that I can understand the pattern and accordingly modify the solution?

Sorry, it was my mistake (I thought I can live without crtl+C and started typing). Your solution works perfectly.

 

Much appreciated for your help.

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors