Convert Line break values to Row without disturbing other columns value

Hi Anonymous ,

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.