Have lots of messy sub tables in Excel you want to analyze? Say goodbye to hours of tedious cut/paste work. Now, union and pivot multiple groups of data in Excel spreadsheets with the newest release of Tableau Prep (2018.1.2). You can also connect to more data sources—Presto DB and statistical files from SAS, SPSS, and R. And we’ve obsessed over the little things. You’ll notice a lot of actions have gotten faster. Lasso select steps in the flow to delete them in one click, see your data refresh instantly when you edit data values, and experience an even faster drag-and-drop experience when connecting to large databases.
Let's explore how working with spreadsheets is even easier with these latest features in this release.
Use wildcard union on sub-tables
Excel spreadsheets often contain complex reports spread across multiple worksheets. Below is an example that has multiple, similar worksheets with two tables on each – a crosstab of monthly sales and tax data and a small summary table. It's often hard to access the data in these sub-tables and combine them without a lot of manual cut/paste work to reshape the data. With the new Tableau Prep you can easily wildcard union the sub-tables discovered by the data interpreter!
The data interpreter will scan the spreadsheets to look for sub-tables and add them in the left Connections pane of Tableau Prep. It finds four sheets (2015, 2016, 2017, 2018) and four sub-tables starting on cell A2 of every sheet—one for each year (highlighted in the screenshot below).
Normally we’d have to bring in each sub-table and union them, but what if you have tens or hundreds of sub-tables? Now we can use a wildcard union to read in sub-tables in one step.
Create multiple pivot groups
Now we’ve combined the sub-tables, but the data is still in crosstabs, which can be hard to analyze. We have 97 columns of Sales and Tax for every month. These need to be pivoted at the same time. We can easily reshape this data with Prep’s new pivot experience to create multiple pivot groups.
Our data is now ready for analysis! Try Tableau Prep today and start quickly and confidently combining and reshaping your data. Tableau Prep is available at no extra charge to Tableau Desktop customers that are on a subscription license or are current on maintenance as of April 24, 2018.