The Basics of Big Data in Excel: PowerQuery and PowerPivot | Ruben Wollerich | Skillshare

The Basics of Big Data in Excel: PowerQuery and PowerPivot

Ruben Wollerich, Data is the key to the top!

The Basics of Big Data in Excel: PowerQuery and PowerPivot

Ruben Wollerich, Data is the key to the top!

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
8 Lessons (39m)
    • 1. Introduction

      1:58
    • 2. Class Outline & Project Description

      3:59
    • 3. Introduction to Power Query

      5:37
    • 4. Power Query: Sourcing

      4:41
    • 5. Power Query: Transforming Data

      6:40
    • 6. Power Pivot: Making Pivot Tables

      9:18
    • 7. Power Pivot: DAX

      6:29
    • 8. Outro

      0:40
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

43

Students

--

Projects

About This Class

PowerQuery and PowerPivot are, for many people, unknown and hidden functions in Excel. Demystifying these features is going to give you endless possibilities if you work with big loads of data. Therefore, this class is not specific for certain fields or positions within a company, but rather for people using large datasets.

The class is useful because it will save you an enormous amount of loading time, formulas crunching, and importing/exporting files. This class will help you automate importing, cleaning, and processing data.

For this class we recommend Excel 2016. The two features discussed: PowerPivot and PowerQuery can be downloaded separately for people using older Excel (2010 and 2013):

Meet Your Teacher

Teacher Profile Image

Ruben Wollerich

Data is the key to the top!

Teacher

I gained my experience teaching and working with Excel as a freelancer for medium and large-sized organizations such as ING, NN Investment Partners and Pfizer. 

Besides, I have a master's degree in Digital Business & Innovation, where the majority was about big data, analytics and business intelligence. 

During my experience, I learned what Excel features have been most valuable for people working in larger organizations. Besides, adopting the right way of working with Excel, unleashes it's full potential and helps you gain insights you wouldn't expected to have gained through Excel. 

In my classes I walk you through some of Excel's most amazing features, but also some very basic tips and tricks that help you become ... See full profile

Class Ratings

Expectations Met?
  • Exceeded!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Your creative journey starts here.

  • Unlimited access to every class
  • Supportive online creative community
  • Learn offline with Skillshare’s app

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.

Transcripts

1. Introduction: Hi, my name is Ruben. Out of this class we're going to look into the features PowerPivot and Power Query in Excel. Powerpivot in Power Query or two features that are great to use to work more fast, more efficient, and easier with big, large datasets. I've given my experience with those features working for different large organizations like ING investment management, slideshow, Pfizer, and more. Whether you're a beginner or an intermediate in Excel. As long as you've not really worked with those features before, this class is great for you. Usually, those big, large datasets are used within large corporates. And if you work for a large corporate and you struggle with data getting slow, or it data getting lost messy. And you just have too much data to handle and to analyze. And this class is going to be great for you. So in this class we're going to go a little bit more ahead in, into Excel Ben formulas. However, it's still not going to be rocket science. We're going to cover the basic fundamentals of using those two functions. And therefore we're going to have for both functions, so both PowerPivot and Power Query, we're going to have an introduction video explaining at what they do. We're going to explain the advantages of using them, disadvantages. And the subsequent videos are going to look into how to use them. We're going to show some examples. I will also have a project assigned to this class, or you can practice yourself using this feature if you're ready to save loads of time and frustration in Excel. And this class is for you. And then I hope I'll see you in my next video where we're going to go over the outline of this class and cover what executive we're going to discuss in this class and what the project is going to be. I hope I see you in the next video. 2. Class Outline & Project Description: Hello and welcome to the class overview and the project description over class, the basics of big data in Excel. And we're going to use Power Query and Power Pivot. So the outline of this class looks as follows. We'll start off with the introduction and the video in which we are right now, the class outline and the project. From there on, we're going to dive into two features, the Power Query and Power Pivot feature in Excel. We'll start off by introducing Power Query. So I'm gonna explain to you why you might want to use it and also why it's so great. And the videos coming after that is going to show you how to use it. So how to source data with Power Query and how to do data transformations to prepare your data before you start calculating it. Then we're going to look into PowerPivot, which uses DAX docs, which is a method of creating formulas within the Power Pivots. We're going to create tables from our power queries. All this together, our methods to use to work with a big pile of data. After that, we're going to look into the review. So we are going to look into how you did the project. Thinks she should have paid attention to when you were doing a project. In the last video is the altro will be signing off the class. So the class project is basically to create your own Power Pivot table. You're going to have the freedom of creating your own report. However, we are not. I am going to require you to include a few insights. And we shall also might require you to apply DAX docs. First of all, tried to add the profit margin per product. And second of all, tried to add the total sales per country, give us insights into that. And the third I wanted to see back into the project is discounts per segment given in percentage. It might sound a little bit vague what I'm talking about right now, but let me show you data sheet we will be working from. And then those three points should make sense and you should be able to with what we're going to learn in this class to apply yourself. So these are two sheets, she'd want to sheet 2 and 1, we have data about the products being sold. So in which segment, which country, what kind of project, products, et cetera. And then the other sheet, we're going to have more financial data. All of our data is going to be connected with a ID, a unique identifier, which is going to help us matching the rows in the sheet with the other sheet. But it's something we're going to look into one, we're gonna make our Power Query. So from this data, we're going to have to make a report using Power Query. And that's going to be our projects along the classes tried to build on your own project. So in the project review, you can see what the things where you should have paid attention to. And if you came close into the right direction of using Power Query and Power Pivot. All right, So that was it for this video. That was our class outline and project description. I hope you're as excited as I am to start diving into this subject as its subject that's not too well-known within Excel. And especially if you keep on struggling with largest, large piles of data. You're going to love this class and you're going to love the features that'll be demystifying for you. So I hope I see you in the next video where we are going to talk about what Power Query is going to introduce ourselves to the subject. So I hope I see you in the next video. 3. Introduction to Power Query: Welcome to the Introduction to Power Query. In this video, I'm going to walk you through what Power Query exact to use. And I will also slightly prefer PowerPivot is basically the knowledge that we need to have in order for us to dive in and therefore this class. So why Power Query? Power Query is a very easy tool that lets you skip a lot of steps that you usually have to do when you work in Excel. So for example, when you want to combine files and Excel or you want to do an analysis, some things that you often have to do is clean up your data to transform your data. And you have to copy paste it from maybe different sources. Use unique identifiers to combine different sheets, putting a lot of formulas. And eventually you'll end up with 80% of your time preparing data and 20 percent of your time actually just analyzing data. Power Query is made to more easily source data and then also transformed into more structured data that is more easily, easy to analyze. So as you can see, there is a variety of sources that we can use. Various Azure, various an SQL Server, salesforce as use VI documents and even sources from the web. So Excel has 1,048,576 rows. That's the limit. You cannot go beyond that. Using Power Query, however, MIG makes it possible to go beyond that because with Power Query you don't really have to see the rows here, not even having it like an Excel spreadsheet. Now what it does, it's sources, the data, and it aggregates to data into tables or charts before even putting it in a huge table with more than a million rows. So therefore, power gray is usually a more efficient, but also a lot faster and easier than an Excel spreadsheet is. So again, why Power Query law should see below, you can make the different connections between your data sources to eventually plot data into charts, tables, etc. You can also create calculated fields. And this makes things so much easier and so much easier to have an overview off. Imagine having all those data sources that you see right in front of you. Having to combine them into one table is going to take you a lot of time, a lot of formulas, a lot of formatting and Power Query is made for the people. They use a lot of data and make their processes faster and easier. So with Power Query, as I said, you can transform data. So there's a couple of steps that you can do and you can automate after you've done it once for transforming your data. And this is removing columns, filtering the data, grouping of data, pivoting or pivoting the data. Splitting strings into substrings, extracting keywords from the strings. Bending rules from another table, and joining two dimension tables. There is a lot more you can do with Power Query. However, it's not just this list, but these are just a couple of examples of frustrating little tasks that you have to do. If you don't have Parker in Excel creating an extra column, adding those formulas to remove this little appendix in your text that shouldn't be there. Or filtering out the wrongly formatted codes or having to transform dates. It can be very, very dreadful to do those processes. And Power Query is made to make this easy. And you can automate those processes for the next file that you're going to import. So as I said, a couple of features, it's outro transforming data, combining sources, consolidating those into datasets. And power Query can be used in Excel for Windows 2010, 2013, and 2016. If you have Excel 2010 or 2013, you have to download an add-in and you can find this on the Microsoft page. It's a free add-in to be able to use this. From 2016 onwards. It's a built-in functionality in Excel. Me myself, I'm using a Mac for this class. I actually have to use a Windows because on Mac, unfortunately, this functionality doesn't exist. And that's it. This is our introduction into Power Query. So I hope you now understand how big of problems Power Query can solve if you work with a large sets, what such a Theta. And therefore, I hope I'll see you in the next class in which we will dive into sourcing data into Power Query. So I hope I see you in the next video. 4. Power Query: Sourcing: So this is the first video where we are actually going to get our hands dirty and start working in Excel. So in the previous video, we have seen it. What Power Query you can actually do for us. Why do we want to use it? In this video, I'm gonna show you how it works. In this example that we have, which are cells that sales data of 20172018. I'm gonna keep it simple. And again, this is a class for beginners on Power Query people that are not familiar with it yet. And therefore, we're going to keep it simple and not all too complex. And I just want to show you some of the key features it has to organize our data when we are working with large datasets or when we have a lot of different sources. So Power Query basically brings our data sources together and helps us transform the data so we can read it altogether. So our screen right now we see two Excel windows. The left one is still stayed at 2017 and a red one is 2018. Something that is important to make sure before we start is make sure you name your tables and give both tables the same name. So we want to put here sales data, and this table is done 17. We also want to put sales data. Now with that in mind, we are going to start our process and the first thing we wanna do is safe both files and close them. And here we have a blank Excel sheet. Here we go to New Query. We click on firm file from folder. Note that you can also use the other sources. So from workbook, from CSV, but even from databases like SQL Servers, microsoft x's, there is a way of connecting Azure and also you can use online sources. So from the web, for example. This is a great way for continuous updates of your data. This is a great way and these online tools or B Servers connecting those richer Power Query makes it even more powerful. And obviously, if you have different sources, it can get even more tricky to transform your data properly. And therefore, Power Query is such a great tool. So we click in our case on from folder. And right here we have a folder that contains the two sales data files we were just looking at. And if you click on open, you see the two files. So seals 2017 and 2018, we click on combined and transfer data. We also can transform data or load, just load data with. For now we want to end combine and transform the data. And we want to select the sales data table. Now if we hit Okay, you will see that we will get both of our sources in one table. And right now you see the Power Query Editor. And if we scroll down in the first column, which is the source name, we should see 2070, but we should also see 2018. And there we go. So here we have the data of 2018 and now Power Query has basically put together both files match the column names. And now we have one big table instead of two different ones from which we can start the transformation process. So that means that in the next video we're going to look at how to transform this data before we're actually going to use it and analyze it. And we can save this process of transformation to use it later on for other sales data sheets as well. So for example, if next year we're going to add those data in 201819. You'll want to combine those data sheets again. We want to remember what were the steps again for transforming, and that's where we're going to look at them the next video. So I hope I see you there. 5. Power Query: Transforming Data: All right, so in this video, we are going to talk about transforming beta, and this is one of the key features that Power Query offers. So what we're going to look at in this video is how we can transform them, both data sources into a better formatted sheet. And also how this process affects our future and how we can automate this. So right now we will start off by building are transformation steps. And we'll just go buy it one by one by the columns of what we should change or what should we, what we should do. So first of all, we can remove source name. This is not relevant for data sheet. And the first next one we see is product. And strangely enough, for some reason, we not only see to predict which is America, but we also see the product ID. And we want to split. This would look a lot nicer. So how are we gonna do this is we want to select the column and we want to click on Split by column, by the limiter. And then if we click on Custom right here, we can put in space because our delimiter is not only the dashboards, space, dash space. And now if we click on Okay, we see that we now have products ID and the product name. And that's also change the name of the column. So accordingly, I'll put this one, it's going to be product name. Looking at the rest, nothing weird seems to be happening. Everything seems to be properly put in and the columns. So the only thing that you want to change now is to formatting. So let's select everything that should be a number, which is product ID, units sold. Price is currency. Sales prices also occur in C, So we're going to skip those. Among number is a number, the ideas and number. The year is a number. And is it? So all these, we are going to give the number format type, which is a whole number. Next up is putting everything that is currency related into a polar formatting. This means that whenever we are going to put and load this data source into Excel, and every time we get a new row and automatically becomes a dollar. In this case, or whatever formatting rule we give it to the column. So we're going to start off with manufacturing price, which is currency. I will just start selecting all of them. And we do the same thing. So right mouse click, we go to change type, and here we click on currency. Then we have debt which seems to be already changed into a date. Yes, it is. And that's it for the forming. It is also possible to add another column. So I've been calculated field. But for this class we won't be doing that. We will do this with the Power Query. So now on the right step we have all our transformation steps and we start off by sourcing it all the way to changing the column types. As you can see, we didn't do too many highly, highly exciting stuff and a lot of magic with our numbers. But at least what should be clear from this video is that you can set by following the steps that we need to do to transform our data properly. And we can create this process, we can create this steps, which will also be the steps for any new row of the data at it. So when you're done, you can go up here and you can click on Close, close and load data. And as you can see, we right now have our data loaded into this sheet and the data is transformed. So as you can see, we have heard the idea and product name separated. And now if we go to our 2018 sheet, which is this one, and this is one over two sources. And we add another row. So let's make this test tests or we can more easily identify it. And we don't put in a product ID. I made a mistake here. I should taste test here. We don't put in print ID and the product name separately, but we're going to do it like it was done initially. So putting, merging them together. And the rest we're going to copy and paste as well. We should be seeing that this is being split. And if that is true, then our transformation is working. It's just a little test to make sure that things work and to show you what the dynamic is between those data sources and Power Query. Now I save the file. And now if we go to our query file and we refresh, you see that test test is being added. We have two different columns, ID and name, and that it's also appropriately split. And this tells us that that part of our transformation process is working. And this leaves you at a glance what Power Query actually does and why it's so functional. So that was it for this video. In the next video we are going to start looking into Power Query, which is a little bit more from an analytical perspective. And we're going to use an analytical approach into processing data, into tables and charts, which is a great tool if you wanna go start of affording with the data that we have. I hope this was useful video for you and I hope I see you in the next one. 6. Power Pivot: Making Pivot Tables: So with our data transformation steps set, which we can see over here, we're going to dive into using Power Query. So we can close this query. Let's just quickly refresh our data. And now we want to add Power Query and we're going to start running tables from different data sources. So before we start, what I have been in the meantime, added some more information that we have. So as we know, every row right here is about ASU. But it doesn't contain this anything about the shipping info because this comes from a separate table. And then we have another table that tells us which carriers for different type of privatization shipping. So let's say in this, in our example case, if the shipping is high, then the H is going to deliver our package. If it's medium, it's x fed, and if it's low, it's Postal. So what we wanna do now is without having to use VLookups, SUMIFS, and other type of formula. We want to aggregate this data and to get out, put it into insightful powered, have pivot charts and pivot tables. So before we start, we'll have to add Power Query. And in order to add Power Query, we want to go to File, click on Options, Add-ins. And then we want to select Add-ins. Click Go. And now you can select here Microsoft PowerPivot for Excel. And now if we click Okay, we can see right here is added Power Pivot. Now if you click on PowerPivot, we don't have that many options to choose from. Basically what we wanna do. We wanna create a data model. And in this data model we're going to put the carriers, the shipping info and the seals data. We're going to put the relationships and the logic behind it. And from there on, we can start creating pivot tables. So our first step is adding our main sheet. So let's call these our main data source, our main sheet. And we want to edit to the data models. And what that does is opening up Power Query for us. And now if we click up here on Diagram View, and this little section represents our source data tab. And before I continue adding the rest, let's make sure that we give names which I forgot to do. Let's make we give Midlands, make sure to give names to the tables. And we can do this by clicking in the table. Click on Design up here. I made this given here and name, which is going to be sales. Then shipping info is the same thing. Well, it's logistics and carriers. I already added it as well. You click on a table, you go to design. And you see we've already added carriers. So with that done, we're going to also do the same thing with those tables. We go to the Power Pivot and also add it to the data model. And as you can see, it's now been added to our overview. And we're going to do the same thing for carriers added to the data model. So what's good to know is it a dataflow only works one way. So we've, we want to create pivot tables. We should think about what is the core data, the source data that we want to use, and our other tables we can use to either connect, make connections between our initial core data and the edit tables like carriers. Sounds little fake. It will become more clear when we dive into pivot tables. So for now what we wanna do is we want to set the relationships between them. We want to teach Excel what is the logic behind those different tables? And how can we connect them? So we can create more insights altogether. First of all, we have ID. An ID is our sales seal ID of the sale. And matching those two means that we can then see that, for example, within the lead times, how many sales has to be generated per lead time or per priority. We cannot do that if we don't have like this matching identifier. So we click on ID and then we start dragging to our Seattle one. And note that the direction is important to direction. If your core data should always be the one that's being pointed at. And now to connect logistics to carrier, we want to connect priority to prevail. And there we go. We have now set our relationships of our different tables. And if you feel like organizing, you can drag them along as well. Right now, like in the previous video, we've kept it very simple. We don't make this a very complicated process, but obviously the most value you get out of tools like PowerPivot. But also Power Query is when it gets complicated because those specific distance, specifically this tool is going to help you to keep the overview of your data relationships instead of having to create endless field lookups and other formulas to match your data together. And when you're working with different sources and maybe not just cheats, but also other sources where you can add here under external Beta. And this makes sure that things, yeah, did you keep having an overview? So with that being said, we now want to go up here to create our first pivot table and show you how it looks like. So we go the pivot table, we go to a new worksheet and we want to use carriers as the first perspective of our first pivot table. And because of the directions that we have been given in the mapping, we cannot say, for example, which country has reached carrier. And this is because of the hierarchy that we gave in the relationships that we built in-between the different tables. So we want to use, preferably use all of the sales data under values. And we want to use carriers or logistics in columns or rows. So let's see how many units have been sold per carrier. Which also an indirectly means that the amount of units that each carrier has driven around, so unit sold is going to go to values. Carrier is going to become the rows. And right here we have from different datasheets combined a pivot table that shows us how many units of each carrier has shipped. Let's give this quickly a nice-looking, a nice-looking formatting. So it becomes a little bit more clear. So this is, again, in hindsight, how it, how it looks like to use Power Pivot. And this is incredibly simple example. But like I said, whenever it starts getting more complicated and you start having more data sources and bigger files, then this is going to save you an enormous amount of time. If we go back to our Pivot Power Pivot manager, then we can also not only created tables, but we can also create a pivot chart. So we can use the existing worksheet and we're going to use d three phrase. And now we can use, we can create the same thing as we did for our table, but then in the chart. And it basically works the same way. And which is units sold. And now you can see that for each of the carriers, how many units they have shipped. And again, what's so special about this is that the, the carriers were not even directly linked with the units sold, but we needed another file in-between. And PowerPivot has enabled us to get these insights. So that was it for this class. It, in the next video we will be diving into backs and VAX is to format the formula language used for Power Pivot. We're going to go over the basics of it, how it works and how you can apply it. And that would then also be the last lesson in the last video that we will be doing covering the contents of Excel. And so I hope I see you in the next video. 7. Power Pivot: DAX: All right, Welcome to the last video of this class where we will be working with in Excel. So in this video we will be going over the basics that real, real basics of the function in Excel ended thanks language is informing on language used for Power Query. So as you've seen in the last video, we've been able to combine different sheets and different tables. In this class. We're going to elaborate on that. We're going to elaborate on what we had already built using formulas and attacks language. So we are going to open up our Power Pivot manager and we want to get out of the diagram view and go to the data view. And what we will do, we will go to the Open it up properly. We will go to the sales, the core sales data. And what we're gonna do for this one is we're going to add profit because as you can see, we have the cost of goods sold. We have this sills. We even have gross Zeus, but we now want to add also what is the profit? And there's two ways mainly to do this. And I'm going to show you the least straightforward way, which might be a very cool way and a quick way to do this, which is by adding a formula. And down here in our little embedded worksheet. And red here we can basically add new fields. So we're going to call this field profit with double-dot, and then we start our formula. So just for your reference, I'm working up here for the cell. So profit is going to be the name of our fields. And with double dot v, we actually are starting the formula. And now we started off by doing this summer and we're just going to add all of the sales, which is right here. And we're going to do it minus all of our costs for cost of goods sold. And now if we hit Enter, it starts calculating. And you will see our total profit. So not just the average profit or whatever is to total profit from all our data in this sheet. And we just gonna give you real quick a little bit of formatting, which in this case is the dollar. And right now we have a new field, so you would say, okay, Reuben, so now we know what the total profit is, but what can we do with this? So if we go to our Excel, and now we go to the PivotTable that we had created in last class, which is based on our Power Pivot Beta. And we click on opening a Skillshare class five theta. We scroll all the way down. We see that this profit field has been added. And now here's where it gets interesting. So instead of just showing the sum of units sold, we're also going to show the profit that we have made using different carriers. So now if we add the perfect two values, you see the total profit that had been made. And what's more interesting is that this profit is not all the same. So it's actually despite that we just have calculated the total amount of profit, it's still, an Excel still manages to calculate backwards and figuring out how many of that total profit can be attributed to DH set X fit and postal individually. And we can continue doing this for different, for different totals and for different data that we want to get out. So for now, I'm going to show you the other way of doing this. And this is going to be the profit margin that we will be calculating. So right here we want to add a column. And in this column we are going to calculate profit margin. And the profit margin is going to be the total profit divided by the total sales. And before we start, we should first calculate the profit again, which we will be doing by opening up the formula. Sales. You can also just write down sales. We don't have to put the entire name of the worksheet. So we click, we slept here, sales minus cost of goods sold. We just want to close this into doing parentheses because we first one minus this deduction to the heparin and Ashton has done has been done. We want to divide it by total sales again. And this should give us our profit margin. And it's immediately a calculated for the entire column. And for this one we want to use percentage. And there we go. Now we have an extra field, are calculated fields. We can give this one a name as well. And these are two ways to quickly create data using the x. Now if we go back to our file and we want to add here profit margin as well. We open up and we go down here to profit margin and we add it to the values. And we now have our profit margin. And profit margin. Make sure that we don't have the totals of our profit margin. Will want to have the average. And it gives us again individually for each carrier what the profit margin is for using them. All right. This is quickly how we go from two rod sheets all the way to get our invader from different angles, from different tables. So I hope I see you in the next video where we will be signing off the glass. 8. Outro: Great. You've made it to the last video already of this class. So, so far we've looked into how you can source data, prepared data, and manipulate data using Power Pivot and Power Query. And now you should be able to work faster using large data sets. I've also made videos about forecasting and business analytics. So go into my profile in Skillshare and see if you can find a class that might enrich your knowledge and use this about the Excel. If you liked this class, please give me your review below would be very grateful and I hope I see you and one of my other classes. Thank you.