Convert Line break values to Row without disturbing other columns value

[Amardeep100115]

Hi Superuser,

Could you please help on dealing line break values without disturbing other columns values

Columns which has line break value are as below

TRANSACTION

OPPORTUNITY  TYPE

OPPORTUNITY  #

APPROVAL STATUS

Sample Data 

Reul

[MFelix]

Hi  @Amardeep100115 ,

Believe you are refering to removing the line breaks on several cell corrects and replace it by a space so that the values on the pivot table don't show all together?

Check if the link below helps to achieve what you need.

https://www.howtoexcelatexcel.com/excel-tips-tricks/find-and-replace-line-breaks-in-excel/

[Amardeep100115]

Hi @MFelix 

Please refer below link where i have raised same query earlier and Ashish Mathur has solved most of it but the sum of Rent in that refence file is not matching with each

If you still have a query please let me know 

Reference Thread 

[MFelix]

Hi @Amardeep100115 ,

Based on the video below you can achieve the expected result

Basically or your setup you need to create 4 additional columns with the following code:

Transation break column
Text.Split([TRANSACTION], "#(lf)")

Opportunity type break column
Text.Split([OPPORTUNITY  TYPE], "#(lf)")

Opportunity break column
try Text.Split([#"OPPORTUNITY  #"], "#(lf)") otherwise
Text.ToList(Number.ToText([#"OPPORTUNITY  #"]))

Approval status break
Text.Split([APPROVAL STATUS], "#(lf)")

This will create 4 tables with  list for each line with line breaks. The Opportunity is a little different since when theres is only one value you are converting numbers and it returns errors

Now create a column tha concatenates all the other columns:

Table.FromColumns({[Transaction_Break],[Opportunity_Type_break],[Opprtunite_break],[Approval_Status_Break]})

Finally just expand this table rows and delete the 8 previous columns.

Check the code below:

let
    Source = Excel.Workbook(File.Contents("C:\Line Break.xlsx"), null, true),
    Data_Sheet = Source{[Item="Data",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Data_Sheet, [PromoteAllScalars=true]),
    #"Added Custom" = Table.AddColumn(#"Promoted Headers", "Transaction_Break", each Text.Split([TRANSACTION], "#(lf)")),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Opportunity_Type_break", each Text.Split([OPPORTUNITY  TYPE], "#(lf)"),type text),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Opprtunite_break", each try Text.Split([#"OPPORTUNITY  #"], "#(lf)") otherwise

Text.ToList(Number.ToText([#"OPPORTUNITY  #"]))),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Approval_Status_Break", each Text.Split([APPROVAL STATUS], "#(lf)")),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Table_from_Columns", each Table.FromColumns({[Transaction_Break],[Opportunity_Type_break],[Opprtunite_break],[Approval_Status_Break]})),
    #"Expanded Table_from_Columns" = Table.ExpandTableColumn(#"Added Custom4", "Table_from_Columns", {"Column1", "Column2", "Column3", "Column4"}, {"Table_from_Columns.Column1", "Table_from_Columns.Column2", "Table_from_Columns.Column3", "Table_from_Columns.Column4"})
in
    #"Expanded Table_from_Columns"

I have made a filter of all rows with line breaks and result appears correct.

[Amardeep100115]

Hi @MFelix 

i am facing below error even after replacing null value to "0" & "NAD"

Please help

 

[MFelix]

Hi @Amardeep100115 ,

Before making the new column do not make any changes in the type of data, check if there is  a step where the format of the data is changed and delete that step.

[Amardeep100115]

is possible to share PBIX file for reference
Please

[MFelix]

Hi @Amardeep100115 ,

There was some errors within the new columns due to data inconsistencys.

Added a replace Error per each new column and everything working correctly now.

Check PBIX file attach.

[Amardeep100115]

i encountered with data missing for some dates, 

instade of replacing error with Null it is possible to use other value

please help me/guide me

[MFelix · Accepted solution]

Hi @Amardeep100115 ,

If you go to the step where the error is changed just click the cog weel and instead of null write the text you need.

[amitchandak]

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

[Amardeep100115]

Hi @amitchandak 

Please refer below link where i have raised same query earlier and Ashish Mathur has solved most of it but the sum of Rent in that refence file is not matching with each

If you still have a query please let me know 

Reference thread