Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
TungNguyen_19
Helper III
Helper III

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 7
Ahmedx
Super User
Super User

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
Super User
Super User

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

 

rubayatyasmin_0-1689517080227.png


Did I answer your question? Mark my post as a solution!super-user-logo

Proud to be a Super User!


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

Ahmedx
Super User
Super User

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

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)

see my video

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

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.