Power Query: Replace All Text and Number With Null (Bulk Replace)

[muhammad susanto]

hi all
i have problem how to replace column (bulk replace) using formula
i have 2 situation:
1. replace any text with null
2. replace any number with null
3. replace combine text and number with null
here my layout table

tranpose complex.xlsx
	A	B
1	case1
2	Region	Region
3	KPKNL testing	null
4	sample	null
5	text	null
6	jjhn	null
7	mika	null
8	sissa	null
Sheet2

and

tranpose complex.xlsx
	A	B
10	case2
11	Region	Region
12	45	null
13	23	null
14	1,2	null
15	0,23	null
16	1000	null
17	240	null
Sheet2

anyone help, thanks in advance

susant

 

[alansidman]

#"Added Custom" = Table.AddColumn(#"Trimmed Text", "Region", each null)

 

[muhammad susanto]

hi alan, how about in my case #2 (number)?

 

[muhammad susanto]

#"Added Custom" = Table.AddColumn(#"Trimmed Text", "Region", each null)

i don't see the code work. you don't use 'let......'

 

[alansidman]

just gave you the line of code that you would add once you loaded your table to PQ. Again, suggest you get the book, so you don't have to be spoon fed for every line and really understand how PQ works.

 

[alansidman]

All three result in a null, so you don't need to distinguish. What other possibility could you have, text, numbers, text and numbers all result in null so you don't need an if then situation.