none
Split Columns by Every 12 Commas RRS feed

  • Question

  • Is there a way in powerquery to split to columns every 12 commas?  I have a text document, where everything is separated by a comma, when really there should be a new row every 12 commas, so I would like to split commas and then transpose the column to rows and then split the columns again by commas, so that I can get a proper table.

    Sunday, June 21, 2015 5:17 AM

Answers

  • Hi Grace:-)

    Try this code :

    let
        fxSplit = (txt as text, sep as text, occur as number) as table =>
             let
                Table = Table.FromColumns({Text.Split(txt, sep)}),
                AddIdx = Table.AddIndexColumn(Table, "Indeks", 1, 1),
                AddCol1 = Table.AddColumn(AddIdx, "Custom", each Number.Mod([Indeks],occur)),
                AddCol2 = Table.AddColumn(AddCol1, "Custom.1", each if [Custom] = 0 then [Column1]&"|||" else [Column1]),
                RemOthCols = Table.SelectColumns(AddCol2,{"Custom.1"}),
                Table2 = Table.FromColumns({Text.Split(Text.Combine( RemOthCols[Custom.1], ","), "|||,")},{"Splitted Text"}),
                Ready =Table.ReplaceValue(Table2,"|||","",Replacer.ReplaceText,{"Splitted Text"})
             in
                Ready,
     
        Source = Excel.CurrentWorkbook(){[Name="Tabela1"]}[Content],
        AddColOk = Table.AddColumn(Source, "Ok", each fxSplit([Tekst], ",", 12)),
        RemCol = Table.RemoveColumns(AddColOk,{"Tekst"}),
        AddIdx = Table.AddIndexColumn(RemCol, "Indeks", 1, 1),
        ReordCols = Table.ReorderColumns(AddIdx,{"Indeks", "Ok"}),
        ExpCol = Table.ExpandTableColumn(ReordCols, "Ok", {"Splitted Text"}, {"Splitted Text"})
    in
        ExpCol

    Here is a file with example of using this code

    https://onedrive.live.com/redir?resid=E92F19DB0EBD60F6!196&authkey=!ANWEn6vL59lA6l0&ithint=file%2cxlsx

    Regards :-)

    Sunday, June 21, 2015 8:43 PM

All replies

  • Can you share a sample of the data? How would you like to handle new lines in the input dataset? Do you have multi line data that should be represented in a single cell?
    Sunday, June 21, 2015 6:43 AM
  • Hi Grace:-)

    Try this code :

    let
        fxSplit = (txt as text, sep as text, occur as number) as table =>
             let
                Table = Table.FromColumns({Text.Split(txt, sep)}),
                AddIdx = Table.AddIndexColumn(Table, "Indeks", 1, 1),
                AddCol1 = Table.AddColumn(AddIdx, "Custom", each Number.Mod([Indeks],occur)),
                AddCol2 = Table.AddColumn(AddCol1, "Custom.1", each if [Custom] = 0 then [Column1]&"|||" else [Column1]),
                RemOthCols = Table.SelectColumns(AddCol2,{"Custom.1"}),
                Table2 = Table.FromColumns({Text.Split(Text.Combine( RemOthCols[Custom.1], ","), "|||,")},{"Splitted Text"}),
                Ready =Table.ReplaceValue(Table2,"|||","",Replacer.ReplaceText,{"Splitted Text"})
             in
                Ready,
     
        Source = Excel.CurrentWorkbook(){[Name="Tabela1"]}[Content],
        AddColOk = Table.AddColumn(Source, "Ok", each fxSplit([Tekst], ",", 12)),
        RemCol = Table.RemoveColumns(AddColOk,{"Tekst"}),
        AddIdx = Table.AddIndexColumn(RemCol, "Indeks", 1, 1),
        ReordCols = Table.ReorderColumns(AddIdx,{"Indeks", "Ok"}),
        ExpCol = Table.ExpandTableColumn(ReordCols, "Ok", {"Splitted Text"}, {"Splitted Text"})
    in
        ExpCol

    Here is a file with example of using this code

    https://onedrive.live.com/redir?resid=E92F19DB0EBD60F6!196&authkey=!ANWEn6vL59lA6l0&ithint=file%2cxlsx

    Regards :-)

    Sunday, June 21, 2015 8:43 PM