none
Split Delimited Row RRS feed

  • Question

  • We work a lot with surveys that have checkbox answers. I was wondering if there is a similar function to split column for rows. In our data table, each column relates to a question and where there are multiple answers, these answers appear together delimited by a space, i.e.

    Row.Number Animals                   Sighted
    
    1          {dog cat spotted_warbler} YES
    2          {elephant jack_rabbit}    NO

    What I would like to do is split out the animals column by creating a duplicate row for each animal:

    Row.Number Animals          Sighted
    
    1         {dog}             YES
    1         {cat}             YES
    1         {spotted_warbler} YES
    2         {elephant}        NO
    2         {jack_rabbit}     NO

    Is there an existing functionality/trick in Power Query/PowerBI to do this? If not has someone written code in M to solve this issue?

    Thanks!

    P.S. here is a link describing the issue using DAX queries: http://sqljason.com/2013/06/split-delimited-row-into-multiple-rows.html


    • Edited by rmyers2 Saturday, May 21, 2016 10:32 AM
    Saturday, May 21, 2016 10:18 AM

Answers

  • let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChType = Table.TransformColumnTypes(Source,{{"Row.Number", Int64.Type}, {"Animals", type text}, {"Sighted", type text}}),
        TxtSplit = Table.TransformColumns(ChType, {{"Animals", each Text.Split(_, " ")}}),
        ExpList = Table.ExpandListColumn(TxtSplit, "Animals")
    in
        ExpList

    Number of animals in cell - unlimited.

    or...if curly brackets are important

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChType = Table.TransformColumnTypes(Source,{{"Row.Number", Int64.Type}, {"Animals", type text}, {"Sighted", type text}}),
        TxtSplit = Table.TransformColumns(ChType, {{"Animals", each Text.Split(Text.Remove(_, {"{","}"}), " ")}}),
        ExpList = Table.ExpandListColumn(TxtSplit, "Animals"),
        AddCurlyBrackets = Table.TransformColumns(ExpList, {{"Animals", each "{"&_&"}"}})
    in
        AddCurlyBrackets
    Regards
    • Edited by Bill Szysz Saturday, May 21, 2016 8:02 PM
    • Marked as answer by rmyers2 Monday, May 23, 2016 12:00 PM
    Saturday, May 21, 2016 7:46 PM

All replies

  • let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChangedType = Table.TransformColumnTypes(
                          Source,
                          {
                            {"Row.Number", Int64.Type},
                            {"Animals", type text},
                            {"Sighted", type text}
                          }
                       ),
        SplittedAnimalColumn = Table.SplitColumn(
                                   ChangedType,
                                   "Animals",
                                   Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv),
                                   {"Animals.1", "Animals.2", "Animals.3"}
                               ),
        UnpivotedAnimalColumn = Table.UnpivotOtherColumns(
                                    SplittedAnimalColumn,
                                    {"Row.Number", "Sighted"},
                                    "Attribute", "Animals"
                                ),
        RemovedAttribiteColumn = Table.RemoveColumns(UnpivotedAnimalColumn,{"Attribute"}),
        TransformedAnimalColumn = Table.TransformColumns(
                                      RemovedAttribiteColumn,
                                      {
                                        "Animals",
                                        each "{" & Text.Trim(_, {"{", "}"})  & "}"
                                      }
                                  ),
        ReorderedAnimalColumn = Table.ReorderColumns(
                                    TransformedAnimalColumn,
                                    {"Row.Number", "Animals", "Sighted"}
                                )
    in
        ReorderedAnimalColumn

    I apologize for the indenting being screwed up by page entry.

    Saturday, May 21, 2016 4:52 PM
  • let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChType = Table.TransformColumnTypes(Source,{{"Row.Number", Int64.Type}, {"Animals", type text}, {"Sighted", type text}}),
        TxtSplit = Table.TransformColumns(ChType, {{"Animals", each Text.Split(_, " ")}}),
        ExpList = Table.ExpandListColumn(TxtSplit, "Animals")
    in
        ExpList

    Number of animals in cell - unlimited.

    or...if curly brackets are important

    let
        Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
        ChType = Table.TransformColumnTypes(Source,{{"Row.Number", Int64.Type}, {"Animals", type text}, {"Sighted", type text}}),
        TxtSplit = Table.TransformColumns(ChType, {{"Animals", each Text.Split(Text.Remove(_, {"{","}"}), " ")}}),
        ExpList = Table.ExpandListColumn(TxtSplit, "Animals"),
        AddCurlyBrackets = Table.TransformColumns(ExpList, {{"Animals", each "{"&_&"}"}})
    in
        AddCurlyBrackets
    Regards
    • Edited by Bill Szysz Saturday, May 21, 2016 8:02 PM
    • Marked as answer by rmyers2 Monday, May 23, 2016 12:00 PM
    Saturday, May 21, 2016 7:46 PM
  • Split the text within the cell instead of splitting the whole column. Much simpler and more elegant solution. Good job William. :)
    Saturday, May 21, 2016 8:13 PM
  • Thanks Colin.

    Above I presented easier version of the code (easier to read and understand) because, really, we do not need AddCurlyBrackets step. The only we have to do, is  change TxtSplit step to this below

    TxtSplit = Table.TransformColumns(ChType, {{"Animals", each List.Transform(Text.Split(Text.Remove(_, {"{","}"}), " "), each "{"&_&"}") }}),
    

    where each of "_" has a different meaning (depending on context) and expand list column.

    Regards

    Sunday, May 22, 2016 10:45 PM
  • Worked perfectly! Thanks! Tried it in PowerBI with minor modifications.
    • Edited by rmyers2 Monday, May 23, 2016 12:01 PM
    Monday, May 23, 2016 12:01 PM