Forum Discussion

danextian's avatar
danextian
Super User
9 years ago
Solved

Split a cell into multiple columns

Hi All,   I need your help again.   1/11 communication 1/10 communication 1/9 communication 1/6 communication 1/5 communication 1/4 I emailed the following: items a items b   Above is th...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    danextianPhil_Seamark

    Indeed, the feature doesn't work in this case.

    The code of the recorded code line is:

     

    = Table.ExpandListColumn(Table.TransformColumns(Source, 
    {{"Spalte ""1""", Splitter.SplitTextByDelimiter("#(#)(lf)", QuoteStyle.Csv), 
    let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), 
    "Spalte ""1""")

    By simply adjusint the part

    Splitter.SplitTextByDelimiter("#(#)(lf)",

    to

    Splitter.SplitTextByDelimiter("#(lf)",

     

    it works as desired.

     

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
9 years ago

Hi danextian

 

You could try the great now "split into rows" feature that was introduced in a recent version of Power BI Desktop

 

So use the Query Editor and do the following.  Highlight your column and split on special character #(lf) into rows

.

  • danextian's avatar
    danextian
    Super User
    9 years ago

    Hi Phil_Seamark,

     

    Thanks for the reply.

     

    I tried your advise to break a cell by line feed into rows. Unfortunately, it didn't work. Doing so does nothing.

    I tried to split the cells into column and they did.  The feature is there but i don't think it is working.

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      danextianPhil_Seamark

      Indeed, the feature doesn't work in this case.

      The code of the recorded code line is:

       

      = Table.ExpandListColumn(Table.TransformColumns(Source, 
      {{"Spalte ""1""", Splitter.SplitTextByDelimiter("#(#)(lf)", QuoteStyle.Csv), 
      let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), 
      "Spalte ""1""")

      By simply adjusint the part

      Splitter.SplitTextByDelimiter("#(#)(lf)",

      to

      Splitter.SplitTextByDelimiter("#(lf)",

       

      it works as desired.

       

      • danextian's avatar
        danextian
        Super User
        9 years ago

        Anonymous

         

        by the way, upon checking Power BI does this Splitter.SplitTextByDelimiter("/#(lf)",