How to display Power Query results with line feed or carriage return

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:

Pic1

  1. Click into the formula bar
  2. Click back into your sheet & the lines will be broken:

pic2

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 🙂

Comments (10) Write a comment

      • 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!!!

        Reply

          • 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!

  1. Has anyone had any luck being able to transfer and display the “#(tab)” injections form Power Query to the Excel table?

    Reply

Leave a Reply