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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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.

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 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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