'Number' field stuck on 'Text' in Power Pivot

[m5edward]

Hi Everyone,

I have an extremely annoying issue. Any help would be greatly appreciated.

The Issue

I work for a retail company and would like to compute the average sales per square foot of each store and banner in a pivot table, like I have done a hundred times. When I try to do so, I get this error message.

For some reason, Power Pivot will not let me convert the column 'square feet' from 'text' into a either 'whole number' or 'decimal number'. I get this error message when I try to do so. There do not appear to be any '#N/A' in the column.

Naturally, I went to the start of the data flow and checked to make sure square footage was formatted as a number, not text, across the entire chain.

My Dataflow

My department (real estate) uses a single Excel file that contains all things related to real estate, including store sizes and sales.

I created a separate file for this report and used power query to bring over the necessary data. The data sits in multiple worksheets (in tables), and those tables were loaded into Power Pivot.

In the table, I tried creating a second column for the store size which is simply 'Store Size*1' to force it into being a number. Both columns for store size are formatted as 'Number'.


Any ideas why this is happening?


Thanks,

Mark

 

[RoryA]

If you filter the Square feet column in the relevant table in Excel, does #N/A appear in the filter dropdown?

 

[m5edward]

If you filter the Square feet column in the relevant table in Excel, does #N/A appear in the filter dropdown?

Just figured it out. There was a store closure that I wasn't aware of and it was screwing things up.

Thanks for your response, much appreciated!