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

[Amardeep100115]

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 

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[d_gosbell]

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.

[Amardeep100115]

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 TYPE

TYPE

APPROVAL STATUS

Expiration Date

Target Date

Critical 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

[v-juanli-msft]

Hi @Amardeep100115 

I have no acess to your file.

It is limited.

Just share a little and small data which can exanplain your problem.

Best Regards
Maggie

[Amardeep100115]

Please try below link 

it has 5 cololumn where line break values are there .

Result : line break value splite to rows and the rent value should not be get multipal accoriding to new splitted row

so  we can easiliy calculate rent

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

[Ashish_Mathur]

Hi,

Since the file size exceed 5 MB, it is not opening.  There is no option to download the file either.

[Amardeep100115]

Hi @Ashish_Mathur 

i have reduced size of file please have a look.

https://1drv.ms/x/s!Ai3rGPgy20kLwTTKh2Pewer6csYe?e=Svf3gu

[Amardeep100115]

Hi @Ashish_Mathur 

above link was has excel file and below is power Bi file

https://1drv.ms/u/s!Ai3rGPgy20kLwTWea9DhobGCfAJU?e=ZRCVIS

[Ashish_Mathur]

Hi,

To carry out any transformation, i will need the Excel file.

[Amardeep100115]

Hi @Ashish_Mathur 

Please visit below link to access the excel file

https://1drv.ms/x/s!Ai3rGPgy20kLwTTKh2Pewer6csYe?e=Af8ah4

[Ashish_Mathur]

Hi,

In that Excel file, i do not see line breaks in any column.

[Amardeep100115]

Hi @Ashish_Mathur 

Thank you for  your reply.

I have i highlighted rows where line break values. 

please visit below link to access the said file 

https://1drv.ms/x/s!Ai3rGPgy20kLwTTKh2Pewer6csYe?e=kzN1KR 

https://1drv.ms/x/s!Ai3rGPgy20kLwTTKh2Pewer6csYe?e=kzN1KR

[Ashish_Mathur]

Hi,

So in row 25 once data is split into 2 rows, what value would you want to see in the rent column of those 2 rows?  Also, what value of rent do you want to see in row 45 (where there is TBD in the Target date column)?

[Amardeep100115]

Hi @Ashish_Mathur 

For example; if row no 25 split to row and one of that row should have rent value and splitted row should not be have any rent value. if there has TBD then ignore.

Please find the below snap you will get an idea.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Amardeep100115]

Hi @Ashish_Mathur 

Thank you so much for your great help.

But the sum of rent sould be $ 626296157 not  $ 626640900. 

Can you help me with fix this?

But really it's great

[Ashish_Mathur]

Hi,

Now it works.  Download the file from here.

[Amardeep100115]

Hi @Ashish_Mathur 

could you please guide me how you add below step in your file

= Table.Group(#"Removed Columns", {"Merged"}, {{"Partition", each Table.AddIndexColumn(_, "Index",1,1), type table}})

[Ashish_Mathur]

Hi,

That statement has to be written.  It basically resets the counter for every change in the Merged column.  It's like a COUNTIF() function in MS Excel.

[Amardeep100115]

Hi @Ashish_Mathur 

Hi have replaced data with my main data but the partition step is not working could you pleas help me on this

Click here