Forum Discussion
Convert Line break values to Row without disturbing other columns value
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 |
Reul
Hi Anonymous ,
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.
11 Replies
The information you have provided is not making the problem clear to me. Can you please explain with an example.
Appreciate your Kudos.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
Hi Anonymous ,
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/
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
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.
