Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
11,434 questions
Azure Data Factory cannot read '#N/A' value of Excel column
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(268.8, 50%, 36%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EBO%3C/text%3E%3C/svg%3E)
Burak Ozkan
0
Reputation points
Hello,
I would like to process the Excel file and the Excel sheet has a '#N/A' value. When I try to preview data, the ADF cannot read it. Is there a way to replace that value with an empty string or null value?
{count} votes
-
Amira Bedhiafi 31,391 Reputation pointsFeb 29, 2024, 3:52 PM Try to use Data Flow in ADF which allows you to handle NULL values :
ifNull({ColumnName}, 'Unknown') -
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(54.400000000000006, 38%, 15%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EQ%3C/text%3E%3C/svg%3E)
QuantumCache 20,356 Reputation pointsFeb 29, 2024, 7:22 PM Burak Ozkan Could you please Attach the sample Excel file to the Question? Is the Data Preview looks like the below, please provide us the sample excel from your side and we will try to repro on our side to help you...! thanks for your understanding.
-
QuantumCache 20,356 Reputation points
Feb 29, 2024, 7:52 PM @Amira Bedhiafi Did you verify the Expression if that function is available? Could you please add more context from your side?
Sign in to comment
Sign in to answer