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
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 ❤️
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?
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
Proud to be a Super User!
Hi thank for your reply but I want to do this in Power Query with M language
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}))
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |