Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

how to convert Line Break Values from One Cell to Multiple/next rows in power bi

how to convert Line Break Values from One Cell to Multiple rows in power bi, i have a imported excel there is an 3 cilumn has line break values first column is has Text, second  has date, thisrd has number + hypen 

please guide me 

Need Help

37 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi Anonymous 

    Do you mean converting the table a to table b?

    table a

    column1 column2 column3
    a 1/1/2019 sd ed rf
    b 2/1/2019 df rf yh

     

    table b

    column1 column2 column3
    a 1/1/2019 sd
    a 1/1/2019 ed
    a 1/1/2019 rf
    b 2/1/2019 df
    b 2/1/2019 rh
    b 2/1/2019 yh

     

    If so, you could open edit queries, use split column option.

    https://support.office.com/en-gb/article/split-a-column-of-text-power-query-5282d425-6dd0-46ca-95bf-8e0da9539662

    If my understanding is not correct, please show me your data example and expected output.

     

    Best Regards
    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • d_gosbell's avatar
      d_gosbell
      Super User
      7 years ago

      If you have data like the following:

       

      And you want to split it into separate columns

       

      You would use the Split Column option under the Transforms tab in Power Query and set it up as follows:

      Note that you may have to experiment with using #(lf) or #(cr) or #(cr)#(lf) as your delimiter character depending how the line breaks in your source file were generated.

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        Hi

         

        Please find the below onedrive link in which i have shared excel file, 

        i wish to saperate line break cell and convert into row for below column.

         

        TRANSACTION TYPETYPEAPPROVAL STATUSExpiration Date Target DateCritical Date

         

        i wish to convert/split them in to row and the data visuals should be reflact on filter selection of the month on visulisation page. i wish to have a table on visulisation page which include data like in wxcel file but it should be replact or change on the selection of monthand display for that month with upcoming expiring product details . 

         

        Please note sum of rent should not be exceed than in excel.  i tried to split column and then i noticed that rent value gets multiple according to rows after splitting any colum

         

        https://1drv.ms/x/s!Ai3rGPgy20kLwTNZ1uUzBj9qRRc_?e=a6VTqD