Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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}))
User | Count |
---|---|
87 | |
84 | |
67 | |
62 | |
57 |
User | Count |
---|---|
138 | |
112 | |
94 | |
82 | |
71 |