Although you can inject linefeeds (“#(lf)”) and carriage returns (“#(cr)”) in Power Query, lines will not break in the Excel-display. This simple trick makes your lines break nicely in Excel:
- Click into the formula bar
- Click back into your sheet & the lines will be broken:
3. Apply that format to the whole column.
4. Make that format stick for future refreshes: Table Tools -> Design -> External Table Data -> Properties -> Check: “Preserve column sort/filter/layout”
Enjoy & stay queryious 🙂
Hi Imke, you can also just format the column to Wrap Text in the Excel table and it will wrap the line feeds.
Thanks Mynda, that’s definitely easier 😉
Hello, Thank you for the tip. A few things…
I’m using Office 365.
Clicking in the formula bar works for me. Great.
However, applying format to whole column is not working
Wrap text is not working.
It also won’t retain the settings on refresh.
Any ideas would be appreciated!!!
Sorry Jeaux,
but I have no further insight here.
/Imke
Hello Imke,
I am having the same issue as Jeaux. I originally tried what Mynda suggested but that did not work then I found this article and it works when I click in the formula bar but does not work when I try to apply the formatting to other cells. What version of MS Excel were you using when you applied the format to other cells and got it to work? Thanks in advance!
Regards,
Ryan
I answered my own question. I was just using #(cr) and needed to add #(lf). Thanks! Great article!
Thank you Imke for the tip
Has anyone had any luck being able to transfer and display the “#(tab)” injections form Power Query to the Excel table?
Haven’t tried that so far.
I have found that adding both #(cr)#(lf) does the trick