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