Search code examples
powerquerym

Can I use ASCII characters (specifically char(10)) when concatenating text fields in Power Query?

My CRM system requires address lines before town/city to be imported into a single field, with separate lines (where there are more than one) separated by the delimiter char(10). I need to import a file which has two columns for this data, so I need to merge the columns into one.

This is what I've tried in Power Query

[address line 1]&char(10)&[address line 2]

However, I then get an error message saying "The name 'char' wasn't recognised. Make sure it's spelt properly".

Any ideas? I feel like this is the opposite of what most people want to do in Power Query, which is separate out text strings... I actually want to squeeze it into one column!

Solution

  • Oops, no sooner had I posted than I figured it out! The correct answer is 

    [address line 1]&Character.FromNumber(10)&[address line 2]

    Edited to add: the below is better - the above code will only work if both fields are populated.

    if [Address Line 2] is null
then
[Address Line 1]
else
[Address Line 1]&Character.FromNumber(10)&[Address Line 2]