Forum Discussion
How to filter #N/A only in Power Query?
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 ❤️
7 Replies
try the following:
1) remove the change data type step
2) remove errors
3) apply the new data typeSorry 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)
see my video
= Table.ReplaceErrorValues(tbl_Table, List.Transform( Table.ColumnNames(tbl_Table), (x)=> {x, null}))
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
Hi thank for your reply but I want to do this in Power Query with M language
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?
