Forum Discussion

eliottbbedard's avatar
eliottbbedard
Frequent Visitor
2 years ago
Solved

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 elem...
Need Help
  • dufoq3's avatar
    dufoq3
    2 years ago

    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's avatar
eliottbbedard
Frequent Visitor
2 years ago

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's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago
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's avatar
    eliottbbedard
    Frequent Visitor
    2 years ago

    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 lawsConformité aux lois

    Compliance with laws

    Non compliance with Articles of Agreement

    Conformité aux lois

    Non conforme aux modalités de l'Entente

    Pay related issuesProblèmes de paie

     

    Thanks!

    • lbendlin's avatar
      lbendlin
      Icon for Super User rankSuper User
      2 years ago

       

       

      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.