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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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

Top Solution Authors
Top Kudoed Authors