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 the content of a single cell. I want to split them into multiple rows per line break  - that's char(10) in excel -  and get the earliest date. I know i cant split them into multiple columns but I just can't find the function in Power BI Desktop to split them into rows. 

 

The splitting step is where I am stuck with. After I done with that step, I can just extract the text string until right before the first blank space, convert the strings to date, replace text (items a & items b lines) with null and remove them, sort the remaining rows in ascending order and remove the duplicates.

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.

     

6 Replies

  • 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.