User-defined fields, as well as a host of advanced features and abilities. We'll start by connecting to a variety of data sources. This will leave you with some great workbooks to practice on and develop on your own. After this course, then we'll move into creating a wide variety of charts, including Univariate, looking to support our channel and get a great deal. Become a member today to unlock ad free videos. That's right, your favorite courses without a single ad..
Interested in a specific video purchase one of our ad free courses individually, looking for even more gain access to exams, certificates, and exclusive content@learnitanytime.com. More information can be found in the video description. Below the first module, we'll focus on connecting to data sources. Now it has eight lessons in this module, and we'll start by connecting to Text, Excel, and access files. I have the files for you in the video description, and they're listed on this slide, so you're gonna want to grab US City's population..
That is a text file. Airline comparison is an Excel file, and there is an access database file called North Wind that you're gonna wanna grab as well. But wait, hold on. There's more that you're gonna grab before we're done with this slide. In the second lesson, you're gonna learn how to paste in a data source from the clipboard, and we'll be using two different Excel files for that. They're named vehicles and pricing. We're gonna use those same two Excel files in lesson three when you learn.
How to merge multiple data sources. And in lesson four, we're gonna connect to other databases. That's the title of the lesson. We're actually gonna connect to a SharePoint list. Um, if you have access to SharePoint, um, if you want to, you can take a moment and go create a quick list on SharePoint that we will connect to later in this module. The other thing is in order to connect to a SharePoint list, you're going to need a specific driver..
Description for your future reference. Now, if you wanna take a moment and pause the video and go grab the files that are noted on this slide, including the useful links Word document, go ahead and do so and then resume. Before we get hands-on in Tableau, I have a couple of other slides I'd like to review with you. And I will be referencing this slide deck throughout the course. So during this module, you're gonna learn how to copy and paste data into Tableau, and you can do that easily from a variety of office.
Applications, including Excel Word. You can also copy and paste H T M L. Tables from web pages and table set are copied as comma separated values, or tab delimited can be pasted into Tableau. So there's two things that could happen when you paste data into Tableau. If you paste data on the data source view, Tableau will create a new connection in an existing data source. If any. If you paste data on the sheet view, Tableau will create a new data.
Source that you can begin analyzing. So a connection versus a data source. And that data source would be saved as a text file to your Tableau repository when you save the Tableau workbook. So just keep that in mind. We're gonna get to that in this module. I've added a slide here about the data sources that are supported in Tableau desktop. And there's an active link on the slide where you can go in and look at the supported connectors on your own. Um, you'll see them when we get into Tableau, but I just wanted you to.
Have this slide for future reference. So go ahead and launch Tableau now and we'll get started. So I am in Tableau desktop, and the first thing we're gonna do is we're going to connect with a text data source. So it just a little bit about this home screen interface here in Tableau. Okay. You have your toolbar at the top, as usual. You have a couple of menu items there. Um, we have the connect area on the left, the connect pane where.
You can look on Tableau server. You can connect to a file to a different kind of server. There's even more. So I told you there's a lot of different data sources, also known as connectors that you can connect to. And this is where you can find them by doing the arrow next to to more. And then you have some saved data sources that come with Tableau Desktop and you'll see the sample Superstore one that we're gonna connect with a little bit later. Um, you also have accelerators and they're prebuilt dashboards.
Posts Related:
- DAX for Power BI Part 1 - Getting Started with DAX for Power BI
- How To Create An Excel Data Entry Form WITHOUT A UserForm
- Manage Anxiety to Build a Positive Mindset Office
That you can play with. And on the right side you have a more accelerators link, which - ID Card Make
You can get to if necessary. Um, if you have an existing Tableau workbook over on the right at the top, there's an open a workbook link that you can use to access that. So what we wanna do in the connect pane under two a file, we are gonna select text file and then you're gonna navigate to wherever you put the files that you bought in from the video description..The text file is US City's population. At this point, I only have one text file in my folder and I'm gonna just double click it to be able to connect with it in Tableau. We'll go into a deep dive on the Tableau interface in just a little bit in this module. But what I wanna draw your attention to right now is this data grid on the bottom, you're actually seeing the data that is in that text file that we just connected to..
So that's all the data in the text file right there. Um, it's showing a hundred rows at a time, as you'll see over here on the right, and it actually has. Like nine fields in 304 rows showing here on the left. Um, we're not gonna do anything with this file. This is one of the files that you will have that you will be able to come back to and build up later after you learn a bunch of cool things in this advance course. So what we wanna do is we just wanna save this file. So there's this little bit of a toolbar here where you can click on save or you could do control Ss..
And if it doesn't take you there automatically, you wanna navigate to your documents folder and look for a folder called My Tableau Repository. When you locate locate It, you're gonna double click it and then you're gonna double click workbooks because this is a Tableau workbook and we are just going to name this workbook, you s Cities population. And then save. We can go to the file tab there, the file menu and choose close. And then what you wanna do is right under the word file, right under the file menu, you have the house icon..
You wanna go back to the start page, which is your homepage. When you're saving workbooks, they will show up here automatically for you. You can pin them there so they never disappear. Or in our case, we're gonna do the X to say Don't show this workbook. The point of that one was how to connect to a text file. Now we're gonna connect to a Microsoft Excel file. Um, you've already grabbed this file from the video description, but when I.
Went over that slide I said we're gonna use a file called Airline Comparisons. I've changed my mind and we're gonna use the vehicles file that you brought in from the video description. So in your connect pane, go ahead and click on Microsoft Excel underneath two a file, and then I'm gonna double click vehicles to connect to it. So you'll notice, um, a couple of things here. Um, on the left side, it's showing you the connection. So it's the vehicles file, that's a Microsoft Excel file, and then that file.
Has a worksheet in it called Inventory. So what you're seeing is the data that's on that inventory sheet. Now we will be using this file again in just a little bit, so we're going to save this one, but we'll reopen it in just a little while. So go ahead and do your save and we're gonna name it Vehicles and Pricing and what. Once it's saved, we can go to file and close. And I am gonna go back to my start screen by using the home icon. So I'm gonna leave the vehicles and pricing tile here because.
We're gonna reopen that Tableau workbook in just a little while. And the last type of file that we're gonna connect to right now is the Microsoft Access database named North Wind. And before I do that, I'm going to just get this airline comparison file off of my start screen. And under two a file in the connect pane, I'm gonna select Microsoft Access. Now this one is a little bit different. When you go to open it, it doesn't just take you to where you want to go..
You have to click the browse button at the top and you'll see that North Wind database that was in the files from the video description. And I'm gonna just double click it. So we don't have a database password that's not necessary. We don't have any work group security that we have to work through. At the bottom, we are gonna go ahead and click on open. You'll notice it was processing the request and then it opens it up. And this is in data source view like we saw earlier..
And again, you're gonna learn more about this, but it made the connection, if you look at the top of the left pane, it lets you know the connection that you have
Here and it's showing all the tables in the data, in the database, as well as queries that are in the database as well. So what we're gonna do is what we've been doing so far, again, you're gonna have these three files that you can play with on your own to build them up after you learn what you're gonna learn in this course. So we're gonna go ahead and save it and we're gonna call it North Wind..And go ahead and close it and go back to your start screen. Now we're going to paste data into an existing workbook. Um, I got rid of my North Wind tile here, and I'm gonna use the vehicles and pricing tile to reopen that Tableau workbook. And when you open a workbook, it takes you to a different view. This is called Sheet View. If you look at the bottom of your screen, you'll see that it's on the sheet, one tab there..
And what we wanna do is we wanna switch over. To the data source tab, which is to the left of sheet one. So go ahead and click on data source to get to that tab. And this is what you've been used to seeing. So what we're gonna do here now is we're going to just switch over to the directory where you brought in the files from the video description and open the file named pricing in Excel. And now I'm gonna just click anywhere in this data and I'm gonna do control.
A C to select and copy all the data. And then I'm gonna switch back over to Tableau. If we were to use Control V here to paste and data source view, let me show you what would happen. Let's go to the data menu. And you'll notice that in data source view you have two paste data options. The one with Control V is what makes a connection. Now, technically you could paste it in as a data source here,.
But the default behavior is pasting it as a connection here. And let's go back to sheet one for a moment and look at the data menu and you'll see that there's only one paste option. And when you're in sheet view, it will only paste it as a data source, not a connection. You don't have an option here. So control V here means paste it as another data source..
Let's go back to data source view. And I'm gonna just do Control V here. And you'll notice on the left that I have a new connection. It's called clipboard. And it has a string of numbers after it. The first numbers before it gets to a letter represent the date that you copied it to your clipboard. And so that's one way of getting more data in. If you had it, if it was an H T M L table from a website, it would come.
In as another connection along with the connection that you have existing. Let's do the dropdown arrow next to that clipboard item and choose remove. So we added it as a connection. We remove the connection. Now let's go back to sheet one. And in sheet one we're gonna do control V. So it's still on the clipboard for us..
And if you look on the left side, we're on the data pane there. We have a clipboard entry, the same name, but it puts it in here as a new data source instead of a new connection to an existing data source. And you could tell the data source, 'cause it has the database icon in front of it. So if you paste it into using the default control V, if you paste it in sheet view, it's going to become a new data source. If you paste it in data source view, it will create a new connection..
So what we're gonna do here now is we're gonna close this file, but we're not gonna save the changes because we're gonna marry these two files in a different way in our next lesson. So let's go ahead and go to file close and then say no, you don't wanna save the changes. Before we move on, um, to merging multiple data sources in Tableau, there's some.
Slides I wanna go over with you, but before I forget, switch back over to the Excel pricing file and close it. The first slide I wanna go over with you is doing a deeper dive into data source view. So we've seen this view several times when we have been connecting to our data sources so far, but what I wanna just do a deeper dive in, just so you get familiar with what things are called and so on and so forth. As you work in Tableau, on the left side of the screen where you have the.
Letter A that is known as your left pan, it displays details about your data. So it's letting you know what connection is there, what sheets are there, so on and so forth. And then in the upper center where it says sample superstore and big letters there, that is known as the relationships canvas. And you'll see that there is a relationship between the orders and the returns table in that sample superstore file..
And the line between those two table panes is known as a noodle underneath that layer. Okay? The relationship canvas, there's another canvas called the joins canvas or the joins unions canvas. And that's indicated by the letter C on this slide. So right here, this is your joins canvas. It's not visible when you first go in, you have to do something to make it show up. And you'll see what that's used for in just a little while. On the bottom of the screen in the background, you have your data grid..
That's when we, where we've been looking at the data that we've been connecting to and to it's left is another grid called the metadata grid. Here's some useful information for you in terms of the data types that are supported in Tableau, and you can change the data types of your data that you're connected to in Tableau, which you'll see in this module. So I said that when you're in data source view, the default view is the relationships canvas. That's also known as the logical layer, and you use that layer to.
Combine data, you using relationships. When we looked at that returns table, that was kind of like a pop-out on the slide. That's the other layer that you can get to. So there's a physical layer underneath the logical layer, and you use the physical layer to combine data between tables using joins and unions. Each logical table contains at least one physical table, and it's also known as the join union canvas..
So this one talks about the difference between relationships, joins unions and blends. Relationships do not merge the data but keeps it separate and it's based on a common field. Joins are also based on a common field, but they actually merge the data from the same source. Joins will combine the data and then will aggregate it. Unions also merge table data, but unions append the related fields.
As rows in the merge data set. And then you have blends, and that's an approach to combine data from multiple varieties of sources and display them on a single screen. Blending will aggregate the data and then we'll display the combined data. Now, because we're talking about relationships in this module, there are two relationship terms at the bottom of the slide, cardinality and referential integrity. Don't worry about them for now. You'll get to see what they mean as we work with relationships.
In just a little bit. There's another slide here for your future reference with the cardinality and referential integrity options available in Tableau. And I've included a few slides indicating the join types that are available to you. So back in Tableau, I am going to reopen our vehicles and pricing workbook, and I'm gonna make my way to the data source tab. So at the top of the data tab on the left next to connections, I'm gonna go.
Ahead and click add, and I'm gonna choose Microsoft Excel and navigate to where you have your files and open the pricing file. So now I have two connections in here. I have vehicles and I have pricing and vehicles, has the inventory sheet. And if you look under sheets here, you'll see there's a pricing sheet, which is from the pricing file. So what we're gonna do is we're gonna drag that sheet right onto this relationships canvas..
And so it creates a relationship based on a common field, or it's going to create a relationship based on a common field. Now both of these files have a then column in it. And so if you look at your data grid at the bottom, it's showing you the then column from the pricing file. And that file also has dealer cost and manufacturer suggested retail price. If you hover your mouse over the noodle, joining those two tables, inventory.
And pricing, you'll see that the relationship is inventory to pricing. The cardinality is many to many. So many inventory items are defaulting to many prices. And then you have your related fields there. So then from the inventory table equals VIN from the pricing table. And if you look down here in your data grid to the left, there's you learn more link. How to relationships differ from joins where relationships is not combining or merging the data, it's just relating it..
And you see that it's using the VIN number from inventory equals the VIN number from pricing. And so it created that relationship. Now Tableau is really good about that as long as you have a matching field with the same name. Now the fields don't have to have the same name if they don't. Tableau probably will not be able to create the relationship for you automatically like it did here. So right now we're looking at the relationship canvas, and this is.
Also known as the logical layer. To get to the physical layer, we're gonna double click on that inventory table on the canvas. So when I double click it, it goes to the physical layer where is where you, which is where you create your joins and unions. It's also known as the join union canvas. It lets you know that inventory is made of one table. So remember from the slide, even if you're only using one table, it will show in both the logical and the physical layer. Now we want to create a join between the inventory and the pricing tables..
So what we're gonna do here is we're gonna grab the pricing sheet and drag it and drop it into the canvas. And it automatically creates a join between the two tables. So it's saying if I hover over the join symbol, it's an inner join of inventory. Notice it appended the pricing table with the number one, right? And it's based on the VIN field in both tables..
So an inner join in the inner join, only the common matching data between the two tables is displayed. And that's what we're gonna have here. Now, if I wanted to change the join type, I can just click right on that join symbol, and you'll see the other join types that are there. We're not changing it, but I just wanted to let you know that you could, if you wanted to. And I'm gonna close that join type window. And now if I look at the bottom, it's merged the data, right?.
So you'll notice in your data grid down here, it's letting you know underneath the data type symbol, it lets you know which table it's pulling from. So there your inventory tables, the first, the, your inventory tables are the first set of fields, and now you have your pricing tables as your second set of fields. And since we don't want the then showing twice, I'm gonna hide the.
Then column from the pricing table. And I'm gonna just right click on the column header and choose hide. So there's our merged dataset. Now if we wanna go back to the logical layer at this point, we can do the X in the upper right hand corner of that physical layer. And it brings you back to the logical layer. And again, in your data grid, you can see your combined merged dataset. Also, if you notice up here in the canvas, it lets you know if you hover over inventory, um, it lets you know it's logical table and it's.
Physical tables, and it also gives the symbol for the join there so you know that those tables are joined. So the automatic relationship that happened is because the different tables have a common field, it was able to automatically create the relationship for you, but that did not merge the data together. That just related those tables to each other. We used the join from the physical layer in order to be able to merge the data together. And then we got rid of the redundant VIN column that we.
Didn't need in our merge data set. Go ahead and save this file and you can go ahead and close it. So now we're ready to connect to a SharePoint list. So I'm gonna go back to my home, start my home icon to get back to my start screen. And I'm gonna go ahead and get rid of my TE vehicles and pricing tile here. And this time where it says connect on the connect pane on the left,.
We're gonna go to the more arrow. So we saw this a little bit earlier. These are all the connectors or the data sources that you can connect to. And on that list, in the middle, toward the bottom, you will find SharePoint list. And that's what I'm going to select. So this is the one that you will need a driver for. Now, if you don't have the driver, it will let you know as you go through this process. So you have to give it the U R L to your SharePoint site and.
