Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Is there a way to change Error in a Cell to NA or Blank when we Import/Refresh data from source Excel file.
I do not want to remove those rows using Remove Errors but keep and convert errors into something like blank.
Solved! Go to Solution.
yes in query editor there is option "replace error"
go to transform tab, there is arrow next to replace value and choose replace error and give the value you want to use to replace error.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
to add, if you want something in it's place that won't dilute an average calculation or similar, you'll want to replace those errors with: null
Dears,
I tried the above, but it does not work with a cloumn type %, the issue I have is the column data type is % and some times i have NA for some rows, so I need to keep the same row but show NA or blank in the cell, but when I tried the solution mentioned above (replace erorr), when I try to type/ write "N/A" or leave it blank, it asks for to write only a number because the column type is %. How can I keep the column as % and in the same time show some of the rows as blank or N/A?
Your support is appreciated.
Regards,
Shady
yes in query editor there is option "replace error"
go to transform tab, there is arrow next to replace value and choose replace error and give the value you want to use to replace error.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Once you have replaced the errors, when the database that Power BI is connected to updates the table, does the replacement also fix future invalid values that cause the error?
Hi. It will replace all errors; regardless of their cause. Not just a particular error of which you already know the cause.
@NaCl-yYes
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
User | Count |
---|---|
93 | |
83 | |
77 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |