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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mork
Helper V
Helper V

New line Characters

Hello, 

I use an excel file to import some text notes into Power BI for our departments. The notes are around two paragraphs and the show fine on excel. When importing them to Power BI they appear as a single line. I'm trying to solve this issue the past two weeks but I haven't managed to do that. 

 

I know that at the moment Power BI doesn't have a visual or a way to seperate lines. But there is the custom visual "Long text viewer" which looks like its the only way to go. Still though I have to find a way to insert new lines.

 

To do that I tried using HTML tags <p> </p> and the usual \n\r but it didn't work.

 

Then I found out that inside the query editor when replacing values, on the pop up that appears, under advanced options there is the option to use special characters. I selected line feed and carriage return. This replaced a value I had selected in my text with #(cr)#(lf) which seem to be the new line characters Power BI recognizes. 

 

This unfortunately for some weird reason only works inside query editor. What I mean is that inside the query editor on my table there is a line feed in every place there is that I replaced the value with the new line characters.

 

Outside of the query editor it doesn't work. When I go to the data view, all my txt data is still in a single line. Plus the special characters are no where to be seen. The long text viewer visual also doesn't work but I guess that is expected since my table doesn't show the text with multiple lines.

 

Does anyone have any solution to that? It is really vital for my business to make that text appear correctly. 

It doesn't really make sense for Power BI to provide the feature of line feed and carriage return only to work inside the query editor. Plus having a way to present multiline text is a must for every reporting tool and its a big omission from the side of Power BI.

31 REPLIES 31
theBIbutler
Resolver II
Resolver II

I had the same issue trying to display an address....ended up concatenating the address lines together then using the long text visual to display it..as that is the only one that wraps text..

Hi,

 

How do you word wrap in long text viewer. Is there a special character that i can add to text data to make sure data is displayed in new line.

Brian_M
Responsive Resident
Responsive Resident

Yes, you can add a custom string where you want the line break (I used "(LF)" but you can use anything) and then specify this under the Newline starts with option under Format > Text for the Long Text Viewer visual (Beta v.2) 

 

HHope that helps!

Is there still no way to get Power BI visuals to handle line breaks?

 

I have tried with an Excel source. The Excel sheet consist of a single column with a header and one row. In this row I have made a text with a couple of line breaks and then loaded the sheet into a Power BI Desktop model (march 2017 version).

 

My row show with line breaks when viewing data in Query Editor. But when I add the column to my visuals the line breaks disapear (but however show in the popup when I hover the mouse over the value).

 

I have tried using Table, Multi-row Card, Matrix and Matrix preview but neither will show the line break.

 

I also tried creating a measure, but this didn't help much. I used this formular:

Comment_Meas = CONCATENATE( VALUES(Sheet1[Comment]); "")

Is there any other way to get Power BI to accept line breaks in a text measure??

/sdjensen
Brian_M
Responsive Resident
Responsive Resident

Have you tried the same thing but with the preview version of the Table visual with word wrap enabled?

Brian_M
Responsive Resident
Responsive Resident

I've just tried it myself, no luck sorry 😞

Mardin
Advocate IV
Advocate IV

I have this issue as well. My text string appear with line-break in PowerQuery but as a single line in the final tabel 😞

Greg_Deckler
Super User
Super User

I haven't figured out a way to get line breaks to appear where I want them and have tried a kinds of things like ASCII characters, etc. If you figure it out, I'd be very interested.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

My gut tells me the right special characters are #(cr)#(lf) but they don't work at the moment.

 

@Greg_Deckler Do you have a workaround maybe? A way to better show text without line breaks?

I have been trying to use all the solutions available on the net but still no luck. Any Help from power BI guys? 

 

Full Contact = LOOKUPVALUE(' Contacts'[Manager Name],' Contacts'[Id],'Customer Contact'[ ContactId])
& " " & LOOKUPVALUE(' Contacts'[Email],' Contacts'[Id],'Customer Contact'[ ContactId])
& " " & LOOKUPVALUE(' Contacts'[Telephone],' Contacts'[Id],'Customer Contact'[ ContactId])

 

here's my code. 

Suggest that you raise it as a bug for the data import team to look at - at their leisure.  we are able to see the line break in the text  when looking at the data in Advanced editor .... but does not seem to get into the table from there Smiley Sad  Thinking pacman is eating them up! 

I have already submitted a ticket. still no reply.

Vicky_Song
Impactful Individual
Impactful Individual

@mork@sornavoor@FawadRehman@Greg_Deckler, thanks for reporting this issue. I can reproduce the issue as well, I will report it internally and will post back once I get any updates. 

@Vicky_Song Thank you for reporting it. hopefully we will get a fix soon! And a better way to present text in power bi too.

@mork any update on line break character ? we really need this option in our reports. Thanks

We know about this issue, we've recently implemented a fix that takes <br> tags as new line. We aren't doing the change for <p> tags *yet*, because that will break the backwards compatibility (lot more than handling <br> tags 🙂 ) This fix will be in the next or 2nd next PowerBI Desktop release.

 

If you don't want to wait for that, I've suggested a workaround here: https://social.technet.microsoft.com/Forums/en-US/4c7b662d-2cb2-4d00-a1aa-da4f086ac984/powerquery-sp...

Basically, depending on your scenario, you can replace the <br> or <p> characters with a special character, then handle it that way.

Vicky_Song
Impactful Individual
Impactful Individual

@mork, please have a check on arify's solution to see whether it works. If you still need further help, please post back.

Hi @Vicky_Song,

 

I don't think this issue has been resolved. If it has, could you direct me to the solution?

 

I need to display an address like this:

 

Line 1

Line 2

City

Postcode

 

Instead of this:

 

Line 1, Line 2, City, Postcode

 

Thanks!

Hi,

 

I know you asked this question on 2016, though if you are still looking for an answer. It should work from below

This will work considering you have seperate column for each. If that is not the case just replace (,) with #(cr)#(lf)

 

= Table.CombineColumns(Table.TransformColumnTypes(#"Source", {{"Line1", type text}, {"Line2", type text}, {"City", type text}, {"Postal Code", type text}},Combiner.CombineTextByDelimiter("#(cr)#(lf)", QuoteStyle.None),"Address")

 

 

Mark this as a Solution if this work.

 

Thanks,

Dhawal

I'm told this is available in the July 2017 release...

 

The Power BI Desktop July release has word wrap support e.g. Measure = "This is the first line" & UNICHAR(10) & "This is the second line".  Make sure you turn on word wrap for the grid/title for the visualization.  https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-july-feature-summary-2/

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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