2 Replies Latest reply on Jul 24, 2012 6:57 AM by Manjit Varwandkar

    Tableau does not recognize excel formulas as #

    Manjit Varwandkar

      Hi,

      I have an excel sheet with some measures that are numeric values and others that I calculate using excel formulas. When I pull this data over in to Tableau, the calculated measures are defaulted as dimensions (text). The moment, I convert the data type for these measures to 'number', the values in the data go to 'Null'. I get the same result if I first convert the dimension to a measure and then change the data type to number.

       

      Any help/ pointer in the right direction much appreciated.

       

      Thanks

      Manjit

        • 1. Re: Tableau does not recognize excel formulas as #
          Jonathan Drummey

          Hi Manjit,

           

          Tableau uses MS JET for Access, Excel, and text files, and particularly with Excel strange things can happen depending on what the cell formatting is. In the case you describe, I'm guessing that the calculated measures possibly have extra characters being returned that cause Tableau (via JET) to treat those fields as text on import, such that when you change the data type to number they are actually not valid numbers and therefore become Null.

           

          One way to debug this is to export your Excel worksheet into .csv, then open it up in Notepad and inspect a few lines character by character. I've often found things like extra spaces at the beginning or end of fields this way.

           

          If this doesn't help, I suggest posting packaged workbook (.twbx) with some sample data and someone here on the forums can help you get to the bottom of this.

          1 of 1 people found this helpful
          • 2. Re: Tableau does not recognize excel formulas as #
            Manjit Varwandkar

            Thanks Jonathan, that was it. Tableau was unable to recognize excel values formatted as '%'. I went back and changed my excel formatting to  'Number' and it worked. I have reported this issue and I suppose it will be logged as a bug somewhere.