4 Replies Latest reply on Apr 25, 2018 4:53 PM by Aldwin Roman

    Data Flattening

    Aldwin Roman

      I am trying to flatten a data report. After looking at a number of support pages on both Excel and Tableau, none seemed to answer my specific problem. I have report that single rows that will split into multiple rows. There can be mulitple Service and Subsidy rows for each record. There are the only two columns that have split rows. All the solutions I have found are for reports with blank cells but that is not what I am working with. I've attached a sample with only two rows. The full report I has over 12,000 rows so I'm looking for a automated way of adjusting this data. If there is another thread that can answer this please point me in the right direction.

        • 1. Re: Data Flattening
          Don Wise

          Hi Aldwin,

          Yes, your data is going to need re-structuring to be useable.  A simple pivot or use of field merging tool in Tableau data source pane window is probably not going to work for you. 

           

          There are some tools for Excel that might be able to help you with this type of situation:  https://www.extendoffice.com/documents/excel/1139-excel-unmerge-cells-and-fill.html  

           

          Hope it helps with your issue.  Thx, Don

          • 2. Re: Data Flattening
            Isaac Kunen

            Hi Aldwin,

             

            You might try using data interpreter - it looked like it worked pretty well for me on your sample.

             

            To do this, open your Excel file in Prep, and in the left-hand pane, choose the option to "Use Data Interpreter":

             

            Once you've done this, go ahead and add the sheet to the flow. This is how your sample came in for me with data interpreter:

             

            I'd love to know whether this works for you.

             

            Cheers,

            -Isaac

            2 of 2 people found this helpful
            • 3. Re: Data Flattening
              Isaac Kunen

              Aldwin,

               

              Just following up to say that I was a bit confused when I answered your question: I though it was posted to the Tableau Prep forum, so I presumed that you were using Prep. I tried to pull up data interpreter with Tableau, and it doesn't show for your file... and I'm not sure why...

               

              I'll ask around. In the meantime, I guess I'd encourage you to try Tableau Prep.

               

              Cheers,

              -Isaac

              1 of 1 people found this helpful
              • 4. Re: Data Flattening
                Aldwin Roman

                Thanks for your help Don. I'm going to try this fix for the whole data set and see if it works. I posted this to the Tableslay Prep as well as Tableau Desktop because I wasnt sure which could handle this problem. I currently have both so I wanted to see what options 8 had. Thanks again.