question

CharleyKyd-2312 avatar image
0 Votes"
CharleyKyd-2312 asked CharleyKyd-2312 commented

Replace carriage returns in a Power Query column?

I can find no way to replace a carriage return in Power Query column. This SHOULD work:

Result=Table.ReplaceValue(QtrInc,"#(#)(cr)"," ",Replacer.ReplaceText,{"AcctName"})

But it has no effect.

Instead of "#(#)(cr)" I've tried:

"#(#)(000D)"
"#(cr)"
"#(#)(000D)"

But they have no effect. I tried to split the column on the character, but still no joy.

Is this a bug? Or have I blundered?

power-query-not-supported
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RonRosenfeld-3452 avatar image
0 Votes"
RonRosenfeld-3452 answered CharleyKyd-2312 commented

"#(cr)" should work.

Are you absolutely certain you have carriage-return characters in the string?
When I try the replace here, it works flawlessly.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Yes. I have a code 13 character. And when I split the column on #(cr), that works. Text.Clean also works. But that leaves me without a space between two words in my column of labels wherever they were wrapped.

Luckily, I have only one #(cr) per item in the column. So as a workaround, I've split the column into two columns, replaced any nulls in the second column with a null string, and then concatenated the two columns. It works; but it's a kludge.

Aw, well. It's handled for now. I'll have to revisit it in the future.

Thanks for your question. It made sense.

@RonRosenfeld-3452

0 Votes 0 ·
RonRosenfeld-3452 avatar image
0 Votes"
RonRosenfeld-3452 answered RonRosenfeld-3452 edited

Just replace it with a space:

eg:

 let
     Source = Table.FromList(
         {"abc" & Character.FromNumber(13) & "def"},
         Splitter.SplitByNothing(),
         {"Column1"}        
         ),
    
     replaceCR = Table.ReplaceValue(Source,"#(cr)"," ",Replacer.ReplaceText,{"Column1"})
 in
     replaceCR

Source
130922-image.png

Replace CR
130894-image.png



image.png (1.9 KiB)
image.png (1.7 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RustyMiles-9044 avatar image
0 Votes"
RustyMiles-9044 answered RustyMiles-9044 published

When #(cf) wouldn't work for me, #(lf) did work. ie Line Feed

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.