Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Split collum challenge

 

Hello, Power friends,

 

I have the following data source in excel format.

AB
144987#199270
#199271
148511#344827
#344816
#344838
#344823
148511#344831
#344825
#344840
#344822
#344815
#344834
#344837
#343949

 

I need to separate all data starting with "#" in column "B" to generate a new table with the relationship between column A and B, such as:

AB
144987#199270
144987#199271
148511#344827
148511#344816
148511#344838
148511#344823

And, so on...

 

Note that there is no fixed number of strings beginning with "#" in column B.

 

Thanks for your time.

Need Help
  • Nolock's avatar
    Nolock
    7 years ago

    I see, but that is exactly what I did before for you. Check the following 2 screenshots:

     

    The steps Source and Navigation on the right hand site are importing Excel file into Power BI. You also see how the data looks like.

     

    The 3rd step is the split described above. Again, you see the result of this step. And it is what you expect, I hope.

12 Replies

  • Nolock's avatar
    Nolock
    Resident Rockstar
    7 years ago

    Hi Anonymous ,

     

    the only thing you have to do is to split the column into rows by your delimiter.

    If your delimiter is a new line, use #(cr) or #(lf) or both, it depends on your needs.

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Very goog Nolock . This is the easy part. And how about the new table generation?

      Thanks for your time.

       

      • Nolock's avatar
        Nolock
        Resident Rockstar
        7 years ago

        I've created an Excel file as you have. With Alt+Enter as a line delimiter in Excel.

         

        And then I used the same code as before but as delimiter I use #(lf) - trial/error, I didn't know if the delimiter is cr or lf or both.

         

        let
    Source = Excel.Workbook(File.Contents("C:\Users\...\Desktop\Book1.xlsx"), null, true),
    Sheet2_Sheet = Source{[Item="Sheet2",Kind="Sheet"]}[Data],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Sheet2_Sheet, {{"Column2", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.None), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Column2")
in
    #"Split Column by Delimiter"