2 Replies Latest reply on Apr 28, 2017 2:34 AM by Michael Elser

    Bug? Losing Pivot option after refreshing Excel data source

    Michael Elser
    Michael Elser Apr 27, 2017 8:30 AM

      Hi,

       

      (The following problem is a bit difficult for me to describe so I attached a video demonstrating the possible bug as well).

       

      I am working with Tableau 10.2.0 and have successfully imported an Excel data source. Using the pivot function I transform columns into a single field.

      Now, I change the value of one of the column headers in the Excel file. Next I refresh the data source. Tableau will kick the changed column out of the pivot. This is a bit annoying but I can live with it since it is quite rare to change the headers rather than the actual data. In any case I can get the missing column back in by first removing the pivot and redefining the pivot from scratch.

       

      However, if I change ALL the headers in the Excel file, all columns are kicked out of the pivot. Now I don't have the possiblity to remove the pivot anymore as the pivot colums that contain this option are gone as well. More importantly, I have not found a way of re-doing the pivot other than deleting and redifining the data source which is not a workable solution.

       

      Again, this is probably quite a rare use-case but it did happen to me in the very first workbook I wanted to make. Can this be fixed or am I maybe missing an option?

       

      Best regards,

       

      Michael

        • 1. Re: Bug? Losing Pivot option after refreshing Excel data source
          Galen Busch
          Galen Busch Apr 27, 2017 8:38 AM (in response to Michael Elser)

          Hi Michael,

           

          Tableau, along with most (vast majority) of BI tools depend on column headers. Sometimes when I hit a problem with Tableau, I think about what I'm asking it to do.

           

          Data sources, whether you're using Excel, SQL, or anything else, feed Tableau with column headers. Columns can move around in order of appearance, data can get rearranged, but one thing stays constant: column headers. There is not a 'metadata' layer that Tableau reads for column headers. It simply doesn't exist.

           

          In data warehouses, (Excel) column headers don't change - they can't. They are referenced in views and tables by their header. The reference is static - the column header must be static. Tableau is acting precisely how it should.

           

          When you pivot your Excel sheet, you are defining which headers should be included in the pivot. If you went in your excel sheet and re-arranged your columns, your pivot wouldn't break.

           

          I know it isn't the answer you're looking for, but one absolute must of business intelligence in general, regardless of the visualization tool or data source you use, is to leave column headers alone. You can always alias them in the very end product.


          Galen

          1 of 1 people found this helpful
          • 2. Re: Bug? Losing Pivot option after refreshing Excel data source
            Michael Elser
            Michael Elser Apr 28, 2017 2:34 AM (in response to Galen Busch)

            Dear Galen,

             

            Thank you very much for this great explanation. I can understand the technical constraint and knowing this I can certainly change my data file to have fixed month names only and get the year from somewhere else.

             

            Best regards,

             

            Michael