Splitting column in 2 for each delimiter occurence

[eliottbbedard]

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!

[dufoq3 · Accepted solution]

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
    TrimmedText

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

[eliottbbedard]

@dufoq3  Awsome! Thanks a lot! Exactly what I was trying to do 

[dufoq3]

You're welcome 😉

[Greg_Deckler]

@eliottbbedard Yeah, that will take a bit more than that. Can you post sample data as text?

[eliottbbedard]

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

Goal

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]

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.

[eliottbbedard]

@lbendlin Thanks for your answer but this isn't what i'm trying to acheive. Maybe my example was not clear enough. 

The different items are on the same line and not multiple lines. something like this:

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

Compliance with laws / Conformité aux lois

Compliance with laws / Conformité aux lois Non compliance with Articles of Agreement / Non conforme aux modalités de l'Entente

Pay related issues / Problèmes de paie

What i'm trying to do is to split the column to have one for each language and keep the same formatting (a line break between each items on the row)

English	French
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
Compliance with laws	Conformité aux lois
Compliance with laws Non compliance with Articles of Agreement	Conformité aux lois Non conforme aux modalités de l'Entente
Pay related issues	Problèmes de paie

Thanks!

[lbendlin]

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZG9TsNADMdfxcpMRcWCxFZVCJaiShmTDCZxwqE7O7oPSt+IPkdeDN9lgokObP74+6e/7aapjngGTxYjDWBCSBTgFo5eXu3y5TQZCGY01LLW3qmPcEDGiRxxfIBnQhvfAHmAGkeK5xxuxEOdZvIfJhjh0j2oXLzhSeFPFGKuZ3JmKqdGjssFKEK9XPrkzZptJP0gaX8FYWx5L262BrknOBk1YfGUre+FR/GuEDB9ghUTWn7R6f7XwM5H01tdUUbYTZ7KTkpYtYVCBeFkQJuB5Rq2Tdvt3f0jR5VT1d001RVWrtX/t/U/vr/qum8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t]),
    #"Added Index" = Table.AddIndexColumn(Source, "Index", 0, 1, Int64.Type),
    #"Split Column by Delimiter1" = Table.ExpandListColumn(Table.TransformColumns(#"Added Index", {{"Column1", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column1"),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Split Column by Delimiter1", "Column1", Splitter.SplitTextByEachDelimiter({" / "}, QuoteStyle.Csv, false), {"English", "French"}),
    #"Grouped Rows" = Table.Group(#"Split Column by Delimiter2", {"Index"}, {{"English", (k)=>Text.Combine(k[English],"#(lf)")},{"French", (k)=>Text.Combine(k[French],"#(lf)")}}),
    #"Removed Other Columns" = Table.SelectColumns(#"Grouped Rows",{"English", "French"})
in
    #"Removed Other Columns"

That's not something Power Query supports out of the box. You would need to provide a binary representation of the data - so we can see where it uses CR and where it uses LF.