How to filter #N/A only in Power Query?

[TungNguyen_19]

Hi everyone,

Today I have to import over 20 files Excel to Power Query and I face the Error which point out that there are #N/A in the files.

The problem is I can not use Replace Error option because there are other Errors in the same column so I only want to filter out N/A value and Replace it with null

Also I have try filter function which I place in "N/A" string but it can not be find.

Please help me to do this, many thanks to you guys ❤️ 

[Ahmedx]

Refer to this solution

https://community.fabric.microsoft.com/t5/Desktop/Invalid-cell-value-N-A-An-unexpected-exception-occ...

[TungNguyen_19]

Hi, thanks for your reply. I have read it but refer to this "a replace function to check all the data fields to replace the NA to null " I don't know how to create this function by M language. Can you please show me how to do this if you know?

[rubayatyasmin]

Hi, @TungNguyen_19 

Excel #N/A values are the result of formula error, you can use IFERROR and ISNA in Excel to filter the N/A. 

refer: Excel Find and Replace #N/A - Super User

How to correct a #N/A error - Microsoft Support

[TungNguyen_19]

Hi thank for your reply but I want to do this in Power Query with M language 

[Ahmedx]

try the following:
1) remove the change data type step
2) remove errors
3) apply the new data type

[TungNguyen_19]

Sorry but I don't want to remove rows contain N/A which cause error in Power Query, I just want to filter it and replace it with null (But other solution beside "Replace Error" function)

[Ahmedx]

see my video

= Table.ReplaceErrorValues(tbl_Table, 
 List.Transform( Table.ColumnNames(tbl_Table), (x)=> {x, null}))

https://1drv.ms/v/s!AiUZ0Ws7G26RiyunRkiuBZLwuHO5?e=0gTbGj