Forum Discussion

OKgo's avatar
OKgo
Helper IV
6 years ago
Solved

A more robust Table.SplitColumn / Splitter.SplitTextByDelimiter

I have a dataset with ISO countries stored in one column e.g. "USA, CAN, MEX, GBR, DEU, POL". This will grow as the database grows. Is there a way to future proof this PQ to account for that growth? I made some tests with more than 6 counties and the hardcoding then ommits critical data.

 

= Table.SplitColumn(#"Removed Other Columns", "* Merge Colum 9 & 11", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"Column9.1", "Column9.2", "Column9.3", "Column9.4", "Column9.5", "Column9.6"})

 

Thank you

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    a probably more efficient way would be this:

     

     

     

     

     

     

        nrows=Table.RowCount(yourTab),
    split=Table.FromColumns(List.Zip(List.Transform({0..nrows-1},each Text.Split(yourTab[country]{_},","))))
in
    split

     

     

     

    PS

    If Table.TransformRows had, as Table.TransformColumns has, the ability to manage lists of different lengths, there would have been no need to use the List.zip function to do an intermediate step.

     

  • Anonymous's avatar
    Anonymous
    6 years ago

    aahhh ... 😀  ok, now  I see.

     

    you have a table like this (ONLY ONE COUNTRY LIST PER PartNumber. Is it true?)):

     

     

     

    and want a table like this (only first rows are displayed):

     

     

     

    all you need is in this line (no adda column, no unpivot, no delete lines,...) :

     

    Table.ExpandListColumn(Table.Group(yourTab, {"PartNumber"}, {{"split", each Text.Split(_[country]{0},",")}}),"split")

     

    PS

    or more directly e I tink clearer

     

    Table.ExpandListColumn(Table.TransformColumns(yourTab, {"country", each Text.Split(_,",")}),"country")

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    if you are sure the length/composition of string is constant between different rows, you can manage the future different input in in this way:

     

     

    cols=Text.Split(yourTab[country]{0},","),
colSpl = Table.SplitColumn(yourTab, "country", Splitter.SplitTextByDelimiter(",", QuoteStyle.None), cols)


in
colSpl

     

     

    If the length/strcture of string could varies between the different lines, you need some more line of code to manage the situation.

     

    • OKgo's avatar
      OKgo
      Helper IV
      6 years ago

      Thank you Anonymous . Unfortuneatly the number of coutnries varies from null to a probable max of 50. The end goal is to turn
      "PartNumber1", "USA, MEX, CAN"

      into something like this duplicating the data

      "PartNumber1", "USA"

      "PartNumber1", "MEX"
      "PartNumber1", "CAN"

       

      Maybe I am using my Excel user interface brain too much and there is an easier PQ function I could be using.

       

      Hardcoding 50 columns and unpivitoing is what could do - but I was hoping for some code I would not have to worry about periodly checking "oh I wonder do I have more than 50 countires today"

      • mahoneypat's avatar
        mahoneypat
        Microsoft Employee
        6 years ago

        Here is one way to do it in the query editor.  I am assuming you want a list of lists for use in a subsequent step, so you can delete the steps after the ListOfLists step, if so.

         

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCkgsKvErzU1KLTJU0lEKDXbUcXb00/F1jVCKjQUA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Column1 = _t, Column2 = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each let col1 = [Column1] in List.Transform(Text.Split([Column2], ","), each {col1, _})),
    ListOfLists = #"Added Custom"{0}[Custom],
    #"Converted to Table" = Table.FromList(ListOfLists, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Extracted Values" = Table.TransformColumns(#"Converted to Table", {"Column1", each Text.Combine(List.Transform(_, Text.From), ";"), type text}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Extracted Values", "Column1", Splitter.SplitTextByDelimiter(";", QuoteStyle.Csv), {"Column1.1", "Column1.2"}),
    TableVersion = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Column1.1", type text}, {"Column1.2", type text}})
in
    TableVersion

         

        If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

        Regards,

        Pat