Tableau VS Power BI – How To Go From Data to Dashboard

A transcript of this video follows:

[00:00:00] Steve: Hi, my name’s Steve Adams. I’m the founder of VizDJ.com, where we give people the ability and confidence to build better dashboards in Tableau more quickly in order to save and improve peoples lives. Today I am joined by Andy, who is going to introduce himself shortly and we are representing Tableau and Power BI. What we’re going to do is just look at a very high level detail compare and contrast the two tools to see how they do things differently. They obviously ended up with very similar outcomes in some respects, but how we get there can be very different. So today’s video is going to cover a number of topics.

We’re going to look at how we import data into the two tools. We’re going to look at how we can clean up some rows and columns that we don’t want in there. We’re going to look at excluding some specific rows from our data, because there may be duplicating data with subtotals, for example we’re going to unpivot data.

Where are we going to take a bunch of columns and pivot those down so that they create a better data structure to analyze. Then we’re going to build some charts and we’re going to build a Treemap. We’re going to build a bar chart and we’re going to build a line chart & combine all those together into a dashboard and add a little bit of interactivity.

Before we dive into that, I’d just like to hand you over to Andy to introduce himself.

[00:01:33] Andy: So my name is Andy Brown. I’m co-founder of Wise Owl with my wife, Jenny, 30 years ago now, and we do training in Excel in SQL server and programming. And of course in Power BI,

Tableau VS Power BI on: Importing Data

[00:01:50] Steve: thanks for that, Andy. And without further ado, let’s get into it. So I’m going to pop open Tableau. And this is a workspace screen where we can create charts. Typically there are other screens, but we’re going to cut through to the bare necessities for today. I want to connect through to data. I’m going to click on the connect to data.

We then choose the connection type in terms of where the data is coming from. We can see here, we’ve got a Microsoft Excel which is the data type that we are looking at today. So I’m going to click Microsoft Excel. I’m going to navigate on to my computer to where that is. And I’m picking up the EU Superstore sample data Andy’s very kindly allowed us to use the standard dataset from Tableau.

Although I have manipulated it a little bit, which you’ll see if you’re a regular Tableau user. We’re now on the data source screen, where we get to visualize a little bit what the data looks like from a Tableau perspective. So we can see here that it’s a little bit dirty. Why can I see that? Because the column headings are just coming up as F1 F two of three, and it’s not picking up what that data actually looks like.

If we look down here, we see that actually the. Headings are in row three or what customer ID category, Sub-Category, et cetera, et cetera. So there’s a couple of rows which are dirty. This data is a standard Tableau data set for demonstrations and for training purposes, it’s got a whole bunch of dimensions in here.

And in this case, we’ve also got some columns which are by date, giving us a particular measure, which is quantity. So we can see, we start at January 18 and the quantity values scrolling. To give us the data all the way over to the latest data . There’s a lot of NULLs in there. That’s not a very good shape for either Tableau or Power BI.

So later on, we’re going to want to pivot that data so that we have one column with the data. And then another column with the quantity value in there. The other things we’ve got going on with this dataset is it’s coming from an Excel spreadsheet, which is being used for a report. So it’s got a title here, top level, row with a title.

And so there’s a couple of rows here, which we’re not interested in either. Likewise, there’s also columns here with no data at all because it’s a report that we want to clean up. And we don’t see them on here, but there’s also some subtotals which have been added into. To bring in a subtotal for all of the different category values which there are three of furniture, office supplies and technology.

So that’s how we connect through to data in Tableau. The next thing I’m going to do in Tableau is just clean up those columns and rows. So how can I do that? Well, we could use another tool which we have in our Tableau suite, which is Tableau Prep. But actually what we need to do right now is very basic.

And Tableau has this data interpreter capability over here, which. Click on. And what that’s going to do is it’s Tableau is going to then analyze the Excel spreadsheet and focus in on what it believes are the data areas on that spreadsheet. And it’s going to do that automatically. I can’t really do much about whether it gets things right or wrong, but to be honest, it gets a lot of things.

Right. First time, in this case, for example, you can see now that we’ve connected through, we cleaned it up and Tableau is now seeing and recognizing that top row. Of headers as being the the names that we’re looking for. It’s also not looking at the empty columns, so it’s removed those completely. And that is us connecting through to the data in a much more clean way.

Andy, how does Power BI do that?

[00:05:26] Andy: So Power BI doesn’t have a data interpreter site, like Tableau does. Instead what happens is when you load some data, it applies a series of sensible steps. Now it hasn’t quite gone far enough. So what I need to do is also removed the top row, which contains just NULLs. And then what I need to do is promote the first road to make them into headers column headers.

And finally, I need to lead the blank column. Which are columns one, four, and I think nine as well. So we’ll just select those columns. And I normally just press the delete key, but I’ll choose remove columns. So that takes us to the same place in Power BI as Steve got to in Tableau.

Tableau VS Power BI on: Exclude Rows

[00:06:12] Steve: We have now got our clean state, but we’ve got a slight problem. And that is on the Excel spreadsheet. We have some subtotals that have been introduced. Let me just show you those. So here’s the original underlying Excel spreadsheet. You can see here with somebody put a subtotal in and we’re adding up all the values above it.

And we’ve got those in Customer ID column and they’re writing up the category. Subtotals, obviously that means we’re double counting. So we want to remove those in Tableau. We can go to our data source filter, and I can add a filter on here. And there’s different ways I could do that. I could go to my customer ID, for example.

And I could find the row by looking for the value, which is written on there, which is subtotal. I could click on there and I can exclude those. Or perhaps I could go to another column, let’s go to the category column itself, for example. And in there, we’ll see that there are the three categories. We’ve also got the null, in which case I can exclude the NULLs.

So that means any row, which hasn’t got any data in that particular column is going to be excluded from our data.

[00:07:18] Andy: So, What I want to do now is to remove all the subcategories which are NULL, but if I click on the drop arrow next to it, to filter by that column, you can see NULL isn’t one of the values. Now there’s two clues as to why this might be the case. It says lists maybe incomplete there. And at the bottom it says column profiling is based on the top thousand rows.

What happens is when Power BI brings in the data to speed up your processing. It only brings in a representative sample one way to make sure it brings in everything there’s many others is to choose to sort by a column. And then this forces as Power BI to bring in absolutely all the data. But I can now do, is click on the same little icon and choose to exclude the null values I’m going to choose.

Okay. I’ll have the same data that the Tableau part of the video did.

Tableau VS Power BI on: Pivot Data

[00:08:06] Steve: Now the last step in our data cleanup in the Excel spreadsheet, you can see here that we had different columns for each of the quantities by month. We want to pivot those by selecting them, clicking on the dropdown and choosing pivot and Tableau has now created two columns from all of those separate data columns.

The first one called “Pivot Field Names”, ” Date”. I’m going to rename those “Quantity”. And I’m going to check the data types. So here we have. A number that’s correct. And that here for the date, we can see that Tableau currently seeing that as a string. That’s what the ABC means by click on there. I can select date. And now Tableau is going to read that column as a data is made every single one of those months, that the first of the month, so that we can analyze that correctly later on.

[00:09:16] Andy: So to unpivot data, as it’s called in Power BI is virtually identical as it was Tableau, what I can do a slit. My first. Scroll all the way across to the right, holding down shift key click on the last column and then right. Click them and choose to unpivot the selected columns. And just as in Tableau, what it will do is create two new columns called attribute attributes and value, which I’ll rename to “Date” and “Quantity”.

Now the date column, needs translating into a data, you can see it’s coming as text. So I’ll change that to a date column. And it does the same thing in Power BI. It makes it the first day of each month. And for the quantity column, for some bizarre reason, which I don’t quite understand this decided some of these are dates.

So what I’m going to do is change this to a whole number. So that’s the end of my data transformation process and extraction. What I now need to do is load it into Power BI desktop with this additional step I need to close and apply it. And I’ll be taken back into Power BI desktop, and you can see there it’s brought in the table with all the fields.

Tableau VS Power BI on: Treemap

[00:10:27] Steve: Cool. Thanks for that, Andy. We said we’re going to draw a couple of charts. And in Tableau we create each individual chart on a sheet, much like a. Workbook. We have worksheets within our workbook for Tableau. So on my first sheet, I’m going to create a Treemap. There’s multiple ways we can do this. I’m going to take the quick way.

I want to do a Treemap by country, so I’m going to select my country and I’m going to do it. Multi-select on my measure in this case, it’s going to be quantity. So that’s a clicking the control key, and I can now go to “Show Me” which gives me a list of different charts that we can create. One of which is this Treemap.

You’ll notice that some of them are grayed out that’s because I’ve only selected a particular combination of dimensions and measures depending on what your selection is. You’ll see different options available to you. I can click on Treemap. And there we have our Treemap and you’ll notice what Tableau is doing is it’s color coding those automatically based upon the measure so that we can see that France is the darkest blue, because that’s got the highest value of quantity. Over to you, Andy.

[00:11:38] Andy: So to create the Power BI version of the Treemap, I’m going to just going to go to my visuals on the right-hand side, as they called click on the Treemap, Visual, and then choose which fields I want to display in it. You can see everything’s added on the same page. I could, if I wanted to add other pages to my report.

So what I do is choose the country and region and the quantity, and that will create my true. Unlike in Tableau, it doesn’t automatically come in with conditional formatting set. So I’ll go to my properties and I’ll choose my colors and I’ll choose advanced controls. As it’s called to apply something called conditional formatting.

I’m going to choose a gradient. I’ll just leave all the default set. And when I choose, okay, I should get a Treemap, which looks very similar to Steve’s.

Tableau VS Power BI on: Bar Chart

[00:12:27] Steve: That’s great. And now I’m going to just rename my worksheet so I can refer to that later on and click on another one to build our bar chart. So I’m just going to call this worksheet “Bar” . Now I’m going to bring in Sub-Category. And we’re going to look at subcategory quantity values on a drag that onto our shelf, you can see up here, we’re doing a visual drag and drop with Tableau.

And these items here are referred to as pills. And we have green pills and we have blue pills. It’s not like the matrix, the green pills are continuous values and the blue pills are discrete. I can sort from. I can bring in a label and then do a copy drag onto the end of our bars here.

And I’m also going to remove the axis so that we’re no longer seeing that . And that is a simple bar chart. So Andy creating a bar chart in Power BI

[00:13:33] Andy: I’m going to choose a slightly different way to create the bar chart. We’re going to begin with a column, so I’ll choose a subcategory and I will choose the quantity and just move that up here. And what Power BI will do is choose what it thinks is the most appropriate visual type. And you can see it’s grieving by the subcategory and it’s choosing to sum the quantity.

So what I want to do is change the visual type cause I wanted a bar chart. So I’ll change it. And then I’ll apply to some of the formatting, which Steve did as well. So we’ll go to the formatting tab. And the first thing I’ll do is turn my x-axis off. And then what I’ll do is apply some data labels. So to do that, I’ll look for my data labels and alternate.

And the other things Dave did was to sort it. I don’t need to do that because by chance has chosen the same or the correct sort order to begin with. But if I did need to, I could do that there.

Tableau VS Power BI on: Line Chart

[00:14:27] Steve: Cool. Thank you very much for that, Andy. So the last chart we wanted to show you is a simple time series on a line chart. I am going to bring in my measure and I’m going to bring in my date field onto my column. And in this case, I’m going to drill down from year two. I’m not interested in the quarter granularity.

So I just dragged it off until the red cross appear. So I can remove that. And instead of actually having those discrete months by year, I’m going to just take my year pill and pop it onto the color marks card so that we can differentiate now between the years. But we’re looking at January through to December.

I don’t like that color palette so I can change the color palette. I’m going to double-click into the color pallet here. And assign that instead. So we have 20, 21 and dark 2018 in light click on apply, and there, we can see our colored line chart over to you Andy.

[00:15:40] Andy: So to add in the final, one of our three charts, I’m going to click on a line chart and then I’ll choose to display that the date and the. The date comes in as a hierarchy. So I don’t want the quarter and I don’t want the day. And I also don’t want a year because that’s going to go on a slicer. Okay. But that, and then on the right hand side, I will add in something called a slicer, as I said, which is a way of filtering data.

I’ll choose to display on that my year and to make this look the same as they didn’t Tableau, I’ll make it into a list. So that’s my. What I don’t have on my line chart is the year as a stacked line chart. So what I’m going to do is add that in. So I’ll take the year and then drag it down into the legend.

And I may need to scroll down a bit so you can actually see that. So my face isn’t actually obscuring that. So now I’ve got the situation I can click on any of these years and it will filter the data, not just in this line chart, but also in all the other visuals on the same page to show the data for the year of.

And one thing I can’t do easily is format the slicer to make each of these, the right color for the line, the appropriate line, because the slice on the line chart are actually two separate visuals. In this case, there are other ways around that. One of which is to apply something called a theme, but I can’t actually quite reproduce the same things that of.

Tableau VS Power BI on: Dashboard Interactivity

[00:17:03] Steve: Brilliant. One thing that you would have noticed is that in Power BI you’re automatically building things directly in the dashboard itself with Tableau. We do it the other way around. We’ve built up our worksheets and now we want to combine them into a dashboard. So hover over here and you can see we’ve got new dashboard.

I’m going to click on here. It’s a slightly different screen. We can see our three worksheets over here with our items in. I can drag them into. Dashboard.

And then the build my dashboard up by dragging my items in. I’ve got a container over here, which has automatically been added with my legends in it. I’m actually not that interested in those. So I’m going to click that container. I remove them from the dashboard. I’m also going to select my bar chart and squeeze it until we get to the whole truth in the entire view, we can, if we wanted to turn off the titles from the charts that I’ve actually named these, and the last thing we want to do is add. Interactivity. So I might want to select an item on my Treemap, for example, that filters the rest of my dashboard. If I click on the worksheet, I can see that there’s a user’s filter option here.

So I can click on my Treemap and let’s click on the bar chart as well. And now if I click on France, we’re filtering both the bar chart and the line chart down to that level of detail. I can do a multi-select by dragging those items to do that filtering. So Andy, how do you add the interactivity on your dashboard?

[00:18:41] Andy: So as Steve said, I’ve already got the visuals on the same page. So I already in effect have a dashboard, but I do need to get Visual interactions working perfectly. So for the moment, if I click on France, for example, you could see the one it’s filtering the line chart. What is doing with this bar chart is just highlighting the areas corresponding to France.

And that may be what I wanted, but isn’t on this. What I’m going to do is go into something called editing my visual interactions. And what I’m going to do is say that when I click on a country in any other Visual, then I want to filter this data. So let’s try that one again. Now, if I de-select France.

So when I click on a country, now it will filter both the line chart and the bar chart. And that’s pretty much the end of my Power BI. What I’ll now do is hand over to Steve to wrap up. I hope you’ve enjoyed the video and the comparison. And please do let us know if you have any other ideas for things you’d like us to cover.

[00:19:42] Steve: That was a high-level comparison of how Tableau and Power BI do the same things. We are going to dive into some more detailed comparisons in future editions, and we’d love to hear from you if you’ve got some suggestions as to what you’d like us to show you. I hope you enjoyed this video and I’m looking forward to seeing you in the next one…