Excel & Power BI Data Analysis Complete Class in One Video - 365 MECS 04

you need to learn all about excel data analysis you landed on the right video video number four in microsoft 365 excel the complete story and this is going to be a crazy video you're not going to believe it all of these tools taught in one video that's right sort filter flash fill power query pivot table charts power pivot and even power bi that means this is one video to get an introduction to every one of these tools now mex video number four is three hours and 43 minutes long that means there's everything in it one stop shopping but guess what below the video you should probably click more and use the time hyperlink table of contents now this is from video number three so this is only an hour and at the bottom there's also this visual time hyperlink table of contents [Music] now of course each video in this class has pdf notes you've got to be kidding me 32 pages if we click on example one we're going to start it off by learning some cool tricks about sort filter and flash fill number four we'll see one of the greatest uses for a pivot table summarizing survey results five we'll see how power query can go from a website that downloads a csv file and we have to do some crazy transformation to get just the file we want and then later we simply click refresh and everything updates example 6 we're going to have 12 different tables but power query will have no problem converting it to a single table and calculating sales for us and we won't load this to the excel worksheet we'll load it directly to a pivot table and make a report and chart example seven will have to look at x lookup function because sometimes worksheet formulas helps you get to your final pivot table and chart in example eight we'll get an introduction to powerpivot and dax formulas because we have a bunch of different tables all working together to create these reports then once we create our data model and powerpivot we'll send it automatically over to power bi desktop to get this amazing interactive dashboard and the data model and power bi desktop will look almost exactly the same as powerpivot then in our final example we'll import seven million rows of data from an sql database and make two dashboard reports and this is our final data model with four different dax measures here's the download link at the highline college website this link is also below this youtube video right click save link as here's the folder on the desktop that we've been using for our other files that zip folder click save there's the folder path right click extract all click extract then on the left i'm going to open it up select it on the left and there's all the files we're going to use in this video yes we have 10 amazing examples and since this is about data analysis we have a lot of files now below the youtube video there's this list of the 10 examples with a time hyperlink table of contents so you can jump to whichever example you want now on data analysis let's just remind ourselves what we learned in video number two data analysis is when we convert raw data into useful information to gain insight and make decisions there's lots of synonyms data analytics analytics business intelligence data science data analysis in every single example we'll start with data data into information to make decisions or data into information to gain insight and don't forget almost every single one of the tools that we use require a proper data set now let's go over to sort filter and flash fill now the amazing thing about sorting and filtering and this is an excel table is these drop downs in this user interface for sorting and for filtering it's the same in all the tools this is an excel table pivot tables power query power pivot and power bi desktop they all use the same conventions as we have over here in the excel worksheet now to sort means to organize records in a table from smallest to largest or a to z or ascending or the other option biggest to smallest z to a or descending you can also sort by fill color and font color now the most important thing about sort is if we decide to sort on this date column all of the records will remain intact that means this field here will be sorted we'll do a to z but the sort in this column determines the sort for all the records so if i come up and say a to z oldest to newest that record remains intact as do all the other records control z now we sorted by one column but you can definitely sort by more than one column let's sort sales largest to smallest every single record is sorted from biggest to smallest but watch what happens when i come over and sort region a to z this is a second sword this is beautiful because this field was already sorted when i sort all the east together they are listed biggest to smallest and when i get to the next category biggest to smallest control z z now if you want to sort a column or a table that's not an excel table up in the data ribbon tab sort and filter group for sorting there's buttons and a dialog box and then some filtering options now sorting without an excel table you can do that you got to have a field name at the top yes i know you can highlight and force sort by highlighting something in general it's always best to have a field name at the top then we can simply click the button and knows what to do also if this is not bold and the field name is text and these are all text watch what happens that field name gets sorted with the records control z that's why you hear me always say in every video i've ever made about tables if you don't have an excel table add some formatting to the field name at the top because excel is programmed to know hey that's a field name now when i do this it works fine control z now most of the time when we're doing data analysis and in particular when we use power query we're going to have a method to ensure that the data type in each column is consistent but if you have mixed data in the worksheet and you try to sort a to z this is the order that the types of data will be sorted in as an example here's a really terrible field or column of data including empty cells now instead of using the buttons for sorting we can also right click there are filter and sorting options now 8 is easy to aim some other options here but what we want is a to z numbers at the top text true falses errors and look at that empty cells always sort to the bottom and they'll sort to the bottom whether you have a to z or z to a control z now let's look at filter to filter means to show only certain records based on one or more conditions for one or more columns and you can use any logical test an and logical test or logical test between and many more and whereas sorting organizes the records but keeps all records showing when you filter it's going to show you only the records that match the criteria and hide the rest so filtering will allow us to set a condition and get just the records that we're interested in but before i show you filtering there's a particularly helpful use for filters and guess what this filter feature will be in every tool now imagine we just got this data set and you're like i don't know how many product ids there are well when you click the filter drop down in every tool it will show you a unique list so instantly i know there are seven ids in this column region there are six even in a column like this with tons of values there is a unique list now in power query for example we'll use filter to get just the records we want before we import but when you're in the excel worksheet one of the best uses for the filter feature is simply to extract the records you're interested in so imagine your boss just texts you and says hey i need the west records you just come up filter uncheck select all click west click ok and there are your records now you can tell that they're hidden because the row header numbers are blue and there's hidden rows and watch what happens when we highlight and copy control c the dancing ants are having a huge party now those dancing ants are only dancing around visible cells those hidden records are not copied now you'd open up a new workbook paste and then email it to your boss for right now we're going to come over to west control v and there we have extracted the records for the region west back over here we can bust up this dancing ant party by clicking the escape key now you can un-filter by clicking the filter clear also up in sort and filter there's a clear clear now the most powerful aspect of filtering is data type specific filtering when we click a drop down on a date it knows it's a date and if we point to date filters you've got to be kidding me there's all sorts of date specific filters escape for text text filters all sorts of important text filters when we get over to power query and we're filtering excel files we might say we only want files that end with dot xlsx and for numbers there's all sorts of number specific filters including greater than or equal to some hurdle or this one top 10 or you can change it want to see the top five sales use this filter now one other important thing about filter is we can use any type of logical test and we have a video a couple videos ahead that talks all about logical test but if i select west from the region column that's filtering on one column as soon as i jump over to a second column and say for example chantel and notice that unique list is based on that filtered column but when i select two conditions on two different columns this is an and logical test that means the record only is included if region equals west and sales rep equals chantel i'm going to clear this now that's two columns two conditions if you instead use one column and i say northwest and in english we say and west but this is not an and logical test this is an or logical test when you're selecting multiple items from one column it's an or logical test when i click ok as we'll learn a few videos ahead what's happening is i'm saying is the region equal to west or is it equal to northwest and we'll use both of those logical tests throughout the rest of the class i'm going to unfilter now on to flash fill now flash fill data ribbon tab data tools there's the flash fill button this is a one time simple data cleaning tool if you have a consistent pattern you can clean the data by typing an example to the right of the column of data then clicking flash fill or control e now let's scroll over and look at an example now we have a column of data here and our task is simply to get the name in the column to the right now before you use flash fill you have to make sure of three things the first is this is a one-time task we're never going to have to repeat this the second thing is we will not get more data later and need to refresh if that's the case you use formulas or power query and the most important thing is you have to recognize a simple pattern and this column of data has an easy simple pattern i want everything after the first space now here's how i do it i usually copy and paste so i don't make a typing error control c i'm going to paste this and click in the cell below and you either click flash fill or control e i can't tell you how often i use this tool but not as part of a an elaborate data analysis project just want to have a one-time task with a simple pattern now this was everything after the first space over here i want everything after the last space i type west enter control e and that is amazing now you have to be careful sometimes flash fill will really mess things up and it's because it cannot see the pattern if i come over here and type zero five slash zero five slash 2016 when i hit enter and control e that is just junk it cannot see the pattern now i have lots of videos at youtube about flash fill and how to do special tricky things to extract a date like this with flash fill but it's just not worth it most of the time if you cannot immediately see a simple pattern in this case i would use power query and get the text between these two delimiters all right so on this sheet we saw sort filter and flash fill now we're going to go over to the sheet pt and look at one of the single greatest examples of why the pivot table tool is so amazing now back in video number two we learned about the basics of pivot tables and our goal here is to simply take this one column of survey data get a summary with count and percentage responses now anytime you have a data analysis project the question is which one of the many awesome tools to use now in this case we know we're going to use the standard pivot table and the reasons are we already have the data in excel so we don't need to import it with power query we don't need to clean it with power query we have a small data set so we don't need to use power pivot or power bi desktop in fact if i control down arrow we only have about 160 records control home and the calculations we need to make are simple all we need to do is count and do a percent of total the standard pivot table calculation abilities are perfect for that now we have a proper data set field name at the top empty cells all the way around so we click in a single cell in the proper data set go up to insert tables group and click the pivot table drop down now we have four options that allow us to go get tables from table or range that's if we have a table in the worksheet from external data source that's if we have a table stored as connection only in power query from data model that's if we have a table or a data model stored in power pivot and power bi that's when we have a table or a data model stored in power bi online we'll get to use a number of these different data sources throughout the class now because we're going to do so many pivot tables in this class we're going to use the alt keyboard alt that brings up n for insert i tap n i see v for pivot table v and we have a table from the worksheet so we hit t there's our pivot table from range dialog box because this is a proper data set it always guesses right we want to on the existing sheet i'm going to put it in d4 click ok now i'm going to grab this pivot table fields task pane and drag it closer and then resize it in the pivot tables fields task pane we always get a complete field list at the top we only have one field so we drag the field down to rows and instantly we get a unique list now because this is a text field when i drag it to values which is where we drag fields when we want to make calculations it will default to count now if you want to change the aggregate calculation you click in the values area right click summarize values by you can choose your different calculations now for us we definitely want count because we need to count how many responses there are now summarize values by is one way to make a calculation the other way is this amazing feature show values as now i'm going to click escape we'll use that show values as in just a second and i want to rename this just count will do and enter now we want to make a second calculation that tells us the percentage responses so we drag survey data down again to the values now we come over and right click and what we want is make sure that summarize values by is set to count because as soon as you switch over to show values as whatever you choose here is based on that aggregate calculation and what we want is to take each individual count and divide by the column total so that's what we're going to use so i'm going to click percent of column total select the top percent count enter that means in less than 30 seconds we can take this survey data and we know what the count and percent responses are now we'll get to learn a lot more about pivot tables in this video and throughout the class all right we want to go over to the sheet pq now we want to talk about power query and guess what power query is the greatest excel tool invented since the pivot table and the reason why is ever since the 90s the pivot table has been an amazing tool to build reports and charts but we've always been missing a tool that can import the data into excel and also fix and clean bad data that's what power query does power query can import data from outside of excel things like text files other excel files databases and lots of other locations it can clean the data like split first and last name into different columns or extract a date from a date time it can transform data like remove unwanted columns which is one of the things we'll do in our next task calculate new columns we'll do that two examples ahead or combine multiple tables into one table we'll do that two examples ahead and then it loads the data either to the excel worksheet directly to the pivot table cache or to the power pivot data model not only that but power query is not just in excel it's also over in power bi desktop so whatever you learn about power query here in excel it works the same over in power bi desktop now in excel in the data ribbon tab get and transform data and queries and connections that's power query there's a bunch of different connectors that connects us to different data sources and then this will open up the queries after we create them just for kicks here's a power bi desktop file and data and queries those are the two groups that make up power query here's all our connection to different data sources this is how we get to queries we've already created now we want to go over to the sheet pq1 info now in this example we're going to have some data at a website we're going to need to download it as a csv file and we'll talk about what that is then import it transform it and load it to the excel worksheet and in the worksheet we'll add formulas and formatting and then when the website updates we'll simply click refresh power query will re-import re-clean and transform and update our table now of course when we're trying to figure out which data analysis tool to do a project like this because we're importing cleaning and transforming of course we use power query now the data we have at this website it's student assignment score data from a particular class now actually i made this website this is a fake website if you click this export csv button it doesn't work and i did this because the website that i usually use to get my grade data i can't show you that live in public but this is exactly what it looks like over in the canvas system now this is gray data but it could easily be a website with sales data for a company or government economic data or data from a 911 call center and at most websites where you get data there will be an export to csv button now for our particular grading data from our canvas website they have a bunch of columns with a bunch of field names that i don't need i want to change them a bunch of columns that i don't need both with data and some extra calculations that i don't need now if this was canvas i just click this button and it would download the csv file and then that's what we import into excel so what i've done is i've created three csv files that will simulate this is the first website this is when it updates later and this is the final update so even though we won't download these from the website the process is still the same when we get a new file we need this new data to update our project now what is dot csv mean it means comma separated values and if we right click open with and i'm going to open with notepad or wordpad or word i'm going to use word and then click ok this is what you see each one of the field names name id quarter is separated with a comma and then all of the records there's a first name comma id comma the quarter comma the name of the class and then a bunch of scores now why in the world would a table of data be stored like this well it goes back to the beginning of database history since there were so many different types of systems we needed one method to get tables of data from one system to another so one of the conventions they agreed on is a delimited table now delimiter means what separates the bits of data for us the delimiter is a comma the other type of common delimiter is a tab we'll see that delimiter in our next example so all a text file either csv comma separated value or a text file which is a tab delimiter all they do is get data from one system to another and when power query sees this it'll interpret it as a table now another important note about dot csv files this computer is programmed to open up dot csv files with excel so if i double click this this looks like excel but it's not that dot csv file extension and the file structure prevents you from doing almost everything in excel so just be aware when you see csv this is not an excel file now before we jump over to excel we're going to do a little file management now here's the folder we downloaded and extracted and you need to make sure that all of these files are closed before we do our file management so for example if you have this open you need to go close it and then come back to windows explorer so we can do our file management now before we use power query to import the csv data into excel i want to look at the folder path we're here in windows explorer and if i click up in the address bar that's the full folder path that power query will memorize when we import this file now this is called an on-premise folder path and the reason is this folder path is specific to the computer i'm working on users m girvin desktop that's going to be unique to my computer you will have a different beginning folder path but the rest of it folder name from video 1 folder name from the files we just downloaded those will be the same now if you look up in the formula bar when we extract it it has a duplicate folder name to make this folder path a little bit shorter and easier to read in power query i want to remove that first duplicate folder path now over on the left this is the folder we created in video number one but one two there's one duplicate folder if i look inside this one well we're looking at this when i look inside this one this is the one that actually has the files so i want to cut this and paste it into this folder right here you can either click and drag to move it or you can use control x to cut select the folder we created in video one and control v and now we have a clean folder path here's everything inside that first folder here's everything inside the folder we downloaded for video number four now if i click up here that'll be the folder path now we'll point power query to this and it will include that file name at the end now let's open up our excel file and start the import so i'll double click we're on the sheet pq1 we go up to the data tab getting transform now we can directly click from text csv but all of these are connectors and if you click the get data drop down from file it has different types of file types like excel csv xml there are databases later we'll connect to an sql database from azure power platforms and many other data sources i'm going to click escape and click from text csv in the import data dialog box we navigate to our folder there's the folder path i select grade 1 dot csv and then click import now it wants to help us properly import this it wants to know what the file origin is that's correct this is the one you always have to check make sure it's the correct delimiter for us it got it right and it usually does data type detection the default is based on 200 rows which usually works you can base it on the entire data set now you almost never want to click load you want to click transform data what transform data does is it opens up the power query editor and gives us a chance to clean transform do whatever we need to do before we import and then load it so let's click transform data and here it is the power query editor this is a new window on top of the excel window and we can already see that power query did its magic we have a preview of the proper data set over here on the left is the default name for the query also on the right this is where we can rename the query and we definitely want to give this query a good name i'm going to highlight this and this is for the summer business 101 scores and enter we renamed the query we can see it listed on the left now right now in the power query editor we only have one query but as we import and transform new data in this excel file we'll see other queries listed here also now one of the most amazing things about power query are the applied steps now there are three automatic steps we can add other steps later by using the user interface and we'll add new steps these applied steps are memorized by power query so later when we refresh all of these steps are rerun to refresh the query now watch what happens when i click on source it gives you a preview of what it did in that step that means we can look at each one of the steps it memorizes what it did that's really different than in the excel worksheet when we do something in the excel worksheet and have a progression as we're building something we can't go backwards like this so in the first step it got the data in records but notice the field names are in the first row and the column headers column one column two are default field names when i select the next step i get a preview sure enough it promoted the headers now the last step is change type and before i give you a preview of that in the name column it says abc that's the icon to say this field has a text data type if we click abc a drop-down appears and these are the different data types that we can use in power query decimal number that's up to 15 digits like in the excel worksheet currency that will apply currency but only allow four digits there's a whole number that will show it as a percent date time date time text true false and binary as we will see later that allows us to actually have files in a column where we're allowed to do things like combine tables now let's scroll over and so here's test one abc we can see the alignment to the left that tells us it's text but when power query applied change type to all the fields it usually guesses right in this case because there were no decimals it shows the whole number data type which shows as a one two three icon you can also tell that these are numbers of course because they're aligned to the right so these applied steps the automatic ones and the ones that we're going to add later this is the foundation of a power query query but behind this foundation is an amazing power query functional language called m code and microsoft says the m and m code means data mashup that means to bring lots of data together and mash it up into the proper form now power query with the automatic steps and when we use the user interface to add new steps power query writes all that code for us now you can write your own m code but most of the time you don't need to i will show you how to write some m code though when the m code we can write is better than what power query can write now i gotta go show you where to look to see that m code because it is a thing of beauty that microsoft created now first there's applied steps but second when you click on any particular applied step look up in the formula bar and you can expand the formula bar look at the names of the functions csv dot document file dot contents that is amazing microsoft named all of these m code power query functions with really easy to understand names and there's the file path if we go to promote headers sure enough they named this function smartly table dot promote headers if we click on change type table dot transform column types so we have applied steps and then in the formula bar we can see each particular step and the m code that's created but now i want to show you the third place to go and look and notice that's called source there's the formula that's called promote headers there's the formula if we go up to in the home ribbon tab query advanced editor let's click here's the advanced editor in a new window and this is all the official m code it always starts with let there's the name of the step then an equal sign and then the formula now notice this first step we were allowed to go look at it it's called source when we click in the applied steps over here it shows us the result of source at the end is a comma and then of course the next line there's the name of the next step now why is there a pound sign well because in power query and in excel also we know that any text is in double quotes so if this name just use double quotes power query would think it's text so when they created m code they said hey when we put a pound sign on the outside and then double quotes that means it's an official name for an applied step now source does not need the pound and double quotes because there's no space so second step equal sign table dot promote headers a comma now the other amazing thing step one is name source but what does the next step do promote headers it works with that so for automatic steps and when we use the user interface the first argument of the next function is always the name of the previous step comma at the end change type table dot transform column types and sure enough there's the name of the previous step now when it gets to the last step what there's no comma that's because in and repeating the last step tells power query this is what it's going to deliver as the final value in our case it's a table with promoted headers and changed data types we can click cancel now the majority of the time all we need to do is just look at applied steps but now you know applied steps see the formula in the formula bar advanced editor to see everything all right now that we've had a complete preview of the magic of power query let's finish cleaning and transforming this table now if i'm going to do a step i definitely don't want to have source selected i want to act on the last step so i select change type now our first goal is to remove some columns we do not need quarter and course number and there's some columns at the end we don't need the easiest way to do that is up in the left hand corner there's a drop down we click and select choose columns this is especially nice when the table has a lot of fields we don't need quarter or course number we don't need current any of these calculated columns at the end name id all the tests all the quizzes we're good to go click ok there's our new step table dot select columns well that's a smart name notice the first argument contains the name of the previous step now just if you're curious of course if you go up and look the new last step better not have a comma and there it is and it doesn't and the previous step has a comma cancel now we need to rename some of these fields i don't want the summer 22 so i'm going to double click test 1 i'm going to not have a space and enter it adds a new applied step table dot rename columns we could look at the function notice it's acting on the previous step and there it is there's the old field name and the new field name now you can keep this open while you rename the field so i'm going to double click test 2 with no space and enter and sure enough there it is and in power query curly brackets mean a list and notice there's a list within a larger list each one has the old field name and the new field name we will continue on test three and enter test 4 enter quiz 1 enter quiz 4 that's the last one and enter now we can close the formula bar and our last task is we're going to have to add data types now i don't want whole number 1 2 3 i actually want decimal in case we get future scores that are decimals also quiz 3 and 4 have a text data type so when we get numbers later those numbers would not work so we want to change the data type and we absolutely could select the last type and do it one column at a time but i want to be efficient and not only that we have this previous step that already changed the data types so what i'm going to do is i'm going to delete it and just redo it down here that way we're being efficient the fewer steps the more efficient it is now you can hover and use the red x but you got to be careful when you're deleting a step if this step would adversely affect the subsequent steps you don't want to do it but changing the data type has no effect on removing columns or renaming columns so i'm going to delete this and this warns you deleting this step may affect subsequent steps but it's not going to have any effect on subsequent steps so we delete i select the last step i'm going to start at the beginning click on name hold shift click on id and instead of doing it one at a time right click change data type and we'll go down to text now we select test one all the remaining fields are numbers so i scroll over hold shift click on the last one right click any one of the field names change type and we want decimal and there's our new step table dot transform column types we see nulls in these columns that's what power query uses instead of showing an empty cell now we've done all of our transformations we have our steps it's named and now we're going to load our table to the excel worksheet in the home tab close group we click the close and load drop down if you click the button or select this first option it does the default it loads it as a table on a new worksheet if you want control over where you load it you choose close and low to by the way the close means it'll close the power query editor and load gets us to the next dialog box so i click here's the import data dialog box if we select table it will load it as an excel table we can also check pivot table report it'll put it in the pivot table cache or pivot chart that also goes into the pivot table cache if we select only create a connection it doesn't load it to the worksheet as a table or in the pivot table cache if you leave it just like that it just stores the query in the power query editor and doesn't load the data anywhere later when we use powerpivot to load it to the data model we'll select this because we don't want it in the worksheet and then check this so it goes to the data model for us we want table existing i'm going to select c4 and when i click ok there's an excel table loaded to the worksheet now when you load a query over on the right the queries and connection pane always comes out too small so we have to widen the task pane queries in connection that's this button right here if you don't see it now notice there's the name of the query if we hover we get a preview we can see how many columns there's the load status it tells us it's in the worksheet but later when we load it to the data model or a pivot table cache that's a helpful hint about where it was loaded and there's the file path now the name of this query is summer business 101 scores but that's the query object we also have an excel table object here and it has the same name if we go up to the name box in the formula bar sure enough that's the same name but that's the name of the excel table object and it makes sense that a query object and table object have the same name because they're related in fact anytime we want to edit or change this maybe we made a mistake we can just open the query change it and reload it in fact what i really wanted to do is i wanted to split first and last name and then sort the table by first name so let's go do that and then reload the query to open the power query editor double click our goal is to split this column but i want the change data type step last so i'm actually going to x this out i have the last step selected so now i select name i can go to home transform split or right click split column we want to split by the delimiter space now it guesses right and we only have one space here so any of these options will work when we click ok now we have split one column into two columns there's our new step and look at that it added an automatic change type now i don't want that because i want to manually do this at the end so i'm definitely xing this out there's actually a setting in file options to turn off automatic data types now the next task is to rename these and by all means we could select the last step double click and rename but this is one of those situations where if we look at the m code in the formula bar we can probably figure out what's going on table dot split column there's the name of the previous step there's the original name it has this big function that actually did the splitting but look at that there are the new names and it's in a list so we can simply edit the m code selecting name one we'll type first name selecting name two last name and now when i hit enter i did that again i'm going to x this out but sure enough we did some m code editing and now we have first and last name now we need to move last name to the first column so we can click the field name and drag it over there's a new step now we come to the filter drop down and look at this sort and filtering looks just like over in the worksheet i'm going to select sort ascending and now we have accomplished our task two different columns sorted correctly i see all of the steps we want to add our last step i select last name hold shift id right click change type and text select test scroll over hold shift select quiz 4 right click and we'll change this data type to decimal number now we have all of our steps everything's looking good we come up to close and load this is grayed out because we've already loaded it so all we have to do to reload is click the close and load button when i close and load that is a thing of beauty we have updated the excel table by editing the query now next we want to scroll over and we want to add two new fields one for total score and one for percentage grade now anytime you're making calculations like this in a column you have a choice you can use worksheet formulas or you can do them back in the original query either way is fine sometimes people are more familiar with the worksheet formula so they like to do it that way but in our next example i'll show you how to create a new calculated field in the query before we load it to the worksheet now this is going to be called total score so when i type a field name it adds a new field to the excel table object percent grade and enter i'll change the column width now before we do our formulas up above i'm actually going to list the maximum scores so 100 100 and then for the quizzes 30 and 30. that way we can add up the maximum points for assignments and use that in the denominator for our percentage grade so max scores i'll enter some data and some formatting now i went ahead and added some formatting the numbers for the completed assignment and then we want to add them so i hit enter so so far the maximum total points possible on this class are 260. now i want to add the total score for each student alt equals and i'm going to redirect it test 1 to quiz 4 notice this is a table so it lists the name of the table there's the implicit intersection operator which makes this a relative range of cells as the formula is automatically copied down the range will move and then it shows instead of a cell reference colon cell reference it lists the field name field name colon field name so when i hit enter there's the total scores now we can build our percent formula equals left arrow and there's the syntax for relative cell reference divided by and this is a cell outside the range so i have n2 and we need to lock it so we hit the f4 key when i hit enter the formula is automatically copied down and in each cell we compare accumulated points for the particular student compared to the total possible 133 divided by 160.51 257 divided by 260.988 now we can display this as a percentage highlight control 1 percentage two decimals click ok i'm also going to add some style formatting right click mini toolbar and then some borders so what we've done is we created a query and loaded it to the worksheet and then added formulas and formatting now let's go look at the updated website here it is test three has been posted now this is our fake website but what we would do is click csv and download the new file now there's two ways that you can download the file and the way that i usually do it when i download grades from canvas is when i download it i replace the old file using the same name that way over in power query all i have to do is refresh but if you want to keep a historical record and at any time be able to show any one of the sets of scores then you download the files and name them differently that's what we're going to do we're going to go over to power query and say hey power query don't look at grade 01 look at grade 02. now in excel there are two good ways to update the data the first way is let's edit the query double click and in the source step you click the gear icon this dialog box allows you to browse so you click browse you find your file selected click open i'm going to click escape that is one way to do it the other way to do it is in the data ribbon tab get data data source settings now i like to do it this way because over in power bi desktop this is very similar to how we do it we open up data source settings we may have many connections to many different data sources we select the one we want and click change source here's the same dialog box i click browse select file to import ok close and we do have to refresh you can click the refresh button there or you can right click the table refresh and just like that i have the new scores i come up here type 100 and enter and everything's updated now what if we get yet more scores well let's go up to data source settings select the source click change and instead of browsing guess what there's only a one character difference so i'm simply gonna add a three here click ok click close right click refresh add the 30 at the top and everything's working all right so in this example here we use grade data but by all means you might be getting sales data or government data from a website using a csv file we saw how easy it is to get power query to import it we can clean and transform it load it to the worksheet and update later now we want to go over and look at our next example i'm going to click on the sheet pq2 pt info example six this is going to be one of the best examples in the entire video and the reason why is business people for decades have had to take multiple files with monthly sales data combine them into a single table make their monthly sales report and plot the results but the problem is for decades this has been a horrible manual process and when you get new data it was always very difficult to update the solution but then came power query power query has no problem importing all these text files combining them into a single table calculating sales then we load it directly to the pivot table cache and the pivot table is suited perfectly for these types of calculations and then we plot it with an excel chart not only that but if we start with nine months of data and create our solution all we have to do is drop whatever new monthly files we have into the folder and click refresh and everything will update now looking inside of our example six folder that we downloaded we have nine different text files now i want to open one of these up right click open with word now i opened it in word because in the home tab paragraph i want to click the non-printing characters button and now you can see the delimiter that arrow in word means a tab this is the second common delimiter besides a comma and you can see we have one two three fields date units and price when we get over to power query we'll combine all 12 of these and then more easily than in the excel worksheet we'll get power query to multiply these two columns and create a sales column now starting on the sheet pq2 pivot table we're going to go up to data get transform and the get data drop down from file and this is without a doubt one of the most amazing things that that power query brought to excel we can tell power query to get everything from a folder so we'll click and we navigate right down to our folder we're not going to see anything from inside of it because power query is just going to look in this folder and take everything that's in it and bring it into the power query editor so we click open there's a preview and this is different than our last example this is a table but binary means there's a text file which to us is a table in each row all of these fields here are attributes for that file this is just like you would see in windows explorer date access date modified the folder path and also very importantly we'll have to use this column here the extension now we click transform data we can see on the left we have two queries now we immediately come over and rename this i'm going to call it many files into one table and enter there's our source step up in the formula bar there's the function folder dot file and there's our folder path now this content column contains the text files now if you click off to the side don't click directly on the word binary because that will extract just that one file but off to the side we can see this icon preview of january february and so on so that means in this content column we have a column of tables with the same structure field names at the top records in rows all with a tab delimiter that means with power query we can easily combine these into a single table and the way you combine the files i'm hovering over this double downward pointing arrow button it says combine files now you need to be careful binary just means there's a file here if these were excel files or database files and the table object in each row did not have the same structure and delimiter for every row then this button would not work so that's when you use this button only when the tables have the same structure and delimiter now before we use this button we want to think about the fact that power query is pointing to a folder and getting all these files now right now it looks like all of the files are text files but what if we accidentally put an excel file or a database or a word file in that folder then if we tried to combine it wouldn't work now the best way to deal with this is just to make sure that there are only text files in this folder but if there might be some other files there's an easy way to prevent those files from appearing in this column and it's by using the extension field if we use the filter option to only include files with a dot txt that would solve the problem now we have to learn something really important about power query m code power query m code is case sensitive that's different than the excel worksheet almost everything in the worksheet is not case sensitive but in power query everything is case sensitive so it looks like some of the files come out capital txt and some lowercase so if we're going to filter here we're going to have to say hey include that possibility this possibility this possibility and so on but we don't have to do that because because microsoft knows that and so they offered an option let's right click the extension column come down to transform and sure enough there's an option to transform everything into lower case so when i do that now we only have to filter to include txt lowercase now it added this new step now we come to add a second step down to text filters i want to say only equals and because we thought ahead and made everything lowercase that's all we have to do click ok now we're ready to combine we want to remove all the attribute columns first so i come to content right click remove other columns and there's our step now we're going to click this button and when we click this button it is going to do a lot of things for us it's first when we click it it's going to ask us some important questions now because there's a lot of files here power query is just going to use one of the files to build all the code for us so it's saying i'm picking the first one here's the file origin make sure the delimiter is the correct one and it is tab based on the first 200 rows that is fine now when we click ok here power query is going to build all sorts of new queries for us all of these queries were automatically built and then used to get this one final table that is a thing of beauty now we don't really need to know about all of this because we were sure that all the tables had the same structure but what it did is it took a sample file built a parameter which is a variable then from one file transformed just one of the files then it built a custom function and down in our final table it added a bunch of steps including invoking that custom function simply amazing now the last step we need before we load this is i want to multiply units and price in a new column so watch this we can select the column hold shift click on price i've selected two columns and i want to add a new column so i go up to add column there's all sorts of amazing ways to add columns but from number we click the standard and when we click multiply notice we're clicking something in the user interface it'll build the formula for us there's nothing like this in the worksheet so we'll click multiply and sure enough it took 3 times 21.95 and got 65.85 and then did that for every row now here's the new step let's look at the formula in the formula bar table dot add column it's definitely acting on the previous step multiplication that's the new name and then for each row units times price and in the last argument it was polite it added data type decimal it's called number but that means decimal now we definitely want to rename this so instead of double clicking and adding another step we're going to be smart we're going to write better m code than adding a new step we could tell that in the second argument that's where it named this field so i'm going to double click and call it sales and enter all right so now you can say you've actually written your own m code let's close this we have all of our steps we are ready to load this now let's go up to home close and load drop down close and load to and the import data dialog box we do not want to load it as a table and the reason why is we're never going to use that table for things like filtering sorting or making other formulas so why duplicate the data source by putting it into the table and then making a pivot table which stores it in the pivot table cache in this case when you don't need to table and you want a pivot table that's your selection we want to put it on existing c4 click ok and even though i don't have a table in the worksheet it is in the pivot table cache now let's build our pivot table we'll drag date down to rows and of course instantly it will build a bunch of extra columns in the pivot table cache i don't need date i don't need days i see months that's what i want let's change the column width and i'm going to click up on the formula bar just because it was my cursor was close change that to months i drag sales down to values i get sum of sales in the header i'll put in parentheses a dollar sign to indicate the unit and then this is a standard pivot table so in the values area right click number formatting when i use the format cells with a single number tab i'm formatting the actual field so i'll say number comma and i'm going to show zero decimals i don't need to see the pennies click ok now we need to show the month over month change and then the percentage change and then percent of total that's where show values as comes in we drag sales down a second time right click and summarize values by has to be sum because we want to see the change in total sales so the aggregate calculation is right so we come down to show values as and there's this amazing difference from so i select that it wants to know difference from what well of course i want the month to month difference and when you're doing month-to-month difference sometimes accounting reports require everything based on january but that's definitely not what we want we want the previous so previous means when i get to this line right here i'm going to take the difference between this and the previous when i'm down here the difference between this one and the previous month i click ok and if you do the subtraction off to the side 520 minus 671 you're gonna get minus 150 000 right click number formatting will show it all in zero decimals this will be called change with a dollar sign now we drag sales down again right click summarizes sum so we go to show values as and percent difference from months previous click ok and that calculation is polite it adds the number formatting up here i'll type percent change and enter and then we want one other calculation i want to compare each one of the individual monthly amounts to the total at the bottom of the column so i drag sales down to values right click summarizes sum so i do show values as percent of column total percent of total sales double click i'm going to change the number formatting on both the percent columns to show zero decimals now what we want to do is go add some files to the folder and refresh this report remember that query right there is looking into that folder so when we refresh it'll bring in all new files now here's october to december so i click on the first one i hold shift i click on the last one and i want to put in some files that are not txt we want to test our theory i'm holding ctrl key to select items not next to each other we'll do a pdf and a dot csv copy those select the folder control v alright so these should make it into the report showing us october november december and these extra files should have no effect on our report since we loaded this directly to the pivot table cache all we have to do is right click refresh the pivot table and just like that that is absolutely amazing in addition let's go look at the query double click and we have our one table let's look at source and if you don't see everything here you might have to click refresh preview but sure enough it got october to december dot csv and the pdf and if we look at our filtered rose step and sure enough our filter which was protecting our report worked like a charm i'm going to close this query editor now we want to make an excel chart control and roll to zoom in however when you create a pivot table with multiple calculations if you point a chart to this pivot table it will include all the calculations and you can't remove them now that's different than if we built this with formulas if you build a formula report with many different columns you can pick and choose any of the columns for a chart you're making but no problem we're going to highlight this and i'm going to copy the entire pivot table and then off to the side i'm going to select i4 and control v i want to point to the smart tag and say keep column widths scroll over click inside the pivot table i see queries and connections but let's go back to our pivot table task pane and i'm simply going to drag the calculations off except for the one we want for our chart sum of sales by the way if we didn't copy and paste that pivot table and you wanted to access the data in the pivot table cache you could go up to insert pivot table drop down and that's what this option is for from external data source i click you have to choose a connection and then there's the query and the name of our table so you'd select that click open but we have our pivot table here with a single cell in our pivot table selected we go up to insert chart group and we're plotting values in a time series across consecutive months so the perfect chart is charts line chart let's do the one at the right here with the marker so i click now we don't need these field buttons so right click hide all field buttons this is chart junk it's not adding anything a legend really doesn't help unless you have more than one series of numbers so i'm using the delete key up above i'm going to create a chart title something like company change and monthly sales i select the chart title when i see the solid line i type in equal sign that shoots me up to the formula bar i select the cell i see the formula i hit enter with the move cursor on the outside edge i'm going to click and drag now i'm going to hold alt to snap to the grid now let's move holding alt so we snap to the grid now i want to add some percentages and conveniently we're going to steal them from this other pivot table chart elements green arrow data labels and when we click more options it opens up the correct task pane we'll uncheck both of those and we want values from cells we'll get percent change click ok check values from cells down here label position will say above now for the vertical axis i'm going to select it control 1 and in the task pane we're going to say display units and i'm going to say in thousands actually this is a pretty new feature in excel it's been over in power bi desktop for a while but that's looking good so we have our visualization and our report all right in this example we saw how to go from 12 files use power query to combine them create a sales column load it to the pivot table cache create our report and then our visualization all right we want to go over and look at our seventh example on the sheet vlookup and pt now in example number seven and eight and nine also we're going to have three different tables and from the three different tables we're going to need to create reports and visualizations now in all three examples we have what's called a data modeling problem our table does not have all the correct columns we need to create our final data analysis solution if you look at sales rep id these are numbers but i need to look up each one of these numbers and bring over the sales rep name same with product i need to look up each number in each row bring over the correct product name as a new column once we have both sales rep name and product name then we can build reports that actually have the name or down here the product name now we could make the reports with sales rep id and product id but then the reports and visualizations would not be as user friendly now anytime you're trying to build reports and visuals from multiple tables and you have a lookup situation there's a number of different ways to solve the problem now the first way is to use worksheet formulas we're going to add two extra columns and use the x lookup function to look up the sales rep and product names now when is it appropriate to use worksheet formulas well if you have the data in the excel worksheet already and control down arrow you don't have a lot of data 43 000 rows is not a lot of data control home and our data analysis solution can be sufficiently created with pivot table reports and excel charts then we can go ahead and use worksheet formulas have one table and then make our reports and visuals now example eight and nine we're going to have the same tables let's go look at this sheet rdm dax example eight we're going to have the same three tables but they're going to be coming externally from an outside excel file so we're going to use power query to import and instead of loading it to the excel worksheet and creating almost a hundred thousand formulas we're going to load it directly to the power pivot data model that allows us to build relationships between the tables and then actually build data model pivot tables based on multiple tables now we choose this approach of course we're using power query because we're importing but we choose power pivot because in the data model in powerpivot it allows us to create relationships between tables and avoid all those x lookup formulas in the worksheet and later we'll see another reason for using powerpivot or power bi desktop is if you have a lot of data power pivot and power bi desktop can deal with big data much better than the worksheet also we'll get to see that power pivot and power bi desktop allow you to create reusable pivot table formulas called dax measures and then in example 9 we'll have the same data source we'll use power query inside of power bi desktop load it to the data model there and make data model power bi visuals and dashboards now when do you use power bi desktop instead of power pivot or the excel worksheet well the most amazing thing about power bi is that you have interactive and shareable visuals all right let's go over and start the x lookup pt example now we have our three proper data sets and i've already converted the first two to excel tables this table is named f transactions this one is named d sales rep let's click in the product table control t and then enter immediately come up to table design properties and we're going to call this d product now why the d the d is for dimension and this is a dimension or lookup table these contain the attributes that we want to come over in our new columns that will serve as conditions and criteria in our reports and charts now there's a very important characteristic to look up or dimension tables and that is the first column of any lookup or dimension table must have a unique list and both of these do and the reason why is simple over here in the sales rep id column we have many repeats three through three five five and so on but for a particular row when the lookup formula is going to look up three and try and find a match if there were duplicates it wouldn't know which one to bring back so dimension or lookup tables always have a unique list in the first column and then attributes or characteristics that can be used for conditions or criteria for making some summary calculation or other characteristics such as a product price that you might look up and use in a formula by the way this one f transactions the f means fact because these are the facts that we're trying to summarize now we're going to use excel tables we don't necessarily have new data we're going to add later but that's the main benefit of excel tables if we add data later or new products everything updates also as we have seen before when we add new columns the columns are automatically incorporated into the table object and also the formulas we create are going to be easier because we're going to use table formulas now this new field would be called sales rep and of course when i hit enter a new column is added to the table now we need to look up three here five here four here and in each case we need to get a match in the first column and then go over to our second column and bring back that value now in the old days we used the vlookup function but that is a function from the past it's much more difficult to use than the new amazing x lookup function now our goal is to put a lookup value that is what value we're going to take and try and match in the first column so i select that cell that's a table relative reference comma lookup array that's which one of the columns or fields has the items that we're going to try and match based on the lookup value well it's that one right there comma return array those are the values that we want to bring back to the cell now this is an excel table so i should have clicked at the top with my downward pointing black arrow and that puts the column reference in now there are a bunch of other arguments in x lookup we have a video all about lookup functions a few videos ahead so we'll learn all about these arguments in that video but for doing an exact match where you're trying to exactly match something in the first column you just need lookup value lookup array and return array now this formula will automatically copy down because it's an excel table that's a relative reference but when we highlight entire columns those are locked as the formula is copied down which of course is what we need close parentheses and when i hit enter wow i've added a sales rep attribute column that i can now use as a condition or criteria to help do some summary calculation now let's do the same thing over here this would be called product enter equals x lookup i'm going to arrow over there's the relative cell reference comma lookup array i click at the top of the product id column in the lookup or dimension table comma return array those are the items that i want to go and get so i select that field and there's our formula close parentheses and enter now if this is forty three thousand rows tall forty three thousand times two is almost ninety thousand formulas now excel is going to have no problem this is a small data set but if you really did have a hundred thousand or five hundred thousand rows we would be switching over to power pivot or power bi desktop that much data is handled much better with those tools now the rule i use is about 50 000 rows is the max i put into an excel worksheet but the worksheet does have a million rows so you can add a lot of data here but things tend to slow down when you do that now we have our one table and when we add columns like this from dimension or lookup tables this is called a flat table and all that means is we have one table when you have one table you can use what's called the standard pivot table from table range now the standard pivot table is the pivot table tool that works with one table only and uses summarized value by or show values as calculations a data model pivot table which we'll see when we use powerpivot is a pivot table tool that works with multiple tables and uses dax formulas to make its calculations now i'm going to use the keyboard with a single cell in our one flat table alt n v t now i'm going to put this pivot table on this sheet to illustrate the reports and charts and tables all together normally i'd put it on a different sheet clicking in cell k19 click ok the first one is sales rep so we're going to drag sales rep down to rows instantly we get a unique list we're going to get the sum of sales so we drag sales down to values right click number formatting number comma will show zero decimals click ok now go back over click in a single cell alt v t existing i'm going to put this one in n19 click ok region that was an attribute that was already in the table we drag it down to rows to get a unique list drag sales over to values right click number formatting now i want you to notice something we are using the sales field in multiple reports and every time we have to add number formatting that's just the way it is with the standard pivot table when we use powerpivot or power bi desktop and create data model reports and visualization we won't have to repeatedly add number formatting because you can add number formatting to a reusable dax formula but with a standard pivot table you have to reapply the number formatting each time you use the field click ok come back over to the table alt nvt existing i don't want to put it directly next to the table and i definitely want to put it at least a few rows down i'm going to select k29 click ok product i get my unique list sales down to values right click number we'll add number comma 0 decimals click ok now this one we're not necessarily going to look at we're going to make a chart from this and anytime we have categories like this and we want to compare the amounts across these categories the perfect chart insert charts is not a pie chart research shows that it's harder to tell the differences between the amounts with a pie chart the preferred chart type for comparing amounts across categories is either column or bar don't use the 3d that's mostly chart junk bar chart is a good choice when you have very long labels otherwise column now we'll talk about some of these other two-dimensional columns and bars in our upcoming dashboard and visualization video for the time being we're going to select clustered column right click the field buttons hide all field buttons we'll remove this chart junk delete i'm going to move this temporarily over here right here product sales total and i'm going to indicate the unit and we want to show these in thousands so at the top i'm going to indicate that in the chart title here's the chart title equal sign that shoots me up to the formula bar click on the cell and enter now i would like to show the amounts at the top so we come over to our green plus data labels and actually for this one we can just click data labels now let's do a little trickery because here we have chart junk right we probably don't want both of these but i want to show these in thousands which will then show these in thousands and then i want to remove it because i don't want the double numbers so with the vertical axis selected control 1 to open up the task pane display units thousands and sure enough that it achieved our goal but now i'm going to delete this and there's our finished chart and there with the help of a little data modeling with the x lookup function we've created our two reports and our visualization now we're going to use these same two data sets and use slightly different tools and to summarize here we didn't have so much data it was relatively quick and easy to add these x lookup functions and the calculations and the visualization we wanted are easily done with a standard pivot table and an excel chart let's go over to the sheet relationship data model and dax formulas now we'll go look at our excel file that contains the three tables then we'll use power query in excel and powerpivot in excel to create these data model reports now if you've never used powerpivot before you have to enable that feature file options add-ins on the left manage drop down and we'll select com add-in click go and then you check microsoft powerpivot for excel or here's a great tip in the data tab in the excel ribbon data tools if you click manage data model we're supposed to jump over to the data model but when you click it and it hasn't been enabled it says do you want me to enable it so i'm going to click enable and there we see the empty data model and more importantly there's a power pivot tab in the excel ribbon now the funny thing is we don't really need to use this tab but it does have to be showing so that this button works and the reason we're not going to use the powerpivot tab when we click this to jump over to the data model we can do everything we want like creating relationships and dax formulas more easily over in the data model now on the sheet pp what does powerpivot do it creates data model pivot tables and does these things it allows you to create relationships between related tables to avoid many lookup formulas like x lookup it allows you to use more than one table in a data model pivot table report it allows you to import large amounts of data which we do not have in this example but in our last example we'll have 7 million rows of data so we can import using power query we import into a behind the scenes data model location called the columnar database this compresses the data and can hold millions of rows of data and powerpivot will allow us to create reusable formational dax formulas these are formulas used in data model pivot tables that means instead of using the default summarize values by and show values as we'll build our own formulas now let's go over to rdm dax now we can go look at our source file in our video for folder here's the example 8 source tables i'm going to double click to look inside now on the table sheet there's the fact table and one two three dimension tables we use both of these tables in the last example but we're also going to have a date table and it functions the same way as looking up a unique list and returning a product name from this date over here where there are many duplicates here we have a unique list of dates and so we can find a particular day and return a particular attribute like month name year name you can actually add lots of other columns to a date table weekday fiscal year and many more we'll actually do some of those other date attributes in later videos but the only data attributes we're going to need in our reports are the month name and the year now you might be asking why are we creating a date to mention table when a standard pivot table will automatically group dates into months and years and the reason is we're going to load all of these tables not to the pivot table cache but to the data model and when you're in the data model because we can potentially have millions of rows of data we don't use that automatic date feature in a standard pivot table we build a date dimension table just like we would for product and sales rep in addition when we load these dimensional lookup tables to the data model we're going to avoid having to create those helper columns where we use the x lookup function and the way we're going to do that is we'll create a relationship between each dimension table and the fact table now if we scroll up i have a visual here that helps to understand relationships now in each one of these lookup or dimension tables the first column is always going to have a unique list that means one of each date one of each product id and one of each sales rep id in a relationship this is called the one side however when we go to the fact table the sales rep id column is allowed to have many duplicates and that makes sense because this sales rep is going to make lots of sales that's why this is called the many side so what we'll do in the data model is create a one to many relationship then we won't have to add a helper column with a lookup formula we can simply drag and drop fields from any one of the tables and our report will work perfectly so unique list one side over to the many unique lists for product id over to the many one to many relationships so our next task is to import these four tables into our other excel file now here's the thing this is an excel file and we're going to import this using power query now remember the text files when we import a text file there is only one table in that file but in an excel file you can have many tables and you can actually have many types of objects like define names or even the entire worksheet so what we'll do is we'll say hey power query i want to import the objects from an excel file and power query will immediately say which one of the objects do you want and we'll just check these four tables so we'll close this scroll down we're going to create our reports down here but first we have to go up to data get and transform and import tables from our excel file get data drop down from file from excel workbook we're going to click video 4 folder there's our example 8 source tables you can double click or select it and click import and power query is polite it's looking in that file and the objects that it found are four excel tables and a worksheet called tables we want to select multiple items and check each one of our tables by the way that blue field name line at the top that's the little icon that means this is an excel table as opposed to a worksheet now when we click transform it'll open up the power query editor and create an individual query for each table so i click transform data here's the power query editor on the left we want to close the folder that has all the queries from combining the tables there's our grade table that's the first query did that's the many files into one but here it is d date we want to click on each query and make sure the data types are correct date whole number text whole number those are all correct that's a great name for our query and those steps are fine d product we're checking data types whole number text the name is fine the steps are fine d sales the data types are fine that's a great name the steps are good f transaction date whole number text and correctly it it assigned decimal to the sales column and then whole number for product id that is a good name the steps are good actually these three steps are the same in each one of these four table import queries so if you go back to source in any one of those queries open the formula bar you could see the on-premise file path there and notice the functions are named so smartly xl.workbook because we're accessing a workbook file.contents because we're trying to get some of the contents from that workbook and this formula delivers a table down here if we scroll over that has a column called data and in each row is one of the different objects in fact d date if you click off to the side from the word table you can see a preview that's the d date table here d product so this column here has all the objects from the excel file that's step one it navigates through this table and selects just one of the objects up here you can see it got the f transactions and there's the table and those sets of applied steps are the same for each one of the import queries here navigation is getting d date now so we don't have to add any steps like we did in earlier queries now all four of these have not been loaded so when we go up to home close close and load drop down we definitely want close and load two in the import data dialog box we definitely want to come down and say add this data to the data model but when you use this in the future and you know you're going to the data model your reflex has to be the very first thing you do is come down and check only create a connection the number one mistake people make when they're learning power pivot and data model is they keep this selected and then they come down and click this and the problem is when you click ok it loads the tables to the worksheet and the data model so we want to say only create a connection by selecting this we're forcing this top part to have no effect we're definitely not loading it to the worksheet and we're definitely not loading it to the pivot table cache so only create a connection add this to the data model when i click ok over here in queries and connections you can see power query is working to import this but also the data model that columnar database it compressed all of this data now you can hover and we see loaded to the data model that's to the data model data model and this one too now that we've loaded these to the data model we're definitely going to go look and build relationships and our own formulas now i'm going to use control s to save then i'm going to click the manage data model button now you can see this opens the power pivot for excel window it's a new window on top of excel this is not power query it's not excel this is our data model window it has its own tabs down here these are previews of what's in the columnar database they show it just like a real table there's d date d product d sales rep and f transaction now what i mean by a preview here is if you have millions of rows in the columnar database it's definitely not going to load it all here but it'll give you a preview now later we'll come back and in the fact table we'll build our formulas down in the what's called the measure grid now when we loaded this in the home tab view we're in data view and you can see the little tabs that means we're allowed to get a preview of the tables but we want to go over to diagram view notice the lines between the tables this is where we can go and create relationships so i'm going to click now what we see in diagram view is the name of the table and a list of the fields now i'm going to arrange the fact table right in the middle the date dimension table on the left product and sales rep on the right and in diagram view there's two important things we can do we create the relationships between fields simply by clicking and dragging and then later when we have fields in a table like sales rep id that we don't want to show up over in the reporting area that means the pivot table field list we can hide them now let's first create the relationships and here's how easy it is i'm going to take sales rep id and drag it over and on top of sales rep id when i let go it creates that one to many relationship there's exactly one sales rep id in each row and over here in this column there can be many duplicates we'll drag product id from the dimension or lookup table over to the fact table there is another one-to-many relationship and from the d-date table we'll drag the unique list of dates over to the many repeats in the transaction table and there by dragging and dropping we have created relationships and these relationships will help in two ways the first way is it will replace all those x lookup functions which means the excel file can calculate more efficiently or more quickly because it doesn't have all those formulas and the second thing is we're going to be able to drag and drop fields from any one of these tables into our data model pivot table report so in diagram view we create relationships but we also want to hide fields like date id that's a field we're never going to use in our reports and also a field line date we want to hide that because we're going to use the date field over here also the number field we're not going to drag and drop number fields into pivot tables instead we're going to build our own formulas so in f transactions we need to hide date i click on that one hold control click on sales rep sales and product id right click hide from client tool what this does is it will hide these fields in the pivot table field list now we kept region as unhidden in the fact table because that's an attribute or condition or criteria we can still drop into our pivot table and we don't have a region dimension table product id right click hide from client tool sales rep id hide from client tool and month number we don't need month number although we will use it later the month category we're going to drop into our pivot table is this month so we're going to right click hide from client tool and by the way any fields that are hidden we're still allowed to use them for example we're going to use the sales field to create a formula in just a moment when fields are hidden they're just hidden from showing in the pivot table field list all right so we created relationships and hid our fields now let's go back over to data view now in data view below the bottom of the fact table we can create formulas in what is called the measure grid if you don't see this measure grid that's because area calculation area is not selected i'm going to select this now i'm going to click in a cell below the table and this is one place that you can create what's called a dax measure a dax measure is a formula that you can drag into a data model pivot table the other type of dax formula that you can create in powerpivot is a dax calculated column we're not going to create one in this example now the dax in dax stands for data analysis expressions and expressions is a synonym for formula dax is a function-based formula language which has some functions that are similar to the excel worksheet and some that are unique to dax now when you're building dax measures there's actually two locations where you can build measures now over in the excel ribbon there's a power pivot tab and in the calculations group you can create a measure i don't like how this feature here places the formulas in the measure grid so i tend not to use this some people do though so i'm going to use the measure grid and the formula is going to be similar to excel totally different than power query and if you know access formulas it's exactly like access formulas so we need to name this dax measure and that name will show up in the f transaction table in the pivot table field list so watch this i'm going to start typing in the measure grid and notice what happens it jumps us up to the formula bar now that name will show up in the pivot table field list but now we need to put an assignment operator and then the formula after it now in the excel worksheet and in power bi desktop the assignment operator is the equal sign everything after the equal sign is the formula but in excel powerpivot and access databases we have to type a colon and then an equal sign now our goal here is to sum so i can type sum and we can see the sum function in the drop down now the sum function and other functions like average and min and max those are the same over in excel there are other functions like summarize some x which are only in dax now i hit tab and it says that it wants a column name now i can scroll down and then select there it is right there or in powerpivot and this is different than power bi desktop you can actually click on the column header this is the same as excel tables so when i click at the top of the field name it properly puts in the column reference and column references always have the table name and then in square brackets the field name now i close parentheses name of the tax measure assignment operator formula now when i hit enter i get a result that gives me the grand overall total for every value in the sales column now you'll see the clever thing about dax formulas is when we drop this formula into a data model pivot table whatever the conditions and criteria are in the row column filter area this dax formula will see those conditions and criteria and give us the right answer now another amazing thing about dax formulas is we can add number formatting to the formula and then wherever we use this that number formatting will follow us around so home ribbon formatting i'm going to click comma and then decrease the decimals and there's our dax measure to show you two other simple tax measures we're going to calculate average sales up in the formula bar colon equal av and the average function is the same as over in the worksheet i'm going to select sales close parentheses and enter format this so that average of 318 dollars that's the average for every single transaction in the sales column another formula we might want to see count or number of transactions hopefully i spell it right number transactions colon equal sign and this is a unique function to dax this function does not exist over in the excel worksheet or power query count rows and it's asking for a table so i'm going to put in f transactions notice i type ft i see it in the drop down so i hit tab and the amazing thing is is count rows will take that f transaction and as it sees conditions and criteria in the pivot table it'll count how many transactions there are that meet those conditions now we'll explain a little bit more about how that happens when we build our pivot table but for now close parentheses enter and this is going to be comma and of course 0 decimals because we're counting now let's go back over to diagram view and we can see the f transaction table has some new entries at the bottom now here we are in diagram view in the power pivot for excel window and this is a picture of our finished data model now there's five parts to this data model that we created the first one we can't see that's the behind the scenes columnar database that compressed and stored the data the second thing is we can get a preview of the tables either here in diagram view or over in data view the third thing is we create relationships in this data model then we create dax formulas in this case we created one two three dax measures now the dax measures over here in powerpivot shows an f of x icon this icon and the names will show up over in the pivot table field list and then the last thing we created over here is we hid some fields these are the fields we don't want to show up in the pivot table field list right now we want to jump over to the excel worksheet and make our first data model pivot table i'm going to close the power pivot for excel window with the x now on the sheet rdm dax we're going to select cell c37 go up to insert tables group pivot table drop down and from data model and now this dialog box says pivot table from data model we just select the location and click ok now i'm going to go grab the pivot table fields task pane move it resize it expose all the tables now this is showing you all the tables in the data model plus all the other excel tables that are in this workbook if you hover for example it tells you that this has a range of cell source but these we know they're in the data model but they came from a query now currently we're in the all tab which means we could see every table from the data model and all the worksheets now if we go look at active there's nothing here yet meaning no tables or fields or measures because we haven't added any to the active pivot table but if you come back to all and our first report is going to be a regional report and since we want region in the rows and that's the default i can simply check and instantly i get a unique list of regions in the row and i see the field down here now we want to see total sales for each region and i'm going to check this because the measured by default goes down to values and look at that we do not have to add number formatting we'll check average and it has our number formatting but i would like to change the name of this and the formatting so guess what i can go back to the data model data tab data tools manage data model in data view i select the cell with the measure come up to the formula bar i'm going to call this average transactional sales hopefully i spelled it right and enter and i want to increase the decimal so 1 2. now we don't have to close this we can actually leave this open i can just take my cursor and click back in the pivot table and look at that that measure updated if we had used this in many different pivot table reports they all would update and now let's do check mark for number of transactions and there it is for region we have three different dax formula calculations if we go looking active we can see all the fields we've used now let's come back over to all and i want to create the next pivot table so about four cells below not alt n v t for table on the worksheet we'll use alt n v and d for data model d and because the ok button is selected we can just hit enter now this time i want a sales rep but i'm going to right click the table and force it right up front to go to the active tab transactions also now i can come over to active and that is a thing of data model pivot table beauty we're allowed to have multiple tables in the pivot table field list to avoid all those x lookup functions we definitely hid fields that we don't need that makes this less cluttered and we created reusable formulas called dax measures that even have number formatting so for our sales rep report i simply check that to get my unique list and then check check check i have all three dax measure calculations i didn't have to manually number format any one of those columns now let's create our third report for product and this time we're going to do the keyboard alt nvd and enter we're going to send product over to active f transactions over to active active tab that is a thing of data model pivot table beauty i'm going to check check check check and there's our finished product report now i'm going to move this to the side select cell i 37 alt n v d enter d date right click show inactive f transaction showing active come over here we're going to drag year to rows month down below years and what is happening here the months are sorted alphabetically in a standard pivot table there's a behind the scenes custom list that sorts the month when you drag it into a standard pivot table but in a data model pivot table that feature does not exist to get our months to sort chronologically we have to go back to data data tools manage data model in data view we go to the date table we select the month column and remember this has text but month number 1 to 12 if we could tell this field right here to always sort according to this field then of course january would come first february 2nd and so on in all data models when you have a date table like this you have to use the sort by column feature in powerpivot it's home sort and filter sort by column button you select the column to sort month and by month number when i click ok when we go back to the worksheet the months are sorting chronologically now to finish our report we check three measures we need to change the column width and i'm going to double click between any two to best fit the width of each column and just like that our monthly report is done now let's control s to save now we got to talk about how these one two three reusable dax formulas make their calculations in each row of each report if we look at this second report right here total sales there's the formula when we drop this measure down into the values area behind the scenes it calculated the correct amount for ahmed shantell gigi and it got the correct grand total at the bottom and the way it did it is with something called filter context and what filter context does is when this measure hits this row it sees the condition ahmed that whole f transaction table is filtered down to just the records for ahmed and because the records are just for armed when some sums that filtered sales column it gets the correct amount when it gets to the chantel row the entire fact table is filtered down to just the transactions for chantal and then the measure adds just the sales for chantel but when the same measure gets to the grand total row there are no filters on the table and so it adds to get the correct grand total now the whole filter context process is happening behind the scenes in the powerpivot engine and columnar database so there's no way for us to go look and see how this is working but guess what the process is exactly like the filtering that we learned at the beginning of this video now i made a visual to help understand how filter context works on the sheet filter context visual but remember when we created this report we dragged sales rep from the sales rep table let's go look at filter context visual now here's a picture of the report we were just talking about and here's the d sales rep table and f transactions the fact table and remember from sales rep id over to sales rep id there's a one to many relationship and here's how filter context works this measure right here we drop it into the pivot table but behind the scenes as soon as the measure sees this cell the context is the condition or criteria ahmed because this is coming from the sales rep column in the sales rep table it's just like we learned at the beginning of the video the process filters to show just a med this single condition filter then flows across the relationship from the one side over to the many side and sure enough all we see is five every sale is for just ahmad once the filter has been transferred over to the fact table that's when the measure kicks in and adds all the sales now the reason that dax formulas calculate this way is because if you have a lot of data if we send the filter over here the table is much smaller guess what this measure only has to work across ninety 7694 rows instead of the full forty three thousand two hundred and twenty two so filter context make sure that the formulas are only working over the necessary number of records to make its calculation when the measure gets to the second cell here chantal in the d sales rep table now it's filtered to chantal the filter flows across the one to many relationship all we see is fours all these sales belong to chantal and that's our result and of course when we remove all filters from the d sales rep table there's no filter on the fact table and there's the total and for our count rows function right here now you understand why the function is called count rows all this formula does is look at how many rows are in the f transactions when there's no filter it sees forty three thousand two hundred and twenty two when we select gigi far fewer looks like three thousand three hundred and sixty three rows so count rows counts how many rows sum adds all the sales and the average formula of course averages these so the moral of the story filter context is amazing because it makes dax measures work more efficiently now i want to go over to the sheet mistake now we want to look at two potential pitfalls when you build a data model now we were careful and built our data model to avoid these two pitfalls but i want to show them to you so let's go to data tab over to data tools manage data model and in diagram view we very carefully hid the date field that way nobody's going to drag it into a report if you drag it into a report something really bad happens to the fact table also we hid the sales column because we didn't want anyone to touch that we want them to use measures but if you don't hide it and someone drags it into the pivot table another potential bad thing happens so i'm going to select sales hold control select date right click unhide from client tools now let's go back to this sheet and we're going to use our keyboard alt nvd and enter over the pivot table field list i'm going to right click f transaction show inactive over here we're going to mistakenly drag date in and watch this when i drag it down to rows watch how long it takes all right i'm going to drag off everything but year and now i'm going to drag the sales field this is not a measure drag it over values and already were annoyed because it certainly didn't add any number formatting but now let's go look at the data model we want to go to data view and this is the fact table and the product id is supposed to be the last column what happened here when you drag a date field into a pivot table it thinks you want to group the dates the problem is this is 43 000 rows for each one of these columns that's too many formulas when you use a date table and a relationship we only have two years so this is only 720 rows back over here the other thing is when we drag that sales a number field into the pivot table powerpivot actually created a dax measure but it's hidden if we go to advanced and i don't know why they don't have this on by default we're going to click show implicit measure and sure enough this is called an implicit measure this is the one automatically created by the pivot table this is called an explicit measure we created it ourself now there are many problems with implicit measures the first is well it's hidden second is this is a read-only formula you can't edit the formula you can't add number formatting notice this is grayed out you can't change the name you can't reuse it from the pivot table field list and when we export our data model to other tools like power bi desktop implicit measures do not show up so our rule is we don't use implicit measures we always want to use explicit measures which just means we create them ourselves now the best thing about implicit measures is we unhide them and then right click delete yes i'm going to delete from model i'm also going to select date hold shift click on date month and right click delete columns yes go to home over to view diagram view select date and sales and right click hide from client tool now there's another way to prevent the date field from creating all of those extra columns attached to the fact table if you go over to d date and this works in power pivot and in power bi desktop we want to go to the design tab and we're going to tell powerpivot that this is a date table as soon as we do that it will no longer add those extra columns to the fact table so design calendars mark as date table so i click and click mark as date table it wants to know where the unique identifier is that's our unique list of dates so that's correct click ok and now no matter what we do we're not going to get all those extra columns with 43 000 formulas in each all right let's go back to rdm dax all right we saw some great advantages with a data model pivot table using power pivot by having multiple tables we were allowed to create a relationship and avoid all those x lookups with multiple tables we can drag and drop from any field into a pivot table we have reusable formattable dax measures we also hid a lot of unnecessary fields we saw that filter context helps the formulas calculate more efficiently and guess what if we have a data model inside an excel file built by powerpivot we can automatically send it over to power bi desktop where we have lots of interactive visualizations that are actually a lot nicer than what we have in excel so that's our next task now you want to make sure and save this excel file with a data model and before we go over to power bi desktop which is a different program in this excel file let's go to the sheet bi desktop now power bi desktop does these things first it's a free microsoft download you just click this link and download power bi desktop to your computer and when you get it open and start working power bi desktop has the same tools as power pivot and power query in fact all the tools in power pivot and power query started in excel first and then they invented this other tool called power bi desktop which is designed to have better visuals and shareability now power bi has more varied visuals and reporting tools than in excel and the visuals are interactive there are also more dax functions including table functions and once you create a data model in excel powerpivot you can import your powerpivot data model into power bi desktop now power bi desktop is the free download that's where we build the data model and all the visualizations once you have that file complete you can share that file the traditional way by emailing it to someone and a lot of people in the world do it that way but there's a second tool called power bi online and power bi online requires buying a license now this is august 18 2022 and by this time in history many entities buy power bi online for employees when they buy microsoft 365 office and here's what the online version does different than power bi desktop you can upload your power bi desktop file to power bi online and then it's easy to share the reports visuals dashboards and the data models also you can upload excel files and the powerpivot data models inside of excel files now the sharing of reports and visuals that's on the information side that's when you want to share your results with people but there's a whole other side to power bi online when we upload data models from power bi desktop or from excel powerpivot they become universally available to assigned groups of workers or here at highline college an assigned group of students and teachers that means when you upload data models groups of workers can access the data from within excel or even power bi desktop this means there's one source of truth one location for the data no need to share files by email now let me just show you we already saw an example of this if you go up to insert tables drop down for pivot table there it is once you upload your data model all your fellow workers have access to it no more email in your file with the tables or the data model you just upload it here and then everyone in their different office or here at highline different classrooms they just go to excel log in and they can access the single source of truth data set now in power bi online the way you share things is you create online workspaces for groups of workers to share reports visuals dashboards and data models and all it involves is just adding the emails for your fellow workers or fellow classmates and then you can share your data models visuals reports and dashboards now we'll come back to this excel file a little bit later and upload the data model and then we'll see how amazing it is to have this universal place to save data but what we're going to do next is we're going to take our power pivot data model from this excel file and import it into a new power bi desktop file and then we'll look around and get introduced to power bi desktop however before we do that please control s to save this excel file and then close this excel file before we go over to power bi desktop now i've already downloaded power bi desktop i have an icon to open it on my taskbar and i can see the folder path with this file that's the file that contains the data model so i open a brand new power bi desktop file now here's the power bi desktop file it doesn't have a name and it hasn't been saved so we use the same keyboard as excel to open save as f12 in the save as dialog box we're going to navigate to our class folder and we're going to call it this is what i'm going to name it you can name it that also and then click save there's our name in the title bar now before we take a tour of power bi desktop i want to import that power pivot data model to do that we go to file import and this option power query power pivot and power view we're going to click in the open dialog box we navigate to our class folder and there's our file that contains the powerpivot data model let's click open this message says hey i cannot import everything from an excel file i can only import queries and what's in the data model now when we click start right here we're going to get all the queries from that workbook there are a few we don't need but we'll easily delete them let's click start and the migration is complete here's all the queries here's the data model items let's click close now we could take a proper tour of power bi desktop this is the power bi desktop ribbon it has tabs and over here one two three those are the three areas we can work in in power bi desktop if you hover this model is like diagram view in power pivot let's click oh and there's our imported data model it got everything i think if we look around here adjust things the table the fields the measures the relationships and there's a few differences over here look at that icon for a measure it's like a little calculator instead of that f of x in power pivot and instead of a hidden field being grayed out there's an eyeball with a line through it so this for us in power bi desktop is called model over in power pivot diagram view now if we hover here this is data this is like data view in power pivot so when i click i can see the tables over here there's a list of tables i can click to expand and i see the fields and measures i can click on f transaction and there's the table i also see the measures so data is like data view over in powerpivot and here this is report when i click this is like the worksheet the worksheet would hold data model pivot tables and excel charts and and for that matter you can put anything else from excel in the worksheet here in report view over on the right here are visualizations we can choose from we can open tables and drag and drop fields and measures into our visualizations there's a filter area also some of the possible visualizations and reports well there's a bar there's the other type of bar cluster stack column clustered column there's a line chart x y scatter there's a cool map there's a table a slicer and this is similar to a pivot table a matrix there's also a bunch of other different visualizations and get more visuals is awesome you can go and download all sorts of amazing visuals so we have report data and model but where is power query well home tab everything in data and queries that's power query in excel we have the queries and connections pane to open queries here in power bi desktop queries transform data drop down transform data notice it says this opens the power query editor also notice this is data source settings that same setting in excel is data get data and all the way down to data source settings let's go look at power query editor it looks almost the same as over in excel if we select the query f transactions there's the preview name and applied steps now we don't need a bunch of these queries we're going to collapse this folder before we can delete it though we have to delete the query many files from one table because it is using elements from in that folder click hold shift click on summer business 101 scores right click delete yes i want to delete the queries right click the folder delete group delete now over here in power bi desktop it does not say close and load to over in excel we get to choose where to load it here in power bi desktop we can only load it to the columnar database in the data model so close means to close the window apply means to apply the steps and load it to the columnar database there's the drop down but for us we just click the button close and apply and the steps are applied next we want to build our visualizations and reports we want to create this sales report page that shows region product sales in a crosstab report it also visualizes the sales for sales rep using a column chart and then using a line chart we want to see the trends over the last two years but the thing that distinguishes power bi is if i want to analyze the trends for chantel and see the cross tab with just chantel sales i simply click and that is amazing there's the trends for chantal and the whole table region and product those are all chantal's number that's about 3.9 million sure enough that's the column height and if i want to remove the filter i simply click again i come over and i'm interested in spitfire when i select spitfire this is highlighting just spitfire sales for each sales rep and here's the trend click it again and the filter goes away now the first visual we'll create is a line chart i'm going to click somewhere in the white area go over to visualizations and there's the line chart click we can resize it make it a little bit taller and moving over here has a different cursor and sometimes it's actually kind of hard but it's that white cursor look at that red line it means i'm right in the middle i'm going to let go in visualizations when we're in build visual we can drag and drop fields and measures to these areas of the chart so we'll have year in the x month below year and then total sales is going to go on the y-axis now before we format and do a few other things let's talk about this awesome area here these three dots give us more options if we click we can actually export the data from this particular visual as a csv file show as a table if we click that there's a table down here we have a back to report button we can remove and spotlight spotlight doesn't work here because we don't have any other visuals but it would highlight this and gray everything else out and then we can uncheck spotlight we have focus mode which expands the particular selected visual we can click back to report we have a filter button that will tell you how it's filtered if you have filters we don't have any and then we have one two go to the next level buttons and one two drill buttons now we're already drilled up to the highest level if i select drill down this just turns it on so when we click a particular data point it shows at the next level all of those data points that make up 2021 so when i click there's january to december for 2021. drill up i'm going to turn this off now go down the next level if i click this for a year month this may or may not be an appropriate chart because that's all the januaries all the februaries and so on drill up this is probably the appropriate one it'll go down a level and show year and month now we have the year repeated many times i think i'd like to see just one these labels have been concatenated over in format that's our little paint brush there's different areas and if you're coming from excel it does take a while to get used to where everything is we want to do something to the x-axis if this was collapsed you could open up values and sure enough down here concatenate labels we're going to turn that off we can collapse this it would be nice to have data labels so down here data labels we'll say on now let's go back over to build visual and here's something that does exist over in excel in a pivot table if you hover over a point it shows you a tool tip but over here in power bi desktop we can do better than just showing the amount that's in the vertical axis build visual down here is tool tips we can drag other measures here's number of transactions and when we hover both are listed in the tool tip we drag average down below number of transactions and that is beautiful every one of these tool tips shows three measures and they're calculating the measures based on the conditions and criteria at this point so this would be 2021 march now when we created the tool tip that title is too long and we don't really need it because we have total sales over here so let's go over to format and there's a visual and general here's the title we'll select off all right so we have our line chart next we want a matrix and i got to show you a gotcha i do this all the time i want a matrix right here but i accidentally leave the line chart selected so when i come over to build visual i'm thinking i'm going to get a new matrix but when i click it it's polite it thought i wanted to convert that line chart to a matrix luckily control z gets us back to our line chart the trick is when you create a new visual make sure that the white area is selected and now let's do a matrix we can resize it something like that and when we move it around we can see those lovely red lines and in build visual it looks sort of like a pivot table task pane rows columns values so we'll take region from f transactions that's going to be in the row there's our unique list product we'll put that in the column there's the unique list and we'll drag total sales down to values and there's our cross tab report showing region and product now let's click in the white and create a clustered column we want sales rep we can check this and it will go to the horizontal axis our x and then total sales to the y let's add tool tips here also some number of transactions and average we don't need this long title so over in format general we'll turn the title off back over to in format visual let's have data labels and now when we hover we can see our three measures and here's one of the things that makes power bi desktop so beautiful when i click the line chart is filtered and so is the cross tab report now we click chantel to unfilter now we're allowed to control how these visuals interact we go up to format then click edit interactions so we have the column chart selected so i'm allowed to on the other two visuals decide whether i want it to filter or no i do want it to filter same here i want it to filter selecting the line chart we get to decide filter or not but over here we get a third option highlight and that's the one we want that means when i select an item over here like spitfire it highlights just the amount leaving the total there for easy comparison and then this one of course is filtered clicking spitfire to turn it off we can uncheck edit interactions over on the right we have the filter pane and this filter does three things it can filter on the selected visual it can filter the entire page meaning all the visuals or reports and you can have filters on all the pages so if you have new pages or tabs with other visuals and report a filter here filters them all if we select the line chart here's year for the selected visual we can open it up we could select the logical test equal to which is is and then type i want to see just 20 21 and then you have to click the apply filter button and bam just this visual is filtered for 2021. to clear the filter you can use the eraser now we would like to do a few more things to this report we want to add a title a slicer and a card showing total sales i'm going to move this a little bit to the left let's move this down we'll move this down all right over here let's click in the white and we're going to start with a slicer and we want year then from the drop down we can say list resize it now click in the white and we're going to have a card and we're going to drop the total sales measure we can resize it move it up a little bit end up in format call out value and we'll say value decimal one click in the white we'll try it text box and we can write colorado boomerangs that's the name of the company sales report control a let's say 28 bold we can resize it there we go and center click off to the side now we can select 2020 it filters everything on this sheet 2021 and erase so the sales report is looking good but we want to create another page and show some different metrics for the region so we're going to add a new page double click and call it region report all right we click in the white come over to our visualizations we want a matrix and we're going to put three variables in the row we'll start with the region field from f transactions and then year we're dragging it down if we check these they tend to go to columns so i'm dragging and month below year now if you don't see these expand buttons you can come over to format row headers and turn it on or off back over to build we want total sales down in values actually we can check these and number of transactions we'll expand this let's increase the font size for the whole matrix over to format grid again sometimes these things are hard to find you wouldn't think it's grid options but sure enough global font size and i'm going to increase it to 12 resize it bring it all the way down to the bottom actually we're going to have a title at the top so maybe right about there right click expand to next level so that's how we'll leave it and then as we're analyzing over here we can open the years now let's click in the white and we want two regional bar charts one for product one for sales rep now we're going to look at the difference between stacked and clustered we're going to look at bar charts but the difference is the same for stack column and clustered column let's start with stack bar bars are always horizontal now we want region in the y-axis and then product in the legend and then we'll check total sales there's a stacked bar and it's called stack because the items in the legend are stacked one on top of the other now you use a stacked bar chart like this when you want to directly compare the variable in the axis using the lengths of the bars it's very easy to see which one of these regions is the biggest and the smallest so the emphasis is on the variable in the axis rather than the variable in the legend but if we switch to clustered bar now it's very difficult to compare the totals for the variable in the axis but it's very easy to compare the differences across the product or the item in the legend using the column heights and it holds exactly true if we switch over to stacked column it's this variable down here that's being emphasized by easily comparing column heights if we switch over to cluster column the emphasis is on the items in the legend so we can compare within each region the items from the legend now let's go back to stacked bar now if you want to change the colors of the columns you come over to format down to bars and there you can change all the colors i'm going to leave these colors just as they are pull this up by the way um i like the title i like everything this came out virtually perfect let's add some data labels a data label so over to format down to data labels and on now watch this here's a great trick i need the same exact chart but all i'm going to do is change the item in the legend make it a little bit less tall control c click on the white control v there we go and with this selected we'll remove the legend and watch what happens there's actually no difference between stacked and clustered when there's only one variable but we want to go up to our sales rep table and check sales rep and now we have two beautiful charts region sales rep region product and here's how amazing this whole setup is if i want to look at just northwest there's all the sales reps and the products and it shows me the detail over here i can expand to see a particular year click it again we can click on the outside edge also to unfilter now if we're going to use this as a filter and we only have one bar instead of highlighting let's edit the interactions and filter this i think it'll look better format edit interactions we have this visual selected so i'm going to come down here and say filter and select this one and change this to filter now we can turn it off and now when we click northwest that's looking better click off to the side remove the filter and if we select sue for west that is looking good and the cool thing is if you add up all those amounts it's exactly equal to 0.62 million or the total down here now i'd like to see an excel chart do this this is why if we have a data model over an excel power pivot might as well send it over to power bi desktop and take advantage of some of the interactive visuals next we want to add a title to our report text box [Music] i'm going to click on the outside edge down to effects and background color i'm going to add a very light gray because the next thing we want to do is i want to insert the colorado boomerang's logo insert over to elements image and in our chapter 4 folder there it is colorado boomerangs double click we can resize it and that is looking good now there's one last thing i'd like to do actually i want to show you something about formatting now i am going to change the colors here i'm going to go over to format down to bars and i'm going to change this to a medley of blues so i'm using all these related blues and that's looking pretty good now i do all my formatting manually but in power bi desktop excel word powerpoint all the microsoft tools you can go up to view and i have no idea why this is not in format and there are themes now themes will add formatting to everything you can look through these also you can browse for themes online but if i select this title it formats everything for me now again i don't use themes but you might like to now i'm going to control z and there's our finished regional sales report now our next task is to publish this power bi desktop file and we have two pages with two reports and the file itself will show up at power bi online listed as a report we want to also publish our excel workbook file and when we publish this it'll be listed over at power bi online as a workbook but before we publish either one of these files you have to have a power bi pro license or hire and you have to be at an organization that assigns emails to everyone in the organization like a business or here at highline college and the way you log into power bi online is you have to use your institutional email so here at highline the students in business 218 class and teachers and administrators they'd have to use their highline issued email to not only login to power bi online but also into power bi desktop and of course your excel file now the dialog box for logging into either one of the files or power bi online is exactly the same so we're going to jump over and look at power bi online first before we upload these files now there's lots of ways to get to power bi online one of them is to use this address you could also get to it from your power bi desktop file your excel file or even outlook now i'm going to type powerbi.com and enter it wants your email and it reminds you here about organizational email so i have to enter my highline college email and enter and this is the page that all apps and the website will make you go through you have to go through your institutions login system i'm lucky i have an easy password it's just a bunch of dots but when i hit enter it opens power bi online now here's the home page and it may have some recently used reports or workbooks or workspaces here there's also some items here but what we really want to do is to go over to workspaces and each one of these workspaces is a location that has designated emails so certain people can access each one of these workspaces now i'm going to first show you how to create a workspace which is just a few clicks i'm going to call it fall 2022 business 218. now i added a description and uploaded a picture now i click save now we have no content in our workspace yet to add emails we come over to access and you enter email addresses now i added one person from our entity linda quick also we get to decide what type of participant if you want to learn more you can click this and there's a detailed list we're going to add contributors which allows us to do all the things we want to do create edit and upload reports and workbooks so it changes to contributor click add we have two people in the workspace we could add lots more and actually for this class i will add all the students in fall business 218. now let's add some content that means we'll go back over to those two files now you do have to sign in to both files but once that's done we save our file we have our two pages and then in home we have the publish button click and of course it wants to know where is the destination there's our destination click select it's publishing and if you don't have power bi online open they provide a link you can click this to jump over now it opens in power bi online and shows you the report you just published but over here on the left we can click on our new workspace there's the report that's the power bi file and the data model that was in that power bi file is called a data set content that'll show you your reports and workbooks data sets and data flows that'll show you your data models now we can also click to open up our workspace we don't have any dashboards later but that's a place where we can pin things from reports there's our report we don't have a workbook and there's our data model now let's go look at our report here in power bi online now it works the same as over in power bi desktop we can click to filter and click to unfilter over on the left we see our pages i select region i want to see quad in the east filters perfectly and then unfilter now anyone with access to the workspace can view the report but there's some other options up here you can save a copy that actually saves it online download this file this will download a power bi desktop file you can print the page you can even if you have a website embed code click this and you get the code there's also a generate qr code over here for export we're not going to use this one because we'll open it up from inside of excel but you could export it to powerpoint or pdf you can also share this report you can copy the link and email it to someone directly email from here even teams in powerpoint now we want to go back over to our excel file now here we are in our excel file and when we publish this to power bi online the worksheet data will show up as a workbook none of the queries will show up and the data model will show up as a data set that we can use in various ways now in our second power query example i actually added some formatting and a title at the top just to tidy this up because in power bi online we're actually going to pin this report to an online dashboard now save this and here's how we publish we go up to file publish and there's two options upload your workbook this allows you to interact with your excel workbook but not in excel it is in excel online which is a very limited version of excel and of course you can pin selections from the workbook and share this is the one i usually do this will just upload what's in the data model but i am going to show you this one so before we click either one of these you have to choose a workspace and there it is at the bottom fall 2022 business 218. now we're going to click upload down at the bottom you can see it's working up at the top it gives us a yellow banner and we can jump over to power bi online so i click i got this message but i'm going to click ok then we're going to go look at our workspace open the drop down and i don't know what that message was saying there's our workbook now when we click on the workbook again it's only going to show us just the worksheets not the queries and it opens in excel online this feature is just for us to view stuff you can see all the tabs below that was built with a query but the query is not here but the result is let's go back over to pq2pt if we look at data sets now we have two and we uploaded powerpivot data model to our power bi desktop file so these two are the same and later in excel we'll see that now we have access to these so we can use this as our one source of truth and build pivot tables over in excel now before we do that we want to look at one last thing over here in power bi online dashboards that's just a location where you can pin anything you want from any of the reports or workbooks you have now the word dashboard is very common it just means one location where we can present useful information and metrics for making decisions but dashboards are not limited to power bi online you can absolutely make a dashboard in excel and in fact this page that we made in this power bi desktop report this can be called a dashboard also but when we're in power bi online dashboards are a specific location where we can pin important information and then share those dashboards easily so in summary a dashboard is one location where you can present useful information and metrics for making decisions information is presented in a neat and organized manner dashboards can contain tables charts visualizations data validation pictures and other visualizations of data and when new data arrives the dashboard can be refreshed just like a dashboard in a car a dashboard should present information that is required to make good decisions now to see how power bi online dashboards work let's go to this report i want to pin this to a dashboard and we don't have any dashboards but it will ask us to create one so we go up to the top more options we want to pin this to a dashboard we don't have any dashboard so it's polite it wants us to create a new one i'll call it colorado reports and pin now you can also create a mobile layout and we're not going to do that but you click on that and drag and that determines how it will look on mobile devices there will also be this web view also and there's a dashboard so we can click and sure enough this has been pinned to this dashboard it works just like if we were looking at the actual report i'm going to go back to our workspace and go back to the report regional report we'll go ahead and pin this one also pin to dashboard there's our dashboard pin live now i'm also going to pin something from an excel workbook although the way it works is clunky so i don't usually do this but you never know when you have something in an excel workbook that you want to share i'm going to highlight and this one's way over here we see pin and it will pin this selected range we're going to put it right in the colorado reports dashboard and pin now we can go look at our dashboard so this is working here we have this one it's working also and down here we have a picture of our report and chart we created over in excel now if you click this it jumps over to the excel workbook so that part of it seems clunky to me we're going to go back over to our dashboard scroll down and the little options here i'm going to say delete tile now the advantage of the dashboard is that of course you can take various things from various reports and workbooks and then you have some options up here you can save a copy that saves a copy of the dashboard print it you can share it i'm going to uncheck allow recipients to build content with us send an email notification and up here i'm going to put an address and then grant access now i'm on my cell phone and i'm looking at my email there's the invite i'm going to click it open this dashboard now when you click open this dashboard if this is the first time you're viewing on your phone it'll ask you to download the app and then log in using your organizational email and sure enough i click the dashboard and there it is and i can't really give you a good view but it is interactive i opened this one report and on the phone it is interactive just as if we were in power bi desktop or the web version of power bi online so i'm filtering and then unfiltering that is why power bi online is so wonderful for sharing now one last thing i use outlook online all the time and up in the left hand corner there's an app tile you can go to online excel but this is how i usually get to power bi online all right so that was a lot of fun with power bi online and power bi desktop now that we have two data models stored at power bi online next we want to see how to connect to our online data models in excel and power bi desktop i've opened a new excel file saved it with this name to our folder i named the sheet and i'm logged in and you are not going to believe how cool this is i get to go to insert tables pivot table drop down and there it is or i can use the keyboard remember alt n v that opens the drop down and there it is b is for power bi online so tap b and look at this it opens up a task pane called power bi data sets now i'm the administrator and have all sorts of different workspaces over there so i have access to all sorts of data sets but if you didn't see the data set right at the top you simply type 04 dash m365 and enter and there it is right at the top this one is from our power bi desktop file and this one's from the excel file i'm going to use the excel data model and click insert pivot table and here's our pivot table field list and this is the data model that we're connected to online these tables were not imported this is only a connection to that one source of truth sitting online at power bi online if you go look in the data model there's nothing there and this is nice we have the measures at the top the three dimension tables and that one loan field from the fact table is listed down here now i'm going to select year region sales rep year went to column so i'm going to drag it above rows and then check check check and there i have created a data model pivot table with that lovely number formatting automatically coming from the dax formulas one other note these were explicit measures that means we authored these measures as we mentioned earlier if you create implicit measures either in the excel file or the power bi desktop file those measures will not show up when you connect to this live data model data set from power bi online now i'm going to control s and we're going to jump over to a new power bi desktop file i opened a new file saved it with this name and the data source here we go to get data and there it is power bi data sets and notice it says create a live connection to data sets in power bi service so i click i see the one i want right at the top so i'm going to double click and over here in report view we see all of our tables including f transactions with one field and three measures now i'm going to hit pause and create a new page you can create any page you want and then i created this dashboard click and it's all filtered click again unfiltered and if we go to model it shows us a picture of the model but that data model is not really here well we have model and report well there is no data tab because the data isn't here it is still stored in the power bi online website so that's a pretty cool use for power bi online store a data model online and access it from anywhere now we're back in our original excel file we're on the sheet refresh eight and nine and this is a map of our data connections for example eight and nine here's the file this file has the source data the excel tables that we then imported into this file then we built a data model and from power bi desktop imported it into this file then when we publish both of these files to power bi online those data models were stored online and then we connected both of these files to the data models online that means if we go add some data to this file and refresh all the way through everything will update so we're gonna go open this file here's our file we have one two three four source tables and we have two things to do we're to add new transactions for a new year to the fact table and then we're going to update the date table click in a cell control down arrow scroll down i put new transactions here click in a single cell do not include the field names control asterisks control c and below the table we control v instantly those records are incorporated into this table object control home now the date table has to have a unique list in this first column of every single possible date from the fact table well right now if i control down arrow this table only goes to the end of 2021 now i'm going to show you a killer trick here when you have your date table in the excel worksheet now these are actual dates but look up in the formula bar month text and year these are all formulas this is an excel table so if i can just quickly add the whole new year of dates everything will automatically be copied down now here's a great trick you select the last cell with the last date there's the fill handle hover your cursor and when you see your crosshair cursor you're not going to click you're going to right click so here we go right click drag one cell down back and when you let go you get a secret drop down let go and there it is down at the bottom series now there's other ways to get series but this is the fastest i'm going to click and whatever you do don't leave it rows because that'll shoot the dates to the right we want to shoot them down the columns step value is one day at a time and all we have to do is put the end date 12 slash 31 slash 2022 and when i click ok that is a thing of beauty that's using a series trick formulas and an excel table and that's it now we have to save this control s two of our tables have been updated i'm gonna close this file now in our excel file we go over to the worksheet rdm dax the data was imported by power query all of this is coming from the data model so all we have to do is right click refresh and there we go there's the complete new year and all the amounts have updated in our power bi desktop file we go up to home queries and click refresh it's refreshing and there's the new totals now i had to adjust the font size but that is looking good let's go over to sales report wow look at the trend and for some reason the summer time june and july wasn't nothing but now that is a huge jump control s and now we click publish to send the new version to power bi online select our workspace select it's polite it's asking if we want to replace and we do we need to republish our excel file file publish be sure and select the workspace and upload i definitely want to replace and then the beautiful thing is over in power bi online when i go look at our dashboard look at that it is updated so is this now when we open up our power bi desktop file that was connected live to power bi online it's already updated in our excel file that's connecting to power bi online we have to right click refresh and there's our new data control s control s all right so example eight and nine we learned all sorts of things how to use power query to import data how to build a data model import a data model from excel into power bi desktop upload to power bi online and even connect to power bi online data sets now our next example we're going to use power bi desktop but we're going to connect to an online database with 7 million rows let's go over to the sheet 10. in this example we're going to use power bi desktop and power query to import 7 million rows of sales data that's a lot of data now for some of you in this class you're like we never get that much data well you do get that much data for big companies seven million rows is not even big and then we're going to build an interactive gross profit dashboard now we're going to go over to this next sheet and talk about what we're going to find when we import the data from a structured query language that's sql database we're going to have a fact table with date product the website where the product was sold the country code where the product was sold how many units were sold for the transaction what the revenue discount was so for this transaction right here for every one dollar the customer got 35.3 pennies off for every one dollar and then there's net standard cost in this case for every one dollar of cost from the lookup table the charge is 1.03 so a dollar three that means the price was increased by three percent here the price was the same down here 0.99 that means there is a one percent discount now it's important to understand how these columns are working because we're going to have to calculate revenue a synonym for sales and cost for each transaction the next table uh dimension table there's the what's called the primary key country code that will be hooked up with this foreign key this is the one to many side and then this is the product table with retail price standard cost and the category for the product primary key foreign key this is the many side this is the one side we'll have to look up and use both the price that's for selling it to the customer and the cost that's for the business on the expense side the cost side how they calculate expenses now this table is 22 rows this is 126 but this is 7.7 million rows now let's go talk about the columnar database because when you have 7 million rows that columnar database makes the impossible because you could never have 7 million rows in an excel worksheet it makes the impossible possible now just as a small example this table right here has 606 rows it has four columns that means the total number of cells or bits of data is 2 424 but what a columnar database does is it takes each column and stores them as individual columns but only stores a unique list so product id is going to have 7.7 million rows but when it gets stored in the columnar database they're only going to store the unique items now there's also a behind-the-scenes map that determines how many rows there are that it can use to reconstruct when it makes calculations but on the storage side our table over here that originally had over 2000 rows when we count all these unique lists and add them up there's going to be 621 bits of data or cells in the columnar database now this is only with a table that has 600 rows so you can imagine with 7 million rows that columnar database is going to compress and store it efficiently now let's go over to the next sheet this is a diagram that's going to allow us to calculate the sales for a transaction we're going to have to use the product dimension or lookup table and we're going to have to use the fact table so for this yellow transaction here there were four units sold the revenue discount they only got 1.9 pennies discount for every one dollar so that's why in the formula you take the one dollar minus the discount then in our formula we're going to have to look up based on this foreign key on the many side product name we're going to have to look up in the dimension or lookup table the aspen product and then bring the price back to the formula and that's how we'll calculate sales for every line in our table you can look through this if you want and this will be our formula since we're going to be doing it in the data model we're going to use dax formulas now round we know how to do related is like x lookup x lookup is more complicated when you do related to look up a product price because there's a relationship you just have to say what you want that's it and then we multiply by well one and that's the name of the column that has the revenue discount so that's really this part and then times there's the name of the column for quantity and of course over here d product retail sales that's this field from the dimension or lookup table now the last thing is credentials we're going to be logging on to an sql database and pulling the data from online and the great thing about storing the data online is it's not on premise we don't have to worry about those text files or excel files and what the file path is power query is just always going to be connected to that online database which isn't going to move but in order to get access we're going to have to use these credentials server database username and password all right now we're going to open up a new power bi desktop file our goal will be to create these seven measures this gross profit page and using a map visual will plot gross profit across the world all right we're going to start our sql power bi desktop adventure by opening a new file saving it with a good name and step one is to get the data so we go up to home data we could go to get data sql server but there's a big button right here so i'm clicking this sql server database we have to enter our credentials the server is pawn dot highline dot edu tab and the database is lowercase boom data we're going to do a direct import bring the data into here let's click ok be sure to say database on the left the username is excel is fun all lower case and this is where most people get in trouble you gotta spell this right with correct capitalization capital e x c e l that's excel capital i s is capital f u n and an explanation point we definitely want to connect just to server and database now we're going to click connect this is not encrypted so we click ok and just as in excel we get the navigator window and we get to pick amongst various objects do not import the d calendar table it doesn't have the right dates we want d country d product and f transactions we do not click load we want to click transform data now there's three queries on the left f transaction is selected there's our preview the name is perfect and we have these two steps now we already reviewed these columns and the data type should be perfect because this is coming from an sql database but we still have to check and sure enough revenue discount is not correct so we're going to add a step click abc and we definitely want decimal number as our data type it added that step we'll scroll over and when you import data from a relational database which just means there's relationships these extra columns come in with data from the table on the other side of the relationship we don't need any of these so i'm going to click the first one hold shift click the last one and right click remove there's a new step the data types are right this table is looking good now we click d product data types are all good and this is a column with every single transaction from the f transaction table for that product the relationship is what brings that table in but we don't need this column so right click remove the name and the steps are fine d country looking good right click remove those are good now we're going to click close to close this and apply to send this to the data model but when we click it's busy working behind the scenes building that columnar database so all these millions of records will be compressed into a much smaller space and there it is done now let's go look at data and on the right there's our tables d country d product and f transactions if we go over to model it already put the relationships in there's our f transaction d country from the one to many side our product table we have a unique list of products from the one to many side now what we're going to need next is a date table and a date table must contain a unique list of dates for every possible year from the date field in the fact table if we go look at our tables d country does not have a date column d product doesn't either and in the f transaction table we have one single day column and when you have only one date column in the fact table and there's no other date columns in any of the other tables that will allow us to create our date table in a much easier way now we'll create the date table in just a moment but this is our fact table and we use power query to import it into the data model but i want you to notice that when you're in data view and you select a column on the preview for this table we're allowed to change the format now this is the same over in powerpivot also i want to show this date as a short date i don't want to see the day name also notice that over here in the preview in data view you can actually change the data type on a given column now when we import using power query we don't want to set our data types here in the data model we want to do it with power query but in just a moment when we create our date table we'll have to use this feature to make sure the data type is correct now let's create our date table using dax formulas and the way we do it is we can go over to table tools and this is different than power pivot power pivot we can create measures and calculated columns but we can't create tables here in power bi desktop we're allowed to create dax tables and they'll be loaded to the data model so let's create a new dax formula to create a new table up in the formula bar we type just like over in powerpivot name assignment operator formula the name for us is going to be d date and luckily the assignment operator is just equal sign instead of powerpivot where we had to do colon equal so equals and there's two calendar functions calendar is what you use if you have multiple date columns and then you just specify the start and end and it creates a unique list but when we only have one date field in the fact table we can use calendar auto if you had a fiscal year you'd put it in but we don't so you just don't put anything in and when i hit enter it hunts through the data model finds the min and max date and gives you a unique list for every day for every year it found in the date column in the fact table now we select the date field the automatic field name is date we have to set the data type over here in the data model because we didn't bring this in using power query we definitely want this as a date and i'm going to format it as short date now if we look over on the right we now have a new table and it's in the data model let's open we have one field now when you have a date table you have this unique list but then you can add as many attribute fields as you would like if we want year and month in our reports and visuals we have to add those to the date table you can also add lots of other date attributes like fiscal year fiscal quarter week and many more now we want to go back to table tools because this button right here is really important for date tables we saw this over in power pivot when we mark a table as a date table then the automatic date table creation feature is turned off now we saw in powerpivot that if we dragged a date field into a pivot table it added all these columns to the fact table and we definitely don't want that but over here in power bi desktop it's even worse if you drag a date field into a visual and you don't have your table marked as date every time you do it it creates a new hidden date table and you can't go and look at them so i'm going to make sure and mark this as a date table and then tell it where the primary key or the unique list is date okay now we can add our extra fields or columns we're going to say new column up in the formula bar we definitely need month number equal sign and the function is the same as over in excel month and we want to access using row context the date in each row so i say dd down arrow there's the date field tab close parentheses and enter now this is the first time we've created what is called a dax calculated column this over here is not a dax calculated column this was a one column table but this is a dax calculated column we can see the formula in the formula bar and dax calculated columns calculate differently than excel worksheet formulas and different than excel table formulas notice up here that's the complete column reference if we had a column in the worksheet and used worksheet formulas how would we access each row we'd have to use a relative cell reference if we were in a table column we wouldn't have the full column reference we'd have the field name in square brackets with an at symbol but over here in dax calculated columns and also later we'll see dax iterator functions they both use a complete column reference and then behind the scenes it's calculated using row context and all row context means for a full column like this is it can see each item in each row so from now on when we create dax calculated columns or later iterator functions we'll just put in the whole column reference and row context will get the item in each row now for each new column we create we have to consider data types over in column tools i'm going to check the data type now that's fine but it is a number if we were to drag this into one of the reports it would try to add them but this is one of the fields we're going to hide so back in table tools new column this is going to be month and whereas over in excel you use the text function to create a month name or other possibilities like day name but over here in dax they decided not to call it text they call it format and so what format would do we put a value in dd down arrow tab to get the full date column and i want to format each one so i type a comma there's the format argument and then in double quotes m means month d means day and y means year now watch what happens and it has to be in double quotes but i'm going to put 1 2 3 4 m's for month n double quote close parentheses and enter that will give us the full name of the month but that's not what i want i'm in a backspace and only put three m's that will put an abbreviation three letters now over in column tools we need to check the data type text is fine also just as in powerpivot this would sort alphabetically and watch this i'm going to go over to report click in the white and all i'm going to do is check month and when you do that it throws it into a table sure enough that's alphabetically so we come back over to data and we have to tell month to be sorted by month number so we come over to sort by column and we definitely want to say sort that by month number you don't see anything here but over in report view that's sorted chronologically exactly what we want back to data now we can add our next date attribute field year we'll click new column up in the formula bar the name of this dax calculated column will be year and the function is the same as over in excel year d d down arrow to get the full column tab and when i close parentheses and hit enter row context sees the date in each row and gives us the year now data type whole number if we accidentally drag this to the wrong place an implicit measure would be created to add year which isn't what we want we could add text data type then it would be aligned to the left but better than that we can come up to properties and say summarization i do not want you to summarize so that means the whole number is fine now we have our date table now over on the right there's our completed date table notice the icon for dax calculated columns f x with a table now we go over to model view we grab our d date table we'll bring it over here and then we're going to take date drag it over to date in the fact table we have the one that's the unique list over to the many because we hope we have many sales for every one day the next task is to hide columns that we don't want to appear over in report view month number is only a helper column for month so right click hide in report view notice that's different than over in excel powerpivot over there it's hide from client tool in the fact table we don't want any of the foreign keys country code date and product we also don't want any of the number fields although we're going to use all three in formulas so i'm still holding control and we're going to hide all of those remember if we didn't hide those and left them in report view someone might drag and drop and create an implicit measure right click hide in report view country code right click hide now we're definitely going to use retail price and standard cost and formulas but we do not want them in report view select right click hide in report view we're going to use both of these over in report view so now we've imported the data using power query created the relationships created a date table with a dax table function and then dax calculated columns and then hid the fields we don't want in report view now we're going to create our dax one dax calculated column on the fact table and then a bunch of dax measures let's go over to data select the f transactions table now here's our fact table and when we imported this table it did not come with a field that shows us the amount of revenue or sales for each line or row in this table we don't have a field for cost of goods sold expense either no problem we can create both of those and in fact we already saw the formula for calculating revenue before we started the project now i'm going to show you two ways to calculate the revenue column one is the two-step method the other is absolutely amazing and makes dax unbelievable is the one-step method now i'm going to show you the two-step method first because it will help us understand how the formula is working in each row and then i'm going to show you the one step method now to calculate revenue notice we have quantity that's how many of the product they bought and we have their discount but we don't have the price well the price is over in the d product table retail price is used to calculate revenue and standard cost is used to calculate cost of goods sold which is how much each product cost to make no problem we're going to go over to f transactions and because there's a relationship between those two tables and the product is listed in every row in the fact table instead of using x lookup we just use the dax lookup function related now we definitely want a new column and we're going to call this line because we're going to have two revenue formulas one totaling it up and this one is to calculate the line or row revenue and i'm going to indicate the dollar sign equal sign and we're going to build this formula one part at a time right now all i want to do is look up the price for each row in this table let's use related and notice all it says is give me the column name if i put d product and retail price here because there's a relationship this lookup function knows whenever it sees by row context the product to just get the right price close parentheses and when i hit enter row context gives us the price in each row now this table is so big if you try to scroll down and find a different product you're not going to do it but remember the magic of filters we learned at the beginning of this video if you click it'll give you a unique list and sure enough this field or column has that many different prices i'm going to click cancel now the next thing we need if we have the price is the quantity or the number of units purchased so up in the formula bar times and we actually just can put the full column reference so it's from f transaction and there's quantity again this is the full column but row context will pull the quantity out from each row when i hit enter that's the amount of the revenue without the discount now they give our discount in terms of number of pennies for every one dollar so we want the amount they pay so times in parentheses there's the one dollar and please subtract whatever the discount is f transaction and then revenue discount close parentheses and this is the formula enter that gives us the line revenue after the discount now down below there are some definite decimals that we need to round so before related we use round we learned round in an earlier video at the end comma 2 because we're going to the penny close parentheses and enter and that is our dax calculated column formula now if we look over at the f transaction there's the calculated column now when we created the calculated column line revenue we can see it up here here's the column that's step one now we need to create a measure to add this entire column so we can use that measure over in report view now there's a few places that you can create measures if you already are somewhere in the table where you want the measure to appear you can come over to table tools new measure but since you always have to make sure that you have the right table selected i got in the habit of because i usually have to come over and select the table and then create the measure i just click on the table and right click new measure is right at the top up in the formula bar we're going to call this measure total revenue and i'm actually going to add the function name sum to this because in this measure we're going to use the sum function in our one step method later we'll use just total revenue and that's the form that we'll use over in report view but inside of sum ft that's the f transaction table and guess what i get to use my calculated column so two steps create calculated column and then create measure to aggregate when i hit enter this measure will add this whole field we also could have used other aggregate functions like average min or max now i do want to add some formatting to this measure so when we drop it in the visual it has the right formatting now in power bi desktop in data or table view we cannot see the result of the measure we see the measure down here so what i usually do is i come over to report view we don't need this anymore so i'm going to delete i'm going to select a table and then resize it as i create each measure i'm going to show it here in the report area to make sure it's calculating the right result and i'm going to add one field so that way i can see how the measure is working across the fields and it will calculate the total at the bottom i'm going to check total revenue and bam there it is the two-step method we created the dax calculated column the measure and there's all the revenue for the product with a grand total at the bottom if we were over in power pivot right we would see the grand total in the measure grid but over here in power bi desktop when you are in data or table view we don't have a measure grid so that was the two-step method now we get to see the amazing one-step method now remember what we did with the two-step method here's the f transaction table and we attached to the side of the f transaction table a calculated column then we created a second formula a measure to add well in the one step method we're going to use an amazing iterator function that simulates row by row calculation called sum x and in the first argument of sum x you just say hey which table would you have put the calculated column so the first argument will have f transactions and then you put the exact same formula and i'm going to copy this in the second argument of sum x and then sum x does both steps inside the formula it'll actually calculate all 7 million values as if we had a calculated column that's the x part the iterator part and then the sum part adds all seven million rows to get total revenue now i'm going to hit enter come over to f transaction right click new measure total revenue equal sign the sum part will add the x part will iterate and look at the arguments all it wants the table to iterate down and whatever formula you would have put into a calculated column f transaction that's the table comma expression is a synonym for formula in this case a calculated column control v when i close parentheses enter add some formatting come over to report view the table is selected i check total revenue and you gotta be kidding me the exact same thing but with the one step method we did not have to add that calculated column now the difference between these two methods let's go back to data or table view with the two step method when you create a calculated column these values are stored in the columnar database that means it will take more storage space in the columnar database but these values are only calculated when you create the column or when you refresh the table each individual value is not calculated over in report view when we drag and drop the sum measure into reports or visuals with the one step method you don't have to store anything in the columnar database so it doesn't require extra storage space but every single value in this measure right here that's this entire 7 million row column all those values are calculated inside the formula every time you drop it into a visual or report or when you change the conditions or criteria so which method should you use well it's really up to preference as the great dax masters alberto ferrari and marco russo say a hundred million rows or less and there's not much performance difference now the rule i use is this in report view now i'm going to uncheck both measures and performance just means how fast does the measure calculate and get the answer so we're going to add both measures and watch to see is one faster than the other so i'm going to check the two-step method first so check well that was fast now let's do the one step method well that was fast they look exactly the same and my rule is this if i start dragging and dropping a one step method and it's calculating too slowly that means there's some calculation intensive steps in the formula and i might want to move it over to a column what that would do is allow it to calculate one time when we refresh or create the column and then it wouldn't have to recalculate each time we use it in report u but again if you only have seven million rows like we have it's probably not going to matter which method you use now there are ways to measure speed there's a method inside of power bi desktop and there's also a program called dax studio with those tools you can measure performance but we're not gonna need to do that in this class but again most of the time it doesn't matter now i'm gonna drag this over here and i'm going to name this page test calculations and now we have four more measures to create now you can create measures in table view or report view the advantage of doing it in report view with an extra sheet like this is as soon as you calculate it you can see the result so we'll pretend that this is our measure grid like over in power pivot now i'm going to go over to f transactions right click new measure up in the formula bar we're going to create the total cost of goods sold one step measure this measure here is going to be similar to our total revenue we need to calculate every line item cost of goods sold in the fact table and then add that means we use sum x the table of course is our fact table f transactions comma and expression is the formula we would use in a calculated column now we're going to have to round each amount we're going to have to look up the standard cost from the d product table and then from the f transaction table whatever the quantity total units is and the net cost equivalent so i'm going to type round to get the price we're going to use related but wait a second i thought related had to see each row in the fact table well when you're using sum x remember this is exactly like a calculated column so when we put d product standard cost close parentheses that related we'll see every single row in f transaction of course it will see the product in every single row and through the relationship it'll go and get the standard cost so we have our cost times f transaction quantity times f transaction net standard cost now this is not the discount when you have a discount you have to do one minus when you have net cost they already made that one minus or plus calculation for you so in the formula we just use that column now that's the formula for round now we can look at the screen tip to help us because we're a couple functions deep comma number of digits to round to 2. now watch when i close parentheses round is completed that's the expression or the calculated column now i have to close off some x when i hit enter add some formatting and come over and check total cost of goods sold bam there's our total cost of goods sold for each product and the grand total at the bottom now our fourth measure we want to calculate is a metric called gross profit and gross profit is revenue minus cost of goods sold manufacturers and retailers like this metric because it takes the total revenue minus the total cost for the product cost of goods sold does not include all the other expenses in the business it's revenue minus the single expense cost of goods sold so for example this is a boomerang manufacturing company there's a gel fast there's wood paint sandpaper machine maintenance packaging instructions and maybe some other things also all of those together make up the single cost of goods sold expense so when we subtract these two things it gives us gross profit sometimes called contribution margin that means the amount left over to cover other expenses and profit so we right click f transaction new measure we'll call it gross profit and wait a second we get to take one measure already created minus another now watch this we're not going to include the table name square brackets and the measure that convention is reserved only for columns when we type a square bracket the dax engine knows the convention is to use square brackets only for measures so we can down arrow to get total revenue tab minus square bracket i see total cost of goods sold so tab that's our formula enter i better add enter some number formatting now i'm going to come over and check gross profit and so for the alpine product the gross profit was 32 million now an even better metric is to take gross profit as a numerator total revenue as the denominator and then that's called gross profit percentage that tells you how many pennies for every one dollar are left over for gross profit which is all other expenses and profit so right click new measure and instead of using the division operator in dax we can use the divide function it needs a numerator so square brackets and that's going to be gross profit comma it needs a denominator square bracket the measure total revenue now we could comma and put alternative result that's what you put in the cell if there's a divide by zero error if you leave this omitted meaning just close parentheses then if there is a divide by zero error in the report it'll look like an empty cell close parentheses enter now this is going to be a percentage so we'll add percent number formatting i want two decimals i could come over and check and so crested butte has 37 percent gross profit so for every one dollar 37 pennies are left over for gross profit whereas carlotta doublers much higher gross profit percentage now the last measure we need is total units and we have a quantity column in our fact table so right click new measure total units no dollar sign because this is counting some f transaction and down to quantity that's the number of units sold for each transaction close parentheses enter and i'm going to do comma we definitely want zero decimals because this is counting we come over and check total units and there's our last measure and now let's go over to model to admire our handiwork f transactions there's a bunch of measures we have our hidden columns our one column where we might pull into a report the dimension tables calculated table calculated columns and hidden fields now often time the hard work is creating the data model but getting this correct makes all that visualizing and reporting and sharing easy and effective now we can go over to report view we're going to add a new sheet gross profit report i'm going to click in the white we want a line chart we'll put this down at the bottom we'll put year in the x month below year and we want three lines in our chart we definitely want to see total revenue total cost of goods sold and percent gross profit now anytime you have a measure like this this is a teeny number between 1 and 0. these are huge million or billion dollar numbers so when that happens we come over to the measure in the y axis click the drop down move to secondary y-axis we could see the label here and the two labels over here we definitely want to bring this down two levels and show year and month over in format x-axis we don't want to concatenate that's looking much better in general i don't want to see a title that label this label everything's looking good really a chart like this is a lot easier in power bi desktop than it is over in excel now i hit pause and created this second visual a stacked bar with category in the y-axis and product in the legend and gross profit now we finish this gross profit page and if we look at this line chart we can notice some patterns it looks like we have a seasonal pattern christmas time christmas time christmas time is always up and over time the christmas sales are getting bigger and bigger we can also see the pattern and this is probably not very good and this is the gross profit percentage for all products together but over the years the trend is down so from about 44 down to 40. that means over the four years if gross profit percentage goes down they have less left over to cover other expenses and products now if we come up here if we want to look at how this chart changes by beginner we can competition it looks like different specifics but the overall pattern is still the same the seasonal is christmas and it looks to be going up over time and gross profit percentage down over time now we can unfilter we want to create one more page we're going to call this page gpmap enter we click in the white and we're going to see this amazing visual a world map and it'll show the whole world now oftentimes data sets will have longitude and latitude we don't have that here but it does understand descriptions or names like city state or country we have sales all over the world and we have country so if we drag that down to location immediately those dots show up they're all the same size but we want to look at gross profit so we'll drag this to bubble size and instantly we could see the bubbles all have different sizes now the funny thing is you would think that australia would have a huge bubble but it doesn't it looks like the united states has a big one over here this is europe if you hover germany looks like it has a big one japan too and france has a big one and in fact it's august 20th when i'm filming this video and right now over in france there is a 10-day world championship of boomerang throwing usa has a couple of teams certainly france germany japan and brazil so there's our map for comparing a particular metric all right so we did one page with a map we did a gross profit report we had one page where we tested all of our measures and in model there's our data model now we're not going to publish this and share this and in fact the main thing we learned in this example is how to import big data and build a data model over here in power bi desktop now that was the most epic video i have ever made three and a half hours your one-stop shopping for excel and power bi data analysis we covered all of these topics so you can become awesome and have fun with efficiency using excel and power bi all right if you like that video be sure to click that thumbs up leave a comment and subscribe because there's always lots more videos that come from excel is fun all right we'll see you next video [Music] you

Channel: ExcelIsFun

Views: 138,518

Keywords: Highline College, Busn 218, Mike Girvin, excelisfun, Microsoft 365 Excel, Free Data Analysis Class, Data Analysis Online Course, PivotTable, Power Query, Power Pivot, Power BI, Power BI Desktop, DAX Measures, DAX Formulas, M Code, Power Query Formulas, Sort, Filter, FlashFill, Learn Data Analysis, Data Science, YouTube Data Analysis, Business Intelligence, Data Analytics, Excel MVP, XLOOKUP, SUMX, RELATED, COUNTROWS, Relationships, Data Model, Data Modeling in Power BI

Id: TjSnQ4VDHTE

Length: 224min 8sec (13448 seconds)

Published: Sun Aug 21 2022