Forum Discussion
Splitting column in 2 for each delimiter occurence
Hello,
I'm working with data imported from a bilingual document and I'm trying to split a column in 2 columns, one for each language. In the screenshot below, each line can contains multiple elements displayed on multiple lines. What i'm trying to do is to split the column after the / sign to have an English and French coloumn while keeping the same formatting (one element per line).
Here's an example of what I'd like to achieve
I did try to split the column for each occurence of the delimiter but it isn't working.
Does anyone have a solution?
Thanks!
Hi eliottbbedard, check this:
Before
v1 (each line in new row)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zdK9TsQwDADgV7E6c+LEgsR2OiFY7nRSx7aDad0SlNhVfjjujbjn6IvhpBNMLEhscWx/sqM0TXXCC3iyGGkAE0KiALdw8vJil0+nwUAwo6GW9e6N+ggHZJzIEccHeCa08RWQB6hxpHjJx414qNNM/t0EI1yyBy0Xb3hS/IlCzPdZzqY6NXJcrkAR6uXaJ2/WaCPpm6T5FcLY8l7cbA1yT3A2OoTFcx59LzyKd0XA9AFWTGj5qN39j4adj6a3uqKMsJs8lZ1UWGuLQoVwMqDNYHkN26bt9u7+kaOWU9XdNNU/GuVv9V9+lqrrvgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), Ad_Splitted = Table.AddColumn(Source, "Splitted", each [ a = Text.SplitAny([Column1], "#(lf)#(cr)"), b = List.Transform(a, (x)=> Text.Split(x, "/")), c = Table.FromRows(b, type table[English=text, French=text]) ][c], type table), Splitted = Table.Combine(Ad_Splitted[Splitted]), TrimmedText = Table.TransformColumns(Splitted,{{"English", Text.Trim, type text}, {"French", Text.Trim, type text}}) in TrimmedTextv2 (preserved rows)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zdK9TsQwDADgV7E6c+LEgsR2OiFY7nRSx7aDad0SlNhVfjjujbjn6IvhpBNMLEhscWx/sqM0TXXCC3iyGGkAE0KiALdw8vJil0+nwUAwo6GW9e6N+ggHZJzIEccHeCa08RWQB6hxpHjJx414qNNM/t0EI1yyBy0Xb3hS/IlCzPdZzqY6NXJcrkAR6uXaJ2/WaCPpm6T5FcLY8l7cbA1yT3A2OoTFcx59LzyKd0XA9AFWTGj5qN39j4adj6a3uqKMsJs8lZ1UWGuLQoVwMqDNYHkN26bt9u7+kaOWU9XdNNU/GuVv9V9+lqrrvgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), Ad_Splitted = Table.AddColumn(Source, "Splitted", each [ a = Text.SplitAny([Column1], "#(lf)#(cr)"), b = List.Zip(List.Transform(a, (x)=> List.Transform(Text.Split(x, "/"), Text.Trim))), c = List.Transform(b, (x)=> Text.Combine(x, "#(lf)")), d = Table.FromColumns(List.Split(c, 1), type table[English=text, French=text]) ][d], type table), Splitted = Table.Combine(Ad_Splitted[Splitted]) in Splitted
8 Replies
- Greg_Deckler
Community Champion
eliottbbedard Yeah, that will take a bit more than that. Can you post sample data as text?
Hi
Here is an exemple of one line in my table, what I have currently and what I would like to acheive.
Current
Pay related issues / Problèmes de paie
Project Management: Health and Safety and-or Supervision and Mentoring / Gestion de projet: Santé et Sécurité et-ou Supervision et Mentorat
Compliance with laws / Conformité aux lois
Non compliance with Articles of Agreement / Non conforme aux modalités de l'EntenteGoal
Pay related issues
Project Management: Health and Safety and-or Supervision and Mentoring
Compliance with laws
Non compliance with Articles of Agreement
Problèmes de paie
Gestion de projet: Santé et Sécurité et-ou Supervision et Mentorat
Conformité aux lois
Non conforme aux modalités de l'Entente
Thank you!
- lbendlin
Super User
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XdCxTgMxDAbgV7E6U1GxILFVFYKlVaUbSweT8x1BSXxyHErfiD7HvRhOboItif98sn06rY54BaGASj34nAtluIej8HuYf6JdeoIJPa3Od5YV/iSnsMeEI0VK+gSvhEE/AFMPHQ6k13pcs0BXJpIvnz2nVt1bnMWn0fwXylrfK15NczpMOt+AFLr55or45bbm8key+gKhtpZ2HKfgMTmCi7c+Al7qADtOA0tsCJZvCOxzyx/McP/+bEW9CzYrD7AdhdpkhizZBlFTIvcYqtnWEt7KZvPw+JzU4rag8y8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), #"Split Column by Delimiter" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByEachDelimiter({" / "}, QuoteStyle.Csv, false), {"English", "Francais"}) in #"Split Column by Delimiter"How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.
- dufoq3
Hi eliottbbedard, check this:
Before
v1 (each line in new row)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zdK9TsQwDADgV7E6c+LEgsR2OiFY7nRSx7aDad0SlNhVfjjujbjn6IvhpBNMLEhscWx/sqM0TXXCC3iyGGkAE0KiALdw8vJil0+nwUAwo6GW9e6N+ggHZJzIEccHeCa08RWQB6hxpHjJx414qNNM/t0EI1yyBy0Xb3hS/IlCzPdZzqY6NXJcrkAR6uXaJ2/WaCPpm6T5FcLY8l7cbA1yT3A2OoTFcx59LzyKd0XA9AFWTGj5qN39j4adj6a3uqKMsJs8lZ1UWGuLQoVwMqDNYHkN26bt9u7+kaOWU9XdNNU/GuVv9V9+lqrrvgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), Ad_Splitted = Table.AddColumn(Source, "Splitted", each [ a = Text.SplitAny([Column1], "#(lf)#(cr)"), b = List.Transform(a, (x)=> Text.Split(x, "/")), c = Table.FromRows(b, type table[English=text, French=text]) ][c], type table), Splitted = Table.Combine(Ad_Splitted[Splitted]), TrimmedText = Table.TransformColumns(Splitted,{{"English", Text.Trim, type text}, {"French", Text.Trim, type text}}) in TrimmedTextv2 (preserved rows)
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("zdK9TsQwDADgV7E6c+LEgsR2OiFY7nRSx7aDad0SlNhVfjjujbjn6IvhpBNMLEhscWx/sqM0TXXCC3iyGGkAE0KiALdw8vJil0+nwUAwo6GW9e6N+ggHZJzIEccHeCa08RWQB6hxpHjJx414qNNM/t0EI1yyBy0Xb3hS/IlCzPdZzqY6NXJcrkAR6uXaJ2/WaCPpm6T5FcLY8l7cbA1yT3A2OoTFcx59LzyKd0XA9AFWTGj5qN39j4adj6a3uqKMsJs8lZ1UWGuLQoVwMqDNYHkN26bt9u7+kaOWU9XdNNU/GuVv9V9+lqrrvgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]), Ad_Splitted = Table.AddColumn(Source, "Splitted", each [ a = Text.SplitAny([Column1], "#(lf)#(cr)"), b = List.Zip(List.Transform(a, (x)=> List.Transform(Text.Split(x, "/"), Text.Trim))), c = List.Transform(b, (x)=> Text.Combine(x, "#(lf)")), d = Table.FromColumns(List.Split(c, 1), type table[English=text, French=text]) ][d], type table), Splitted = Table.Combine(Ad_Splitted[Splitted]) in Splitted
