From the course: Tableau Certified Data Analyst Cert Prep

Combining data - Tableau Tutorial

From the course: Tableau Certified Data Analyst Cert Prep

Start my 1-month free trial Buy for my team

Combining data

“

- [Instructor] In the first chapter of this course, you learned how to perform data transformations in Tableau Desktop. Let's now switch to Tableau Prep Builder. You can use Tableau Prep Builder to prepare your data for analysis in Tableau by combining, shaping, and cleaning your data in an easy to use tool. In my case, I want to analyze my sales by product category and month. Let's start by connecting to data. I connect to an Excel file called KinetEco Sales 2022. Because it contains only one sheet, Tableau immediately drops the table onto the flow pane as an input step. I need the sales from 2023 as well, so I connect to another Microsoft Excel file called KinetEco Sales 2023. Again, Tableau turns the single table into an input on my flow pane. Because both input steps are currently called sales, I renamed them to include the year, so Sales 2022 for the blue input and Sales 2023 for the orange one. This bottom half of the screen is called the profile pane. If it takes up too much space and you can't see your flow properly, you can change the height by dragging or you could even collapse the whole pane. You can get it back by simply bringing it up again. If it vanishes because you unclicked anything, you can also bring it back by selecting any step in your flow. I want to union those two tables, so I drag one on top of the other. You can see that Tableau gives us several options of how to combine these two tables. In my case, I want a union, so I select the option underneath the input step. Tableau now adds a union step, which both input steps feed into. A new field called Table Names is added. For all other fields, we can see which input step they came from. The colors here correspond with the color of the input step. If a field existed in both inputs, let me bring this up and scroll over a little bit, then both colors will be reflected here. If a field came from only one input like ship mode, it will be colored only partially in that color. Tableau automatically unions the fields that have the same name, but you may notice that we have a field called shipping date and one called date of shipping. We can select both by pressing Control and then right-click to merge those fields. Let's do the same for ship type and ship mode. Right-click and merge fields. I'm quite happy with my flow so far. However, I want to analyze my sales by product category, and we don't have that information in the sales tables. I need another file with the inventory. I connect to a text file called Inventory.csv. Tableau, again, inserts this file as an input step into the flow pane. We can drag it to the right of our union step to join the text file to the unioned Excel files. Tableau now adds a join step where we can specify the join clauses. I want to use SKU to join my data. Below, we can see the Venn diagram that we already encountered in the previous chapter. However, in Tableau Prep Builder, we also get a summary of our join results. We can see how many rows from either table are being joined, and if there are any mismatched values. In our case, all values match and are included. We can click on the Venn diagram to change the join type. I want to make sure that we always show all the sales data even if we do not have corresponding inventory data. This should ideally never be the case, but if it is, I want to know that. So I changed my join type to a left join by selecting the left half of the Venn diagram. Our join results also shows the number of resulting rows, which is more than either single table contains, indicating that we are dealing with multiplication here. We already talked about that in the last chapter, and we will address this issue later in this chapter, so stick around for the upcoming videos. On the right, you can see all the columns from both join inputs colored by the corresponding flow color. You can see that some columns exist twice because they came from both inputs with identical content. We can clean those up in one of the following videos. For now, I am happy with how my flow is coming along. I still want to analyze my data by month though. Let me show you how to aggregate your data in the next video.

Contents