Use Excel Like a PRO | Learn Power Query, Power Pivot & DAX in 15 MINUTES (project files included!)

if you're a data professional who works with Excel let's be honest that's all of us the tools I'm about to show you will completely revolutionize the way that you work and the crazy thing is that 99% of excel users even those self-proclaimed experts have no idea that they even exist we're talking about excel's insanely powerful business intelligence tools power query power pivot data model and Dax I'm about to show you exactly how you can use these tools to work smarter and faster than you've ever worked before and the best part is that these are free native Excel tools that anyone can learn so close your eyes and imagine you just got hired as an analyst for Maven Electronics a global retailer that sells TVs computers appliances things like that now it's 400 p.m. on a Friday you're about to unplug for the weekend when the VP of sales sends you the dreaded urgent email the quarterly sales call is first thing Monday morning and she needs you to gather some data and build a brand new report for regional sales managers they'll need to see mon Revenue trending sales by product category and breakdowns for each individual country and to make things even tougher it turns out that the data is all over the place transactional records live in a SQL database product details are in a CSV file and for some crazy reason George from it exported the list of store locations as a static PDF now this is the moment where the average Excel user would either curl up into the fetal position and cry or hunker down for hours of mind-numbing tedious work work but we are not the average user we are power users and we're going to use the tools at our disposal to knock out this report earn some serious cred with our VP and still have enough time to clock out by five so let's fire up Excel and get this thing done all right so here we are in a brand new blank Excel workbook and the first thing we're going to do is use power query to extract transform and load data from all of our different sources now here's the key instead of manually copying and pasting data into worksheets power query is going to allow us to create data connections and build automated workflows that we can apply with the click of a button so let me give you a rundown of how this works we're going to head to the data Tab and here in the get data menu this is where you'll find all of those power query connections let's start by grabbing our transactional records our sales data which live in a SQL Server database all I need to do is drop in my database details press okay that's going to pull up a preview window where I can see all the data stored in that table or that database schema so now I'm going to press transform data that's going to take me into the query editor and this is where I can explore the data see exactly what I'm working with I can apply filters sorting logic add columns transform fields and load this data into Excel for further analysis so you can see we've got order numbers got line items for different products sold order dates delivery dates different key columns for customers stores or products that will allow us to relate this table to different lookup or Dimension tables we've got a quantity field and currency code as well now one thing I love to do is in the view menu I like to activate the column quality and distribution fields that gives you a quick glance at the frequency distribution as well as the percent of valid records or empty or error values as well so one thing that jumps out we see a lot of empty values for delivery date and as we scroll through this actually makes sense so the company sells some products in Brick mortar stores where there is no delivery and they also run an online store which is store Key number zero and in those cases we do see a delivery date so nothing concerning there we can also view some additional details and summary stats by activating the column profile pane so here for order number you can see a quick glance at some of those key statistics by default these are based on the first thousand rows but I can click to change this to base on the entire data set and and this gives me some really helpful information at a glance so for instance I can see that we're dealing with 26326 distinct order numbers here so obviously a lot deeper that we could go here but that looks good for now we're going to name this table sales head to home close and load to and this is the key instead of dumping this data into an Excel worksheet we're just going to create a connection to that data source and we're going to add it into excel's data model so let's press okay that's going to fire up the queries and connections Pane and you can see we've got 62,64 rows or records loaded next up let's grab our product data remember that came from a CSV file so let's go ahead to from file text CSV here's our product data right here let's click import again we get this preview pane that gives us a quick glance at the first 200 rows of data and again we're going to click transform to open up the query editor so this table contains all of the information that we need to know about products we've got a unique product key this is a primary key column got product names Brands colors the cost and price in USD and also information about subcategories and categories as well so again like to do a little bit of column profiling here we can see basically the distribution of products by category so this company sells computers TV and video equipment audio gear cameras and camcorders and same St for subcategories as well great way to just get a quick understanding or profile of the data that we're working with so this is looking good data types and headers look good table name is products which looks good it's head to home close and load to same story just a connection add to data model press okay and there we go we've got 2500 product rows loaded next we need our store level detail which remember George from it saved as a static PDF file for some reason but that's no problem for power query we're going to grab some data from PDF point to that store PDF file and import and here in the preview pane you can see that Excel is identifying tables of data contained within that PDF and it's also kind of taking snapshots of each page so we've got a two-page PDF captured here and what I'm going to do is select the entire folder and click transform to pull all of this data into that query editor now just a couple steps I need to do to get this into the format that I need for one we don't need both the tables and the snapshots we really just need the two tables so we can filter those pages out and this button is going to expand this to show us all of the data contained in those tables so you can see from the preview we've got our store Key country and state let's rightclick remove the other columns and the next thing I'm going to do is promote this first row because that contains our Header information so from the home men menu I can click use first row as headers and boom we've got store Key country and state this contains all of our different stores and where they're located including our online shop can name this table stores and you know the drill close and load to just a connection add to data model press okay and just like that we've turned our static PDF into a 67 row table right here inside of our Excel data model now last but not least remember I want to be able to filter and segment our sales data by different date parts right I want to look at Revenue trending by month maybe I want quarter or year and the proper way to do that is to create a dedicated calendar table containing the same date range as our sales table so a little shortcut to do that I can rightclick and actually duplicate that sales query that's going to open up a duplicate copy right here in the query editor and check this out all I'm going to do is keep the order date from this column remove everything else remove all of the duplicates so that I have a unique list of sequential dates in this table let's name this column date we'll name the table calendar and now I can enhance this table and add columns with any additional date field that I'd like to use for filtering so to do that I can add to add column power query has all these amazing out ofthe boox date tools so for instance I could pull in the name of the day I could pull in the week starting date I could pull in the start of the month I could pull in quarters I could pull in the year you get the idea so that's looking pretty good I've got my month field for my Revenue trending chart so let's go back to home close and load get this one added as well boom now we've got four tables and our work with power query is complete now our next stop is excel's data model so let's head to the power pivot Tab and we're going to click manage under data model this is going to open up a dedicated data model window this is where we can store and compress huge amounts of data we don't have that row limitation that we get in worksheets and more importantly this is where we can create table relationships and models to blend the data together without having to use complex worksheet formulas like X lookups or index match so we can see our data here we can get a quick preview in tabs but the real magic happens in the diagram View and check this out we've got our sales data which is our fact table or data table and we have those three dimension tables that we loaded up with power query products stores and calendar and blending or merging this data together is as simple as selecting the primary keys from our Dimension tables like product key and connecting them to the foreign keys in our sales table so product key connects to product key store Key connects to store Key and date connects to order date this creates one to many relationships between the four tables in our relational model and allows us to access data from all of these different tables in one place without writing a single formula so that's the data model go ahead and close that window to get back into our worksheet let's close the queries and connections pane here and our next stop is power pivot so what I'm going to do here is insert a new pivot table from our data model going to drop it right here into cell A1 and look at this we've got that familiar intuitive drag and drop pivot table interface except now it's sitting on top of our entire multi-table data model not just a single table or cell range and because we've created those table relationships in our model it means that we can access fields from any of these tables for instance we can look at the sum of quantity from our sales table and break that down in different ways like product category like so or subcategory as well this works exactly like a traditional pivot table right maybe we want to break this down by different countries right we can pull country from our store table as filters and take a look at how these metrics compare for different territories we can add slicers and pivot charts as well just like we normally would which we'll take a look at in just a minute now we're on to our final step here our final power tool which is using Dax or data analysis Expressions that's the language that we can use to define new calculated columns and measures to enhance our data model instead of the traditional cell formulas that we'd typically use if our data was stored in worksheets so right now all we have in our sales data is quantity that's really the only quantitative field that we're able to analyze right now but remember that the VP wants us to track revenue and Order quantity as well so that means we need to Define some new calculations some calculated fields or measures to produce the values that we need and this is where Dax comes into play so I'm going to head to power pivot you can see this measures option here I'm going to create a new Dax measure it's going to live in my sales table and the first one I want to create is a measure called total orders and this is where I can write that Dax code to define the measure and if I want to calculate total orders what I need is the distinct count of the order number from our sales table so I can tab that in close the parth is check the formula looks good and we can format that let's say as a whole number with a separator press okay that's going to drop in total orders right here into our pivot table so let's go ahead and pull some of quantity out and we're going to add one more measure here this one's a little bit more complicated but we need to calculate Revenue somehow so let's call this one total revenue and the challenge here is that we have order quantities in our sales table but the product price which is required to calculate Revenue lives in our product table but Dax is an incredibly powerful language and we can use an iterator function like sumx to basically say I want to sum records from our sales table and the expression that I want to evaluate on every single row is the quantity times the related unit price from our product table so for each row in our sales table we're multiplying the quantity value by the related unit price for that given product from the product table and then we're adding up the results across all of our records so let's close off two parentheses check that formula looks good let's do currency round it off press okay and there we go now we have the actual total revenue sold broken down by subcategory and category filtered by country and now we have all of the information that we need for this report we've connected to those disparate data sources we've created table relationships to model it all together and merge that information we've used Dax to enhance that model with calculated measures and we're using power pivot to explore that data in different ways now to take things to the Finish Line let's dress this up a little bit let's add some data viz and turn this into an interactive report that the sales team can use for their Monday meeting so I'm going to clear out category and subcategory actually clear country out too and remember we want Revenue by month so let's pull start of month onto rows pull orders out and here we get a revenue Trend by month so let's go ahead and insert a pivot chart here we'll use a line chart and I like to clean things up a little bit get rid of those field buttons get rid of the total Legend add a title here monthly revenue and that's looking pretty good right away I can see some patterns emerging and what we can even do is add another pivot table here insert pivot from data model drop it right here and call D and for this pivot we can actually look at the total orders that's the measure we created broken down by product categories on rows and let's go ahead and sort that descending by order volume and why don't we add a pivot chart can do a simple bar chart here that's going to show order volume at the product category level not worried too much about formatting right now we could certainly polish this up later but same story let's get rid of those field labels get rid of the legends give it a more descriptive title like orders by category and little trick here let's rightclick that axis and reverse the categories so that we've got the highest order volume categories at the top of the chart can actually get rid of that xaxis and we can add some data labels here and now remember we wanted to show the monthly Revenue Trend and the orders by category for each country individually so nice way to do that would be to add a visual slicer here so let's select a cell in our first pivot table go to the field list and let's look for that country field and I'm going to rightclick add it as a slicer just drop it right here for now and what I can do in that slicer menu is connect it to both of our pivot tables here so that it will control both our line chart and our bar chart and I can actually hide the raw data itself to clean things up maybe we get rid of the grid lines as well and now check this out we've got a nice little interactive dashboard that showing the monthly Revenue Trend and the orders by category for each individual country obviously we could do a bit more formatting and polish here but pretty amazing that we're able to go from raw data to interactive report in really just a matter of minutes so there you have it in under 20 minutes we just accomplished what would take most Excel users hours if not days of work plus we built a solution that's fast flexible and scalable this is why these tools are absolute game changers for data professionals and why every Excel user should add them to their toolkit now if you'd like to learn more check out our award-winning self-paced courses and guided projects at Maven analytics and start creating your personalized learning plan for free thank you so much for watching and as always make sure to like And subscribe for more data content just like this I'll see you in the next one [Music]

Channel: Maven Analytics

Views: 58,537

Keywords: excel tutorial, microsoft excel, excel tutorials, power pivot, pivot table, power query, excel tips and tricks, power query tips, ms excel, learn power query, data science, big data, data analytics, learn data, learn, data, analytics, maven, career, analysis, excel, sql, power bi, python, course, Why learn data, what is data, mysql, beginner, visualization, tutorial, education, data science vs business intelligence, business intelligence, remote jobs, data career paths, Data career

Id: GsjCixoyOnE

Length: 17min 28sec (1048 seconds)

Published: Wed May 15 2024