Unpivot causing [DataFormat.Error] Invalid cell value '#N/A' on date field

[jschuchter]

Hi, I have a simple table (10k rows) with a column for date and three columns for persons. I unpivot the three columns for persons into a single columns. My date field seems fine just before the unpivot. It is a date type and when I go to filter and load all values, I see only dates (about 2 months worth in the range) and no errors. However, immediately after the unpivot, I then go to filter and load all values, I get the error: [DataFormat.Error] Invalid cell value '#N/A'.

I've triple checked the date field in Excel, and in BI, and tried to Transform > replace errors w/ null values in Query Editor. No luck.

I'm stuck because I use the date field in my reporting and reference this table a lot.

Thanks,

Joe

[amitchandak · Accepted solution]

@jschuchter , just check you number field might have got - or N/A or null or #NA

In edit query run auto-detect data type and check does it change the data type

[v-xicai · Accepted solution]

Hi @jschuchter ,

You may enter into Query Editor, remove the step "Change Type" behind of step "Source" .

Best Regards,

Amy 

Community Support Team _ Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak · Accepted solution]

@jschuchter , just check you number field might have got - or N/A or null or #NA

In edit query run auto-detect data type and check does it change the data type