How to replace specific characters in data from SharePoint to Power Query?

[rizzo93]

My form on SharePoint has a multiple-choice field. When I bring that data into Excel (as a connection), it displays with extra characters like this:

Bruce Wayne;#5
Mickey Mouse;#20;#Clark Kent;#5
Daffy Duck;#17

When there's more than one name, I want "/" to replace those characters. When it's just one name, I want the characters removed like this:

Bruce Wayne
Mickey Mouse/Clark Kent
Daffy Duck

I'm using Power Query to process this and other columns of data but struggling to figure out how to make this particular change. All ideas are welcome, thanks!

 

[jdellasala]

You'll want to use the Advanced option when replacing special characters:

The #() notation is known as Character Escape Sequences:
From the M Language manual:
#(000D) // short Unicode hexadecimal value
#(0000000D) // long Unicode hexadecimal value
#(cr) // compact escape shorthand for carriage return

Here's a way to determine what character is the problem:

Book1
	C	D	E	F	G	H	I
7	LF	CR		LF		CR
8	This has CR	This has LF		84	T	84	T
9				104	h	104	h
10				105	i	105	i
11				115	s	115	s
12				32		32
13				104	h	104	h
14				97	a	97	a
15				115	s	115	s
16				32		32
17				10		13
18				32		76	L
19				67	C	70	F
20				82	R
Sheet1

Cell Formulas
Range	Formula
C8	C8	="This has "&CHAR(10)&" CR"
D8	D8	="This has "&CHAR(13)&"LF"
F8:F20	F8	=CODE(MID(Sheet1!$C8,SEQUENCE(LEN(Sheet1!$C8)),1))
G8:G20,I8:I19	G8	=CHAR(F8#)
H8:H19	H8	=CODE(MID(Sheet1!$D8,SEQUENCE(LEN(Sheet1!$D8)),1))
Dynamic array formulas.

 

[rizzo93]

Thank you, @jdellasala.

I don't entirely understand your solution. Are you recommending a multi-step approach by replacing the unwanted characters one at a time in Power Query?

You mentioned "a way to determine what character is the problem." But it's multiple characters that are the problem. It's a combination of ";#" and the numbers that follow those characters (;#20;#) when more than one name appears in the field, and when they appear at the end of each value.

If you could show me an example using the sample data I provided above, perhaps that would help.

 

[jdellasala]

Thank you, @jdellasala.

I don't entirely understand your solution. Are you recommending a multi-step approach by replacing the unwanted characters one at a time in Power Query?

You mentioned "a way to determine what character is the problem." But it's multiple characters that are the problem. It's a combination of ";#" and the numbers that follow those characters (;#20;#) when more than one name appears in the field, and when they appear at the end of each value.

If you could show me an example using the sample data I provided above, perhaps that would help.

Yes, you will have to filter each character or sequence of characters separately if you only use the UI. Search YouTube for Power Query filter using list for a solution where you can create a table of problem characters, bring that into PQ and use it to filter a column. That's more than I know off the top of my head, and I have to catch a plane!

 

[rizzo93]

Yes, you will have to filter each character or sequence of characters separately if you only use the UI. Search YouTube for Power Query filter using list for a solution where you can create a table of problem characters, bring that into PQ and use it to filter a column. That's more than I know off the top of my head, and I have to catch a plane!

Much appreciated, @jdellasala!