Carriage Return Character in Power Query

[omezquita]

Is there a way to use the Carriage Return character in Power Query? For example, use it to split columns, combine text, replace values...

In Excel, I can substitute a carriage return by a ! using SUBSTITUTE:

=SUBSTITUTE(A1, CHAR(10), "!")

I can't find how to do this in PQ...

Thanks,
Orlando

 

[kerygma]

The character is "#(cr)". If you go to the split by delimiter option, then select custom from the drop down, and click advanced, then special characters below that, and select carriage return, that should get you the ability to split by carriage returns in your text. To merge, you have the same options. However, after putting that step in, go to the formula bar and correct the replaced value from "#(#)(cr)" to "#(cr)". I cannot figure out how to replace the carriage returns, as it doesn't seem to be working for me to use that same #(cr) and replace. Sometimes what we really have are line feeds instead of carriage returns, and those are #(lf). Same process applies as to carriage returns.