Expression.Error when Pivoting Colum

[amconnel]

All,

I have a column labeled 'Metric' where I have listed out things like Net Sales, EBITDA, COGS, Tax, etc. and another column labeled 'Value with the corresponding values for those metrics. I need to pivot the 'Metric' column so that I have columns for Net Sales, EBITDA, COGS, etc. with all corresponding valyes shown underneath. 

I am receiving the following error when attempting to pivot the column:

Expression.Error: We cannot convert the value null to type Number.
Details:
Value=
Type=[Type]

I have tried the following to fix the issue:

- Checked that the 'Metric' column is text.

- Checked that the 'Value' column is decimal number.

- Replaced all null and blank values in the 'Metric' column with the word "blank."

- Replaced all null and blank values in the 'Value' column with "0."

Any ideas on how else to resolve this error? 

Thank you!

[amconnel · Accepted solution]

I believe there was a file that had been appending with the original query that was causing the problem. This additional file did not necessarily have columns that aligned fully with the original query, so there may have been more than one data type in at least one of the columns I was handling. 

Once I removed this problem file from the source in the query, the pivot column worked like it should. The files do not necessarily need to be in one query; just thought it would be convenient.