none
How to split a list column into multiple columns RRS feed

  • Question

  • Hi team,

    I have such a requirement, say I have a column like this a_b_c, i would like to split it to multiple columns, i.e. a, b, c. I know how to use the split function, and it gives me a list column. But when i expand it, it gives me three more rows, with each row being a, b and c respectively. I want to horizontally expanding, not vertically.  Any easy way to do that?

    Thanks,

    Arthur


    Keep involved!

    Tuesday, December 9, 2014 2:34 AM

Answers

  • Hi Arthur,

    Table.SplitColumn will do what you want. You can apply it from the UI by right clicking on the column you want to split and selecting Split Column > By Delimiter...

    It will produce a query similar to this one:

    let
        Source = Table.FromRecords({[A="a_b_C"]}),
        SplitColumnDelimiter = Table.SplitColumn(Source,"A",Splitter.SplitTextByDelimiter("_"),{"A.1", "A.2", "A.3"})
    in
        SplitColumnDelimiter

    Regards,
    Tristan

    • Marked as answer by Jingfei Wednesday, December 10, 2014 2:41 AM
    Tuesday, December 9, 2014 4:08 AM
    Moderator

All replies

  • Hi Arthur,

    Table.SplitColumn will do what you want. You can apply it from the UI by right clicking on the column you want to split and selecting Split Column > By Delimiter...

    It will produce a query similar to this one:

    let
        Source = Table.FromRecords({[A="a_b_C"]}),
        SplitColumnDelimiter = Table.SplitColumn(Source,"A",Splitter.SplitTextByDelimiter("_"),{"A.1", "A.2", "A.3"})
    in
        SplitColumnDelimiter

    Regards,
    Tristan

    • Marked as answer by Jingfei Wednesday, December 10, 2014 2:41 AM
    Tuesday, December 9, 2014 4:08 AM
    Moderator
  • Thanks, Tristan. It works perfectly.

    Keep involved!

    Wednesday, December 10, 2014 2:41 AM