Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Amardeep100115
Post Prodigy
Post Prodigy

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

TRANSACTIONOPPORTUNITY  TYPEOPPORTUNITY  #APPROVAL STATUS

 

Sample Data 

 

Reul

Amardeep Bhingardeve
1 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.

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

11 REPLIES 11
MFelix
Super User
Super User

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/

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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 

Amardeep Bhingardeve

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Hi @MFelix 

 

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

 

Please help

 Annotation 2020-03-31 133500.jpg

Amardeep Bhingardeve

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



is possible to share PBIX file for reference
Please
Amardeep Bhingardeve

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.


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



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

Amardeep Bhingardeve

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.

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



amitchandak
Super User
Super User

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

Appreciate your Kudos.

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

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 

Amardeep Bhingardeve

Helpful resources

Announcements
July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.