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

you need to learn everything about Excel

data

analysis

. You came to the right

video

. Video number four in Microsoft 365. Excel. The

complete

history. This is going to be a crazy

video

. You won't believe it. All these tools are taught in a single video. that's right, sort filter flash fill

power

query pivot tables

power

pivot and even power bi that means this is a video to get an introduction to each of these tools now mex video number four is three hours and 43 minutes, which means that there is everything in is 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 it's only one hour and at the bottom is also this table of contents from the visual time hyperlink. of course, every video in this class has pdf notes, you gotta be kidding me 32 pages, if we click on example one, we'll start by learning some cool tricks about the sort filter and flash fill number four. See one of the best uses for a pivot table that summarizes survey results. five we'll look at 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 just click refresh and everything will refresh.

Example 6: We will have 12 different tables, but Power Query will have no problem converting it to a single table and calculating the sales for us, and we will not load this into the Excel spreadsheet that we will load. directly to a pivot table and create a report and chart, example seven you will have to look at the x lookup function because sometimes the worksheet formulas help you get to the final pivot table and chart, in example Eight, we'll get an introduction to the powerpivot and dax formulas. Because we have a bunch of different tables working together to create these reports, once we create our

data

model and PowerPivot, we will automatically send it to Power Bi Desktop to get this amazing interactive dashboard and the data model and Power Bi Desktop will be looks almost exactly the same as powerpivot, then in our final example we will import seven million rows of data from a SQL database and create two dashboard reports and this is our final data model with four different dax measures.

Here is the download link at Highline College. website, this link is also below this youtube video, right click save link, since here is the folder on the desktop that we have been using for our other files, that zip folder, click save , there is the folder path, right click extract all, click extract and then left. I'm going to open it, select it on the left and there are all the files that we are going to use in this video. Yes, we have 10 amazing examples and since this is data

analysis

, now we have many files under the YouTube video.

There is this list of the 10 examples with a time hyperlinked table of contents so you can jump to the example you want now about data analysis. Let's remember that what we learned in video number two, data analysis, is when we convert raw data into useful information to obtain information and make decisions. There are many synonyms of data analysis analysis business intelligence data science data analysis in each example we will begin with data data in information to make decisions or data in information to obtain information and do not forget almost all of the tools we use require an adequate data set.

Now let's sort out the filter and flash fill. Now the amazing thing about sorting and filtering and this is an Excel table is these drop down menus in this user interface for sorting and filtering. Same thing across all the tools, this is an Excel table, pivot tables, Power Query, Power Pivot and Power Bi Desktop, they all use the same conventions that we have here in the Excel spreadsheet. Now sorting means arranging records in a table from least to greatest or from a to z or ascending or the other option from greatest to least z to a or descending can also sort by fill color and font color.

Now the most important thing about sorting is that if we decide to sort on this date column, all the records will remain. intact, that means this field here will be sorted, we will do a to z, but the sort in this column determines the sort of all the records, so if I open up and say a to z, oldest to oldest new, that record remains intact just like all other records control z now we sort by one column, but you can definitely sort by more than one column. Let's order the sales from highest to lowest. Each record is sorted from highest to lowest, but notice what happens when I come and sort the region from A to Z.

It's a second sword, this is beautiful because this field was already sorted when I sort the entire east together, it is listed from highest to lowest and when I get to the next category, from highest to lowest I control z z now, if you want to sort a column or a table that is not an

excel

table in the data ribbon tab, sort and filter the group to sort, there are buttons and a dialog and then some filtering options, now sort without an

excel

table, you can do it, it must have a field name at the top, yes, We know you can highlight and force sort by highlighting something in general it's always better to have a field name at the top then we can just click the button and we know what to do also if this is not bold and the field name is text and these are all the text notice what What happens, the field name is sorted with the z record control.

That's why you always hear me say in every video I've ever done 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. field. Now when I do this it works fine. Control z. Now, most of the time when we do data analysis and in particular when we use Power Query, we will have a method. to ensure the data type in each column is consistent, but if you have mixed data in the worksheet and are trying to sort from a to z, this is the order the data types will be sorted, as an example here there is a really terrible field or column of data that includes empty cells now, instead of using the buttons to sort, we can also right click, there are filter and sort options now 8.

It's easy to point to some other options here, but it what we want are numbers a to z at the top of the text true false errors and notice that empty cells are always sorted down and will be sorted down whether you have a to z or a z for a control z now let's look at filter by filter means show only certain records based on one or more conditions for one or more columns and you can use any logical test and logical test or logical test between and many more and while sorting organizes the records but keeps all the records shown when you filter, It will show you only the records that match the criteria. and hide the rest so that the filtering allows us to set a condition and get only the records that we are interested in, but before we show you the filtering, there is a particularly useful use for filters and guess what this filter function will be in each tool, now Imagine We just received this data set and you say: I don't know how many product IDs there are.

When you click on the filter dropdown in each tool, it will show you a single list, so I instantly know there are seven IDs in this. column region there are six, even in a column like this with tons of values, there is now a single list in Power Query for example, we will use the filter to get only the records we want before importing, but when it is in the sheet Excel calculation. One of the best uses of the filter function is to simply pull the records you're interested in, so imagine your boss just texts you and says, "Hey, I need the western records." You just pop up the filter, uncheck it, select all, click West, click OK, and there they are. your records now you can see they are hidden because the row header numbers are blue and there are hidden rows and look what happens when we highlight and copy control c the dancing ants are having a big party now those dancing ants are just visibly dancing cells , those hidden records are not copied now, you would open a new workbook, paste it, and then email it to your boss.

For now, we are going to go to control v west and there we have extracted the records for the west region. over here we can burst this dancing ant party by clicking on the escape key now you can override the filter by clicking on the filter clear also in sort and filter there is a clear clear now the most powerful aspect of filtering is the data type specific filtering when you click a dropdown on a date you know it's a date and if we point out date filters you must be kidding, there are all kinds of date specific filters, escape for text, text filters, all kinds of text filters important when we get to power query and we're filtering excel files, we could say we only want files that end with dot xlsx and for numbers there are all kinds of number specific filters including greater than or equal to some obstacle or this top 10 or you can change it.

To see the top five sales, use this filter now. Another important thing about the filter is that we can use any type of logical test and we have a video, a couple of videos later, that talks all about the logical test, but if I select west from the region column, that is filtering on a column as soon as I jump to a second column and say for example Chantel and I notice that the single list is based on that filtered column but when I select two conditions in two different columns this is a logical test which means the record. only included if region equals west and sales rep equals chantel.

I'm going to clarify this now, it's two columns, two conditions, if you use one column instead and I say northwest and in English we say and west, but this is not a logical test. This is a logical test when you select multiple items from a column. It's a logical test when I click OK as we will learn some videos later. What is happening is that I am saying that the region is equal to west or equal to northwest and we will use both logical tests for the rest of the class. I'm going to remove the filter now to flash fill now flash fill data data tools ribbon tab there is the flash fill button this is a simple one time data cleanup tool, if you have a consistent pattern you can clean the data by typing an example to the right of the data column and then clicking quick fill or control.

Now let's scroll down and look at an example. Now we have a column of data here and our task is simply to get the name in the right column now before using flash fill you need to make sure of three things: the first is that this is a one-time task and we will never have to repeat it the second The thing is that we will not get more data later and we will need to update if that is the case, it uses formulas or power queries and the most important thing is that it should recognize a simple pattern and this column of data has a simple and easy pattern.

I want everything later. The first space now, that's how I do it. I generally copy and paste to avoid making a typing error check. I'm going to paste this and click on the cell below and you click flash fill or control. Can't. Tell you how often I use this tool, but not as part of an elaborate data analysis project. I just want to have a single task with a simple pattern. This was all after the first space here. I want everything after the last space. type west enter control e and that's awesome now you have to be careful sometimes flash fill really messes things up and it's because you can't see the pattern if I come here and type zero five bars zero five bars 2016 when I press enter and control e that's just rubbish, you can't see the pattern.

Now I have a lot of videos on youtube about flash fill and how to do special and complicated things to extract a date like this with flash fill, but it's not worth it most of the time if I can't immediately see a simple pattern, in this case I would use Power Query and would get the text between these two delimiters correctly, so in this sheet we saw the sort filter and quick fill. Now let's go to the pt sheet and look at One of the best examples of why the pivot table tool is so amazing. In video number two, we learned the basics of pivot tables and our goal here is to simply take this column of survey data and get a summary with the count and percentage. answers now, whenever you have a data analysis project, the question is which of the many amazing tools to use now, in this case we know that we are going to use the standard pivot table and the reasons are that we already have the data in Excel, so it is notYou need to import it with Power Query.

We don't need to clean it up 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 the down arrow, we only have about 160 control records at home and the calculations we need to do are simple, all we need to do is count and make a percentage of the total. The standard pivot table calculation capabilities 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 on a single cell in the appropriate data set, go up to insert the table group, and click on the pivot table drop-down menu.

Now we have four options that allow us to get tables from the table or range, that is whether to have a table in the worksheet from an external data source, that is, if we have a table stored as a connection only in Power Query from the model of data, that is, if we have a table or a data model stored in Power Pivot and Power Bi, that is when we have a table or a data model stored in power bi online, we will use several of these different data sources throughout throughout the class now, because we're going to be doing so many pivot tables in this class, we're going to use the alt keyboard alternative that appears n to insert, I hit n, I see v for pivot table v and we have a worksheet table, like this we press t, there is our range dialog pivot table because this is a proper data set, it always hits what we want in the existing sheet I'm going to put in d4, click OK, now I'm going to take this task pane of pivot table fields and I'll drag it closer and then resize it in the pivot table fields task pane.

We always get a full list of fields at the top we only have one field so I drag the field down to the rows and we instantly get a single list because this is a text field when I drag it to the values ​​which is where we drag the fields when we want to do calculations, now it will be counted by default if you want to change the added calculation, click on the values ​​area, right click on summarize values, you can choose your different calculations now for us, definitely We want to count because we need to count how many answers there are now, summarizing values ​​is a way of doing a calculation backwards.

Is this feature amazing to show values? Now I'm going to click Escape. We're going to use that function to display values ​​in just a second and I want to rename it. Just count and enter. Now we want to do a second calculation that tells us. the percentage of responses, so we drag the survey data back into the values, now we zoom in and right click and what we want to do is make sure that summarize the values ​​by is set to count because as soon as it changes to show the values like whatever you choose here is based on that aggregate calculation and what we want is to take each individual count and divide it by the column total, so that's what we're going to use, so I'm going to click on percentage of column total , I will select the top percentage count, it will enter that means.

In less than 30 seconds we can take the data from this survey and we know what the count and percentage of responses are. Now we will learn a lot more about pivot tables in this video and throughout the class. Alright, we want to move on to the sheet because now we want to talk about Power Query and guess what Power Query is the best Excel tool invented since the pivot table and the reason is that since the 90s the pivot table has been an incredible tool for creating reports and charts, but we have always been missing a tool that can import data into Excel and also correct and clean incorrect data, that's what Power Query does.

Power Query can import data from outside of Excel, such as text files, other Excel files, databases, and many other locations that you can clean up. data, such as splitting the first name and last name into different columns or extracting a date from a datetime, can transform data such as removing unwanted columns, which is one of the things we will do in our next task, calculating new columns, we will do those two examples ahead or combine multiple tables into one table, we will do that two examples ahead and then it will load the data in the Excel spreadsheet directly into the pivot table cache or into the Power Pivot data model, not only that , but Power Query is not just in Excel it's also gone in Power Bi Desktop, so everything you learn about Power Query here in Excel works the same in Power Bi Desktop now in Excel, in the data ribbon tab, get and transform data, queries and connections, that's Power Query, there are a lot. of different connectors that connect us to different data sources and then this will open the queries after we create them just for fun.

Here is a Power Bi Desktop file and data and queries. Those are the two groups that make up Power Query. Here is our entire connection. different data sources this is how we get to the queries that we have already created now we want to go to the pq1 info sheet now in this example we are going to have some data on a website we will need to download it as a csv file and we will talk about what it is then we will import it, transform it and load it into the excel worksheet and in the worksheet we will add formulas and formatting and then when the website refreshes we will simply click on refresh power query.

We're going to re-import, re-clean, transform and update our table now, of course, when we're trying to figure out what data analysis tool to do in a project like this because we're importing clean and transform, of course, we use Power Query now. The data we have on this website is assignment score data for students in a particular class. I actually created this website. This is a fake website. If you click this export csv button, it doesn't work and I did it because the website I normally use. get my grades data, I can't show you that live in public, but this is exactly what it looks like on the canvas system.

Now this is gray data, but it could easily be a website with sales data from a company or economic data from the government or data from a 911 call center and on most websites where you get data, there will now be a button export to csv for our particular rating 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 a bunch of columns to them that I don't need, both with data and with some additional calculations that I don't need now. If this was canvas, I just click this button and it will download the csv file and that's what I import into Excel, so what I did was I created three csv files that will simulate that this is the first website, this is when it updates further late and this is the final update, so although we will not download them from the website, The process remains the same when we get a new file, we need this new data to update our project.

Now what does dot csv mean? It means comma separated values ​​and if we right click on open with and I'm going to open with notepad or notepad or word, I'm going to use word and then click OK, this is what you see, each of the field names, name, id, fourth is separated with a comma and then all the records have a name, comma, id, comma, the fourth, comma, the class name and then a bunch of punctuations now why would a data table be stored like this? Well, it goes back to the beginning of database history, as there were so many different types of systems that we needed a method to get tables of data. from one system to another, so one of the conventions they agreed upon is a delimited table.

Now delimiter means what separates the data bits for us. The delimiter is a comma. The other common type of delimiter is a tab. We'll look at that delimiter in our next example, all text files, whether they're csvs with comma separated values ​​or a text file that's a tab delimiter, all they do is get data from one system to another and when Power Query sees this, it will interpret it as a table. Now, another important note. about dot csv files, this computer is programmed to open dot csv files with Excel, so if I double click on this, it looks like Excel, but it's not that dot csv file extension and the file structure prevents it from doing almost everything in Excel, so keep that in mind. when you see csv, this is not an excel file.

Before moving on to Excel, let's do a little file management. Here is the folder we downloaded and extracted and you need to make sure all of these files are closed before we begin. do our file management, so for example, if you have this open, you need to close it and then go 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 see the folder path we're here in Windows Explorer and if I click on the address bar, that's the full folder path that Power Query will memorize when we import this file.

Now this is called local folder path and the reason is this folder. the path is specific to the computer I'm working on, girvin desktop users which will be unique to my computer, will have a different initial folder path, but the rest folder name of the video 1 folder name of the files we just download It will be the same now if you look in the formula bar when we extract it, it has a duplicate folder name to make the path of this folder a little shorter and easier to read in Power Query. I want to remove that first duplicate folder path now. on the left, this is the folder that we created in video number one, but one two, there is a 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 you want to cut this and paste it into this folder right here, you can 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 that's inside the folder that we downloaded for video number four. Now, if I click here, that will be the folder path.

We will now direct the power query to this and include the file name at the end. Now let's open. Open our Excel file and start the import, so I'll double click. We are on sheet pq1. We go up to the data tab to obtain the transformation. Now we can click directly from the CSV text, but these are all connectors and if you click Get. file data drop down menu, it has different types of files like excel csv xml, there are databases later we will connect to a sql database from azure power platform and many other data sources.

I'm going to click on escape and click on the csv text in the data import dialog box we navigate to our folder there is the folder path I select grade 1 dot csv and then I click on import now do you want to help us import this correctly you want to know what the origin of the file is correct this is the one we always have to check and make sure it's the correct delimiter for us, it did it right and it usually detects the data type. 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. I want to click on transform data, what transform data does is it opens up the power query editor and it gives us the opportunity to clean up the transformation, do whatever we need to do before we import it and then load it, so let's click on transform data and here is the energy query. editor, this is a new window at the top of the Excel window and we can already see that Power Query has done its magic. We have a preview of the appropriate data set. Here on the left is the default name for the query.

Also on the right this is where we can change the name of the query and we definitely want to give this query a good name. I'm going to highlight this and this is for summer business 101 scores and we go in, we change the name of the query, we can see it listed on the left right now. In the Power Query editor we only have one query, but as we import and transform new data into this Excel file, we will see other queries listed here. Now, one of the most surprising things about Power Query is the steps applied, there are now three automatic ones. steps, we can add other steps later using the UI and we will add new steps.

These applied steps are memorized by the power query, so later when we update, all these steps are executed again to update the query. Now notice what happens when I click on source, it gives you a preview of what you did in that step, which means we can see each of the steps, it memorizes what you did, that's really different than the sheetExcel calculation when we do something in Excel spreadsheet and we have a progression like We are building something we can't go back like this so in the first step you got the data in records but notice that the field names are in the first row and the column headers, column one, column two, are default field names when I select the The next step I preview, sure enough, promoted the headers.

Now the last step is to change the type and before we give you a preview of that, in the name column it says abc, that's the icon to say that this field has a text data type if we click. abc a dropdown menu appears and these are the different types of data that we can use in Power Query. Decimal number up to 15 digits, as in Excel spreadsheet. Currency that will apply currency but will only allow four digits. There is an integer that will show it. as a percentage date time date time text true false and binary, as we will see later, that allows us to have files in a column where we can do things like combine tables, now let's move around and here we try an abc that we can see the left alignment that we says it's text, but when the power query is applied, change the type to all fields, it usually hits the mark in this case because there were no decimals, it shows the integer data type displayed as a one, two icon , three.

I can also say that these are numbers, of course, because they are right-aligned, so these applied steps, the automatic ones and the ones we will add later, are the basis of a power query query, but behind this basis There's an awesome Power Query functional language called m-code and Microsoft says m-code and m stands for data merging, which means gathering a lot of data and combining it in the right way. Now Power Query with the automatic steps and when we use the UI 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 it's not necessary.

I'm going to show you how to write some mcode, although when the mcode that we can write is better than what Power Query can write now I have to go show you where to look to see that mcode because it's a beautiful thing that Microsoft created now first, there's applied steps, but second, when you click on any particular applied step, it looks in the formula. bar and you can expand the formula bar look at the function names csv dot document file dot content that's awesome microsoft named all these m code power query functions with really easy to understand names and there's the file path if we go to promote headers, indeed, they cleverly named this function table point promotion headers if we click on change table point transformation column types type, so we have applied the steps and then in the tool bar formulas, we can see each step in particular and the code m that was created, but now I want to show you the third place to go and look and notice that it is called source, there is the formula that is called promote headers, there is the formula, if we go up to the home ribbon tab, check out the advanced editor, let's click here is the advanced editor in a new window and this is all the official m code that it always starts with, let's say the name of the step, then an equal sign and then theformula now look at this first step, we were allowed to see it, it is called source when we click on the steps applied here it shows us the result of the source at the end is a comma and then of course on the next line is the name of Next step now, why is there a pound sign?

Because in Power Query and in Excel we also know that any text is enclosed in double quotes, so if this name only uses double quotes, Power Query would think it's text, so when they created the code they said m. 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 the source doesn't need the pound sign and double quotes because there is no space, so the second step from the equal sign table promotes headings with a comma now. The other surprising thing about step one is the font name, but what does the next step do to promote headers?

It works with that, so for automatic steps and when we use the UI, the first argument of the next function is always the name of the previous step, comma in at the end, change type, table, point transformation, types of columns and sure enough there is the name of the previous step now when you get to the last step there is no comma, that's because repeating the last step tells Power Query that this is what it will deliver. the final value in our case is a table with promoted headers and modified data types, we can click cancel now most of the time all we have to do is just look at the steps applied, but now you know the steps applied, see the formula in the formula bar. advanced editor to see everything now that we've had a full 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 the source to be selected.

I want to act on the last step, so I select the exchange type. Now our first goal is to remove some columns that we don't need the quarter and the course number and there are some columns at the end that we don't need. The easiest way to do this is in the left corner there is a drop-down menu, we click and select choose columns. This is especially good when the table has many fields. We do not need a quarter or course number. We don't need any of these calculated columns at the end of the name. identify all the tests, all the quizzes, we are ready to start, click ok, there is our new step table, select points, columns, well that is a clever name, notice that the first argument contains the name of the previous step now just if you're curious, of course, if you go up and look at the new last step, it 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 summer 22, so I'm going to double click on test 1. I'm not going to have a space and I'm going to enter it. Add a new applied step table and rename the columns. We could look at the function and notice that it is acting on the previous step and there is the name of the previous field. and the new field name can now keep it open while you change the field name so I'll double click on test 2 without spaces and enter and sure enough there it is and in Power Query the curly braces mean a list and I notice there's a list inside from a larger list, each one has the old field name and the new field name, we will continue in test three and enter test 4, we will enter test 1, we will enter test 4, that is the last one and we will enter now , we can close the formula bar and our last task.

Are we going to have to add data types now? I don't want integers 1 2 3. I actually want decimals in case we get future scores that are decimal. Also, tests 3 and 4 have a text data type, so when we get numbers. Later those numbers wouldn't work so we want to change the data type and we can select the last type and do it one column at a time, but I want to be efficient and not just have this previous step that already changed the data. types, so what I'm going to do is delete it and redo it down here like that, we will be efficient the fewer steps, the more efficient it will be now you can hover and use the red x, but you have to be careful when deleting a step, yeah this step would negatively affect the following steps, you don't want to do it, but changing the data type has no effect on deleting columns or renaming columns, so I will remove this and this warns you that removing this step may affect subsequent steps, but it will have no effect on subsequent steps, so we delete.

I select the last step. I'm going to start from the beginning. Click on the name. Hold down the Shift key. Click on ID and instead of do. one at a time, right click change data type and we'll go down to text. Now we select to try one. All the remaining fields are numbers, so I scroll and hold down the Shift key. Click on the last one. Right-click any of the field names. Change type. and we want decimal and there's our new step table, point transform column types, we see null values ​​in these columns, that's what Power Query uses instead of showing an empty cell.

Now that we have done all our transformations, we have our steps, it's called and now we're going to load our table into the Excel worksheet in the close group of the Home tab, we click on the close and load dropdown menu if it does Click the button or select this first option, it does by default load it as a table in a new worksheet if you want to control where it loads, choose close and under, by the way, close means it will close the editor. power queries and load takes us to the next dialog, so I click here is the data import dialog if we select tab it. it will load it as an excel table, we can also check the pivot table report, it will put it in the pivot table cache or in the pivot chart which also goes to the pivot table cache, if we select only create a connection, it will not will load it into the worksheet as a table or into the pivot table cache, if you leave it 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 them into the data model. 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 a table to exist.

I'm going to select c4 and when I click OK, there will now be an Excel table loaded into the worksheet. when you load a query on the right, the query and connection panel always comes out too small, so we have to expand the query and connection task panel. That's this button here, if you don't see it, notice there's the name of the query. if we hover we get a preview, we can see how many columns there are, the loading status tells us it's in the worksheet, but then when we load it into the data model or a pivot table cache it's a useful hint on where it was uploaded and there is the file path now the name of this query is summer business 101 scores but that is the query object, we also have an excel table object here and it has the same name if we go up to the box name in the formula bar, sure it's the same name, but that's the name of the Excel table object and it makes sense for a query object and a table object to have the same name because they are, in fact, related , whenever we want to edit or change this, maybe we have made a mistake, we can simply open the query, change it and reload it, in fact what I really wanted to do is split the first name and last name and then sort the table by name, so let's do that and then reload the query to open the power query editor, double click on our The goal is to split this column, but I want the change data type step to be last, so I'm going to solve this.

I have the last step selected, so now I select the name. I can go to Home, transform split, or right click on split the column we want. to divide by the bounding space, now it's 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 notice that it added an automatic transmission. write now, I don't want that because I want to do this manually at the end, so I'm definitely going to fix this. There is actually a setting in the file options to disable automatic data types.

Now the next task is to rename them and finally, it 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 in the point split column of the table, there's the name of the previous step. the original name has this great function that actually did the splitting, but look there are new names and it's in a list, so we can just edit the code m by selecting name one, we'll write the name, selecting name two, the last name and Now when I pressed Enter, I did it again.

I'm going to fix this, but sure enough, we made some edits to the m code and now we have the first and last name. Now we need to move the last name to the first column so we can click on field name and drag it, there is a new step, now we come to the filter dropdown menu and look at this type and theFiltering looks the same as on the worksheet. I'm going to select sort ascending and now we have

complete

d our task in two different columns. sorted correctly I see all the steps we want to add our last step I select last name hold down shift id right click change type and text select test hover over hold down shift select test 4 right click and we will change this data type to decimal number now that we have all our steps, everything looks good, we approach 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. A beautiful thing, we have updated the Excel table by editing the query. Now next we want to move around and we want to add two new fields, one for the total score and one for the percentage score, now every time you perform calculations like this on a column. You have the option of using spreadsheet formulas or you can redo them in the original query. Either way, it's okay. Sometimes people are more familiar with spreadsheet formulas 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 loading it into the worksheet.

Now this will be called total score, so when I type a field name, it adds a new field to the percentage score of the Excel table object and I type. change the column width now, before doing our formulas above. In fact, I'm going to list the maximum scores so 100 100 and then for tests 30 and 30. That way we can add up the maximum points for the assignments and use them in the denominator for our percentage grade, so the maximum scores I'll enter some data and some formatting. Now I went ahead and added some formatting to the numbers for the completed task and then we want to add them, so I hit Enter so far, maximum total points possible. in this class it is 260. now I want to add the total score for each student alt is equal and I am going to redirect test 1 to test 4.

Notice that this is a table, so it shows 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 be moved and then displayed instead of a cell reference with a colon, cell reference, lists the field name , the field name, the field name with a colon, so when I press Enter, there are the total scores. now we can build our formula percent equals the left arrow and there is the syntax for the relative cell reference divide by and this is a cell out of range so I have n2 and we need to lock it so we press the F4 key when I press enter the formula. is automatically copied and in each cell we compare the points accumulated for the particular student with the possible total 133 divided by 160.51 257 divided by 260.988 now we can display this as a percentage highlighted control 1 percentage two decimal places click OK I'm also going to add something of style formatting, right click on the mini toolbar and then on some borders, so what we did was create a query and load it into the worksheet and then add formulas and formatting.

Now let's look at the updated website, here's exhibit three. It's published now, this is our fake website, but what we would do is click on csv and download the new file. Now there are two ways to download the file and the way I normally do it when I download grades from the canvas is when I download it. I replace the old file using the same name that way 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 display any of the score sets then download the file. files and name them differently, that's what we're going to do, we're going to go 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 refresh. the data, the first way is to edit the query, double click and in the source step, click the gear icon, this dialog box allows you to navigate, so click browse, you will find the selected file, click click open, I'm going to click escape, that's one. The other way to do it is in the data ribbon tab get the data source settings. Now I like to do it this way because in Power Bi Desktop this is very similar to how we do it: we open the data source settings.

We may have many connections to many different data sources, we select the one we want and click change source. Here is the same dialog box. I click Browse, select the file to import, ok, we close and we have to update. You can click the Update button there or you can. Right click on refresh table and so I have the new scores. I come up here, type 100 and enter and everything will be updated now. What if we get even more scores? Well, let's go to the data source settings, select the source, click change and instead navigate. Guess what, there's only one character difference, so I'll just add a three here.

Click OK, click close, right click refresh, add the 30 to the top and everything will work fine, so in this example here we use ratings data, but of course you could be getting data sales or government data from a website using a csv file. We saw how easy it is to get a power query. import it, we can clean and transform it, load it into the worksheet and update it later. Now we want to go back and look at our next example. I'm going to click on sheet pq2 pt info example six, this will be one of the best examples in the entire video and the reason is that business people for decades have had to take multiple files with monthly sales data, combine them into a single table, make your monthly sales report and plot the results, but the problem is that 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 no is having trouble importing all these text files by combining them into a single table calculating sales and then we load it directly into the pivot table.

The cache and pivot table are perfectly suited for these types of calculations and then we plot them 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 discard the new monthly files that we have. go into the folder and click refresh and everything will refresh now looking inside our example six folder that we downloaded we have nine different text files now I want to open one of these right click open with word now I opened it in word because in in the home tab paragraph I want to click on the non-printing characters button and now you can see the delimiter that the arrow in Word means a tab.

This is the second common delimiter besides a comma and you can see that we have one, two, three fields, date and price units when we go to power query we will combine all 12 and then, more easily than in the Excel spreadsheet, we will get power query to multiply these two columns and we will create a sales column now starting in the pivot table of sheet pq2. We're going to go up to the get data transformation and the get data from file dropdown and this is certainly one of the most amazing things that Power Query brought to Excel.

We can tell Power Query to get everything from a folder so we can Click and navigate directly to our folder, we won't see anything inside it because Power Query will just look in this folder and grab everything in it and bring it to the editor from Power Query, so click open there's a preview and this is different than our last example. This is a table, but binary means that there is a text file that for us is a table in each row. All of these fields here are attributes for that file. This is just how you would see it. in windows explorer access date modified folder path and also very important that we have to use this column here the extension now we click on transform data we can see on the left we have two queries now We immediately come and rename this.

I'm going to call a lot of files in a table and enter, there's our source, come up in the formula bar, there's the dot file from the functions folder and there's the path to our folder. Now this content column contains the text files. Now if you click on the side, don't click directly on the word binary because that will extract only that file, but on the side we can see this icon preview for January, February, etc., which means that in this content column we have a tables column with the same structure field. names in the top records in rows, all with a tab delimiter, which means that with Power Query we can easily combine them into a single table and the way you combine the files.

I'm hovering over this double arrow button that points down and says merge files now. you have to be careful binary only means there is a file here if they were excel files or database files and the table object in each row did not have the same structure and delimiter for each row then this button would not work then is when you use this button only when the tables have the same structure and delimiter now, before using this button we want to think about the fact that Power Query points to a folder and gets all these files now it looks like all the files are files of text, but what happens if we accidentally place an Excel file, a database or a Word file in that folder?

If we tried to combine it, it wouldn't work. The best way to deal with this is to simply make sure there are only text files in this folder. folder but if there may be other files there is an easy way to prevent those files from appearing in this column and that is 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 that I have You have to learn something really important about Power Query M code. Power Query M code is case sensitive which is different from Excel worksheet.

Almost everything in the worksheet is case-insensitive, but in Power Query everything is case-sensitive, so it looks like some of the files come. type text in uppercase and some in lowercase, so if we're going to filter here, we'll have to say "hey, include that possibility, this possibility, this possibility, etcetera, but we don't have to do that because Microsoft knows that, etcetera." they offered an option, let's right click on the extent column, go down to transform and sure enough there is an option to transform everything to lowercase, so when I do that now we just have to filter to include lowercase text, now added this new step . to add a second step towards the text filters, I mean just equal and as we thought ahead and put everything in lower case, that's all we have to do, click OK, now we're ready to combine, first we want to delete all attribute columns, so go to content, right click on delete other columns and there is our step.

Now we are going to click on this button and when we click on this button, it will do a lot of things for us. The first time we click on it, it will ask us some questions. Important questions now because there are a lot of files here. Power Query will only use one of the files to create all the code for us, which is why it says I'm choosing the first one. Here is the source of the file. Make sure the delimiter is correct. and it is a tab based on the first 200 rows which is fine now when we click ok here power query will create all kinds of new queries for us all these queries were created automatically and then used to get this final table , which is It's a beautiful thing now, we didn't really need to know all this because we were sure that all the tables had the same structure, but what it did was take a sample file and create a parameter that is a variable from a file. transformed just one of the files, then created a custom function and in our final table added a bunch of steps including invoking that custom function, just amazing.

Now the last step we need before loading this is I want to multiply the units and the price. a new column, so look at this, we can select the column, hold down the shift key, click on the price. I have selected two columns and I want to add a new column so I go up to add a column. There are all kinds of amazing ways to add columns, but from the number. click on the standard and when we click onmultiply, notice that we are clicking on something in the UI, it will create the formula for us.

There's nothing like this on the worksheet, so we'll click multiply and sure enough, it took 3 times 21.95 and got 65.85. and then I did that for each row now here is the new step let's see the formula in the sum of points column of the formula bar table it is definitely acting on the multiplication from the previous step that is the new name and then , for each row, units per price and in the last argument was polite, added the decimal data type, it's called number, but that means decimal. Now we definitely want to rename it, so instead of double clicking and adding another step, we'll be smart, we'll write better code than adding a new step, we could say that in the second argument that's where you named this field, so I'll double click and call it sales and enter everything right, so now you can say you've actually written your own code m.

Let's close this. we have all our steps, we are ready to load this now let's go to home, close and load the dropdown, close and load and the data import dialog, we don't want to load it as a table and the reason is that we are I will never use that table for things like filtering, sorting, or creating other formulas, so why duplicate the data source by putting it in the table and then creating a pivot table that stores it in the pivot table cache in this case when you don't need it? to the table and you want a pivot table, that's your selection, we want to put it in existing c4, click OK and even though I don't have a table in the worksheet, it's in the pivot table cache, now let's build our pivot table . drag the date into the rows and of course it will instantly create a bunch of extra columns in the pivot table cache.

I don't need the date, I don't need days, I see months, that's what I want, let's change the column width and I'm. I'm going to click on the formula bar just because my cursor was nearby. Change that within months drags sales down to values. I get the sum of sales in the header. I'll put a dollar sign in parentheses to indicate the unit and then this. it's a standard pivot table so in the values ​​area right click on the number format 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 will show zero decimals.

You don't need to see the cents, click OK, now we need to show the month-over-month change and then the percentage change and then the percentage of the total, that's where the values ​​are displayed as they come in. We drag the sales down a second time, right click and summarize the values ​​by has. to be sum because we want to see the change in total sales so the aggregate calculation is correct so we go down to show the values ​​and there is an amazing difference so I select you want to know the difference with what well for Of course, I want the month-to-month difference and when you do month-to-month difference sometimes the accounting reports require everything to be based on January, but that's definitely not what we want, we want the earlier, so earlier means that when I get to this line here I go to take the difference between this and the previous one when I'm down here, the difference between this and the previous month I click OK and if you subtract to the side 520 minus 671 you will get minus 150,000, right click The number format it will show everything to zero decimal places.

This will be called change with a dollar sign. Now we drag sales down again. The right click summarizes the sum, so we will show the values ​​as and the percentage difference from the previous months. Click OK and that calculation is polite. Add the number format up here I'll type percentage change and enter and then we want another calculation. I want to compare each of the individual monthly amounts to the total at the bottom of the column, so I drag the sales down to the values, right click on summary. sum, so I show the values ​​as a percentage of the Total Percent of Total Sales column.

Double click. I'm going to change the number format in both percentage columns to show zero decimals. Now what we want to do is add some files to the folder and refresh this report remember the query there is looking in that folder so when we refresh it will bring all the new files now here it is from October to December so I do I click on the first one, I hold down the shift key, I click on the last one and I want to put some non-txt files in, we want to test our theory. I'm holding down Ctrl to select items that aren't next to each other.

We'll do a pdf and dot csv, copy them, select v folder control, okay, so this should do it. in the report it shows us october november december and these additional files should have no effect on our report as we load it directly into the pivot table cache all we have to do is right click on refresh pivot table and so it's absolutely incredible. Also, let's look at the query, double click and we'll have our one table, let's look at the source and if you don't see everything here, you may have to click on Refresh Preview, but indeed it came from October to December dot csv and the pdf and If we look at our filtered rose pass and sure enough, our filter that protected our report worked like a charm.

I'm going to close this query editor. Now we want to create an Excel chart control and pan it to zoom, however when you create a pivot table with multiple calculations, if you point a chart at this pivot table it will include all the calculations and you won't be able to delete them now, that's different than if we build this with formulas, if you create a formula report with many different columns, you can pick and choose any of the columns for a chart that you are creating, but no problem, we are going to highlight this and I am going to copy the entire pivot table and then aside, I'm going to select i4 and control v i want to point to the smart tag and say keep column widths scroll and click inside the pivot table.

I see queries and connections, but let's go back to the task pane of our pivot table and I'll just drag the calculations except for the one that By the way, we want the sum of sales from our chart, if we don't copy and paste that pivot table and you want to access the data in the pivot table cache, you can go up to insert the pivot table dropdown and that's what this option is for. from an external data source, I click, you have to choose a connection and then there is the query and the name of our table, so you would select that click on open, but we have our pivot table here with a single cell in our pivot table selected, we go up to insert a group of charts and we are plotting values ​​in a time series over consecutive months, so the perfect chart is the line chart.

Let's do the one here on the right with the marker, so I click now, we don't need these field buttons. click the buttons to hide all fields, this is chart garbage, it doesn't add anything, a legend doesn't really help unless you have more than one series of numbers, so I'm using the delete key above. I am going to create a chart title similar to company and monthly sales. I select the chart title when I see the solid line I type the equal sign which takes me to the formula bar. I select the cell. I see the formula.

I press Enter with the cursor to move on the outer edge. I'm going to click and drag now I'm going to hold down alt to snap to the grid now we're going to move by pressing alt to snap to the grid now I want to add some percentages and conveniently we're going to steal them from these other table chart elements dynamic green arrow data labels and when we click on more options, the right task pane opens, we will uncheck both and we want cell values, we will get a percentage change, click OK, check the cell values ​​down here, the label position will say up now for the vertical axis, I'm going to select control 1 and in the task pane we're going to say display units and I'm going to say in thousands, this is actually a pretty new feature in Excel that already It is not in Power Bi Desktop. a while, but it looks good, so we have our visualization and our report fine.

In this example, we saw how to go from 12 files to use Power Query to combine them, create a sales column, load it into the pivot table cache, create our report and then our visualization, all right, we want to go over and look at our seventh example in the vlookup and pt sheet now in example number seven, eight and nine, we are also going to have three different tables and from the three different tables we are going to I need to create reports and visualizations now in the three examples we have what is called a problem of data modeling.

Our table does not have all the correct columns. We need to create our final data analytics solution. If you look at the sales rep ID, these are numbers, but I need to. to search each of these numbers and display the sales rep name same as the product, i need to search each number in each row, display the correct product name as a new column once we have the sales rep name and the name of the product, then we can create reports that actually have the name or down here the name of the product. Now we could create the reports with the sales rep ID and the product ID, but the reports and visualizations wouldn't be as easy to use now when trying to create reports and visuals.

Since multiple tables and you have a lookup situation, there are several different ways to solve the problem. Now the first way is to use worksheet formulas, we will add two additional columns and use the x search function to search for the sales representative and product names now when is it appropriate to use spreadsheet formulas well? If you already have the data in the Excel spreadsheet and control the down arrow. It doesn't have much data. 43,000 rows is not a lot of data. Home Control and our data analysis. The solution can be created enough with pivot table reports and Excel charts, then we can go ahead and use worksheet formulas, have a table and then create our reports and visuals.

Now, in examples eight and nine, we're going to have the same tables, let's look at this. rdm dax sheet example eight we are going to have the same three tables but they will come externally from an external Excel file, so we will use Power Query to import and instead of loading it into the Excel worksheet and creating almost a hundred thousand formulas, we will load them directly into the power dynamic data model which allows us to build relationships between the tables and then build dynamic data model tables based on multiple tables. Now we choose this approach, of course.

We're using Power Query because we're importing, but we chose 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 then we'll look at another reason to use PowerPivot. or power bi desktop is if you have a large amount of data, power pivot and power bi desktop can handle big data much better than worksheet. Additionally, we will 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 will have the same data source, we will use Power Query inside Power Bi Desktop, we will load it into the data model there and we will create Power Bi dashboards and visuals for the data model.

Now, when do you use Power Bi? desktop instead of power pivot or excel worksheet, well the most amazing thing about power bi is that it has interactive and shareable visuals. Okay, let's go over and start the x lookup pt example, now that we have our three proper data sets and we've already converted the first two to Excel tables. This table is called f transactions. This is called a sales representative. Let's click on the product table control and then enter. Immediately go to the table design properties and now we will call this product d. Because? the d the d is for dimension and this is a dimension or lookup table that contains the attributes that we want to include in our new columns that will serve as conditions and criteria in our reports and charts.

Now there is a very important feature to look for. or dimension tables and that is the first column of any lookup table or dimensions must have a unique list and both have it and the reason is simple here in the sales rep id column we have many repetitions from three to three five five and so on, but for a particular row, when the lookup formula will look for three and try to find a match, if there were duplicates, it wouldn't know which one to bring back, so the dimension or lookup tables will always have a unique list in the first column. and then attributes orfeatures that can be used for conditions or criteria to make some summary calculation or other features like the price of a product that you could look up and use in a formula for the way this f transactions the f means fact because they are The facts that we are trying to To summarize now we will use Excel tables.

We don't necessarily have new data that we will add later, but that is the main benefit of Excel tables if we add data later or new products. 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 that we create will be easier because we are going to use table formulas, now this new field would be called sales representative. and of course when I press Enter a new column is added to the table. Now we need to look for three here five here four here and in each case we need to get a match in the first column and then go to our second column and retrieve that value now in the old days we used the vlookup function but that is a function of the past which is much harder to use than the amazing new search function x now our goal is to put a search value which is the value we have.

I'm going to take and try to match on the first column, so I select that cell which is a table relative reference comma lookup array, which is which of the columns or fields has the elements that we're going to try to match based on the search value. well, it's that comma return array that's there, those are the values ​​that we want to bring back into the cell. Now, this is an Excel table, so I should have clicked on the top with my black arrow pointing down and that places the column reference now. There are many other arguments in the x search.

We have a video on lookup functions, a few videos later, so we'll learn all about these arguments in that video, but to do an exact match where you're trying to exactly match something in the first column you only need the lookup matrix of the lookup value and return array. Now this formula will be copied automatically because it is an Excel table which is a relative reference, but when we highlight entire columns, they are locked as the formula is copied, which of course is what I need to close parentheses and when I press Enter wow, I added a column of sales rep attributes that I can now use as a condition or criterion to help do some summary calculations.

Now let's do the same thing here, this would be called the product, enter is equal to x. lookup, I'm going to place the arrow over the relative cell reference comma lookup array. I click on the top of the product id column in the comma return array of the dimension or lookup table, those are the items I want to get, so I select them. field and there is our formula, close the parenthesis and enter now if this is forty-three thousand rows high forty-three thousand times two is almost ninety thousand formulas, now Excel will have no problems, this is a small data set, but If I really had one hundred thousand or five hundred thousand rows we would change to power pivot or power bi desktop, that large amount of data is handled much better with those tools now the rule I use is approximately 50,000 rows is the maximum I put in a worksheet Excel, but the worksheet has a million rows, so you can add a lot of data here, but things tend to slow down when you do it now that we have our only table and when we add columns like this from dimension tables or lookup, this is called a flat table and all that means is that we have a table, when you have a table, you can use what is called the standard table range pivot table.

Now Standard PivotTable is the PivotTable tool that works with just one table and uses summarized values ​​or displays values ​​as calculations. a data model pivot table that we will see when we use powerpivot is a pivot table tool that works with multiple tables and uses dax formulas to do its calculations. Now I'm going to use the keyboard with a single cell in our flat table. alt n v t now I'm going to put this pivot table on this sheet to illustrate the reports, charts and tables all together, normally I would put it on a different sheet by clicking on cell k19, click OK, the first one is sales rep, like this that we We're going to drag the sales rep into the rows instantly, we get a single list, we're going to get the sum of the sales, so we drag the sales into the values.

Right click on the number format. The comma of the number will show zero decimal places. Click OK. Now go back, click on a single existing cell alt v t. I'm going to put this one on n19. Click the OK region which was an attribute that was already in the table. We drag it down to the rows to get a single list. Drag sales to values. Right click on the number format now. I want you to notice something: We are using sales field in several reports and every time we have to add number formatting, this is how it is with standard pivot table when we use PowerPivot or Power Bi Desktop and create reports and data model visualizations.

We won't have to add number formatting repeatedly because you can add number formatting to a reusable dax formula, but with a standard pivot table you have to reapply the number format every time you use the field, click OK, return 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 Accept Product. I get my unique list of sales up values, right click on the number. We will add number point 0 decimals click OK now we are not necessarily going to look at this, we are going to make a graph from this and whenever we have categories like this and we want to compare the quantities in these categories, the perfect way The insert graphs graphs are not a pie chart.

Research shows that it is more difficult to distinguish differences between quantities with a pie chart. The preferred chart type for comparing quantities between categories is column or bar. Don't use the 3D, which is mostly a garbage bar chart. It is a good option when you have very long labels; otherwise we will now talk about some of these other two dimensional columns and bars in our next panel and visualization video, for the moment we will select a grouped column, right click on field buttons hide all field buttons we will remove this graph remove junk I'm going to temporarily move this here right here total product sales and I'm going to indicate the unit and we want to show them in thousands, so at the top I'm going to indicate that in the title of the chart here is the equal sign of the title from the chart which takes me to the formula bar, click on the cell and enter now.

I'd like to display the amounts at the top so we get to our green plus sign. data labels and actually for this one we can just click on data labels. Now let's do a little trick because we have garbage graphics here. We probably don't want both, but I want to display them in thousands, which will then display them in thousands. and then I want to delete it because I don't want the double numbers, so with the vertical axis selected control 1 to open the task pane, show units of thousands and sure enough, it achieved our goal, but now I'm going to delete this. and there is our finished chart and there, with the help of some data modeling with the x lookup function, we have created our two reports and our visualization.

Now we'll use these same two data sets and use slightly different tools to summarize. We didn't have that much data here, it was relatively quick and easy to add these x lookup functions and the calculations and visualization we wanted are easily done with a standard pivot table and Excel chart. Let's move on to the leaf relationship data model. and dax formulas We will now look at our Excel file containing the three tables, then we will use Power Query in Excel and PowerPivot in Excel to create these data model reports. Now, if you have never used PowerPivot before, you need to enable it. which include file options, add-ins in the admin dropdown on the left and we will select the add-in com, click Go and then you will check Microsoft PowerPivot for Excel or here is a great tip on the data tab in the data tools of the Excel ribbon if you click. manage the data model, we are supposed to jump to the data model, but when you click on it and it hasn't been enabled, it says: you want me to enable it, so I'm going to click on enable and there we see the empty data. model and, more importantly, there is a power pivot tab in the Excel ribbon.

Now the funny thing is that we don't really need to use this tab, but it does have to be shown for this button to work and the reason why we are not going to use the powerpivot tab when we click here to jump to the data model, we can do everything that we want, how to create relationships and dax formulas more easily in the data model now. in pp sheet, what does powerpivot do? It creates pivot tables from the data model and does these things. It allows you to create relationships between related tables to avoid many lookup formulas like x lookup.

Allows you to use more than one table in a data model pivot table. report, allows you to import large amounts of data that we don't have in this example, but in our last example we will have 7 million rows of data that we can import using Power Query. We import into a behind the scenes data model location called the columnar database compresses the data and can contain millions of rows of data and powerpivot will allow us to create reusable formative dax formulas. These are formulas used in data model pivot tables, which means that instead of using the default values, summarize and display values ​​as we will build our own formulas now let's go to rdm dax now we can see our source file in our folder video for here is the example of 8 source tables.

I'm going to double click to look inside now on the table sheet is the fact table and one, two three dimensional tables. We used both tables in the last example, but we're also going to have a date table and it works the same way as searching for a single list and returning a product name from this date onwards. here where there are many duplicates here we have a unique list of dates so we can find a particular day and return a particular attribute like month name, year name actually you can add many other columns to a table of dates, day of the week, fiscal year and many more.

We'll actually add some of those other date attributes in later videos, but the only data attributes we'll need in our reports are the month name and year. You might be wondering why we are creating a date to mention the table when. a standard pivot table will automatically group the dates into months and years and the reason is that we are going to load all these tables not into the pivot table cache but into the data model and when it is in the data model because potentially we can have millions of data rows, we don't use that auto date feature in a standard pivot table, we create a date dimension table just like we would for products and sales reps, plus when we load these dimensional lookup tables into the data model , We're going to avoid having to create those auxiliary columns where we use the x lookup function and the way we're going to do that is to create a relationship between each dimension table and the fact table.

Now if we scroll up, I have a visual here that helps understand the relationships now in each of these lookup tables or dimensions, the first column will always have a unique list which means one of each date, one of each ID product and one of each sales rep ID in a relationship. It is called one side, however when we go to the fact table, the sales rep ID column can have many duplicates and that makes sense because this sales rep is going to make a lot of sales, that is why it is called the side. many, so what we'll do in the data model is create a one-to-many relationship, then we won't have to add an auxiliary column with a lookup formula.

We can simply drag and drop fields from any of the tables and our report will work perfectly in a unique way. list one side of the many unique lists for the product ID and the many one-to-many relationships, so our next task is to import these four tables into our other Excel file. Now, this is what an Excel file is 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 andIn fact, you can have many types of objects, such as defining names or even the entire set. worksheet, so what we're going to do is say, Hi Power Query, I want to import the objects from an Excel file and Power Query will immediately say which of the objects you want and we're just going to check these four tables to We're going to close this scroll down, we're going to create our reports down here, but first we have to go up to get data and transform and import tables from our Excel file.

Get dropdown data from Excel workbook file. Let's click. video 4 folder, there is our example 8 source tables, you can double click or select them and click import and the power query is polite, it is searching in that file and the objects it found are four excel tables and one sheet called tables, we want to select several elements. and check each of our tables by the way the blue field name line at the top is the little icon that means it's an Excel table instead of a worksheet. Now when we click on transform, it will open the Power Query editor and create it. an individual query for each table, so I click on transform data here is the power query editor on the left we want to close the folder that has all the queries from the join of the tables there is our ratings table, which is the first query we did, it's the many files in one, but here is the date we want to click on each query and make sure the data types are correct date integer text integer all are correct that's a great name for our query and those steps are fine d product we are checking the data types integer 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 text of integer and correctly assigned decimal to the sales column and then an integer for the product id which is a good name, the steps are good, actually these three steps are the same in each of these four import queries of tables, so if you go back to the origin in any of those queries, it opens the formula bar and you can see the local file path. there and notice that the functions have clever names like xl.workbook because we're accessing a workbook.contents file because we're trying to get some of the contents of that workbook and this formula provides a table down here if we we move over it. a column called data and in each row there is one of the different objects actually d date, if you click on the side of the word table you can see a preview which is the date table here d product so that this column here has all the objects.

From the Excel file, which is step one, navigate through this table and select just one of the objects up here. You can see that you got the transactions f and there's the table and those sets of steps applied are the same for each of the import queries here. The navigation is dated D now, so we don't have to add any steps like we did in previous queries. Now all four have not loaded, so when we go to Home, Close, Close and Load the dropdown, we definitely want to close and load. two in the import data dialog, we definitely want to go 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 reflection has to be the first thing you do do is go down and check just create a connection the number one mistake people make when learning power pivot and data model is keep this selected and then go down and click this and the problem is when you click ok it goes burden. the tables to the worksheet and the data model, so we mean just create a connection by selecting this, we are forcing this top to have no effect, we definitely won't load it into the worksheet and we definitely won't we will load to the pivot table cache so just create a connection add this to the data model when you click ok here under queries and connections you can see Power Query is working to import this but also the data model of that Columnar database compressed all this data. now you can hover over and we see loaded into the data model, which is the data model and this one too, now that we've loaded them into the data model, we're definitely going to look at and build relationships and our own formulas now.

I'm going to use the save controls and then click the Manage Data Model button. Now you can see that this opens the Power Pivot for Excel window. It is a new window on top of Excel. This is not Power Query, this is not Excel. this is our data model window, it has its own tabs down here, these are previews of what's in the database in columns, they show it as a real table, there's a date, a product, a sales rep and a transaction. What I mean by preview here is If you have millions of rows in the database in columns, it definitely won't load it all here, but it will give you a preview.

Later we'll come back and in the fact table we'll create our formulas in what's called the measures grid now when we load this into the home tab view, we're in the data view and you can see the little tabs, which It means we can preview the tables, but we want to go to the diagram view. lines between the tables, this is where we can go and create relationships, so I'm going to click now. What we see in the diagram view is the name of the table and a list of the fields. Now I'm going to organize the fact table correctly. in the middle, the date dimension table on the left, the product and sales rep on the right and in the diagram view, there are two important things we can do: create the relationships between the fields by simply clicking and dragging and then when we have fields in a table like sales rep id that we don't want to appear in the reporting area that means the list of fields in the pivot table we can hide them now first create the relationships and that's how easy it is .

I'm going to take the sales rep ID and drag it over the sales rep id when I drop it, it creates a one to many relationship, there is exactly one sales rep id in each row and here in this column there can be many duplicates , we will drag the product ID from the dimension. or lookup table to the fact table, there is another one to many relationship and from the date table d we will drag the single list of dates to the many repetitions in the transaction table and there by drag and drop we have relationships created and These relationships will help in two ways: the first is that it will replace all those x lookup functions, which means that the Excel file can calculate more efficiently or faster because it doesn't have all those formulas and the second is that we will be able to drag and drop fields from any of these tables into our data model pivot table report, so in the diagram view we create relationships, but we also want to hide fields like the date id, which is a field we will never use. in our reports and also a date field line that we want to hide because we are going to use the date field here also the numeric field, we are not going to drag and drop numeric fields in pivot tables, but we are going to build our own formulas, so that in f transactions we need to hide the date.

I click on that one and hold control, click on sales rep, product id and sales, right click on hide from customer tool. What this does is hide these fields in the pivot table field list now. We kept region as non-hidden in the fact table because it is an attribute, condition, or criterion that we can still include in our pivot table and we don't have a region dimension table. Product ID. Right-click Hide from Client Tool. Sales representative ID. Hide from client tool. and the month number, we don't need the month number, although we will use it later.

The month category that we're going to put into our pivot table is this month, so we're going to right click on Hide from the client tool and by the way, on any field. that are hidden we can still use them, for example, we are going to use the sales field to create a formula in just a moment, when the fields are hidden, they are simply not hidden so that they do not show in the field list of the pivot table . We create relationships and hide our fields. Now let's go back to the data view. Now, in the data view below the bottom of the fact table, we can create formulas in what is called the measures grid.

If you don't see this measurement grid, that's due to the area calculation. the area is not selected. I'm going to select this now. I'm going to click on a cell below the table and this is a place where 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 calculated dax column. We are not going to create one in this example. Now dax in dax means data analysis expressions and expressions is synonymous with dax formula. is a function-based formula language that has some functions that are similar to the Excel spreadsheet and some that are unique to dax.

Now, when you're creating Dax measures, there are actually two locations where you can create measures. Now in the Excel ribbon, there is a Power Pivot tab and in the Calculations group you can create a measure. I don't like how this feature here puts the formulas in the measures grid, so I tend not to use this, although some people do, so I'm going to use the measures grid. and the formula will be similar to Excel, totally different from Power Query and if you know the access formulas, it is exactly like the access formulas, so we have to name this measure dax and that name will appear in the transaction table f in the field of the dynamic table. list, so check this out.

I'm going to start writing on the measurement grid and notice what happens. It takes us to the formula bar. Now that name will appear in the list of fields in the pivot table, but now we need to put in an assignment operator and then. The formula that follows now in the Excel spreadsheet 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 the sum so you can type sum and we can see the sum function in the drop down menu, now the sum function and other functions like average, minimum and maximum, they are same in Excel, there are other functions like summarizing some x which are only in dax.

Now I press tab and it says it wants a column name. Now I can scroll down and then select it there. it is right there or in powerpivot and this is different from power bi desktop. You can actually click on the column header. This is the same as Excel tables, so when I click on the top of the field name, it is placed correctly in the column reference and column references. always have the table name and then the field name in square brackets. Now I close the name of the fiscal measure assignment operator formula in parentheses.

Now when I press Enter, I get a result that gives me the overall grand total for each value in the sales column. We will see the smart thing about dax formulas is that when we put this formula in a pivot table of the data model, 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 correct answer now, another The amazing thing about dax formulas is that we can add number formatting to the formula and then wherever we use this, the number format will follow us, so I'm going to click on the comma and then decrement the decimals and there is our dax measure to show. two other simple tax measures, let's calculate the average sales in the formula bar, colon, equals v and the average function is the same as in the worksheet.

I'm going to select sales, close parentheses, and enter the format so that the average is $318, that's the average of each transaction in the sales column. Another formula we may want to look at is the count or number of transactions. Hopefully I spell it correctly: number of transactions, colon, equal sign and this is a unique function for dax. This function does not exist. in the Excel worksheet or in Power Query, it counts the rows and asks me for a table, so I'm going to put f transaction notice. I type ft, I see it in the dropdown, so I press tab and the amazing thing is that the rows will be counted. that transaction f and when you see conditions and criteria in the pivot table it will counthow many transactions there are that meet those conditions.

Now we'll explain a little more about how that happens when we build our pivot table, but for now we'll close. Parentheses are entered and this will be a comma and of course 0 decimals because we are counting. Now let's go back to the diagram view and we can see that the transaction table f has some new entries at the bottom. Now here we are in the diagram view. Power Pivot window for Excel and this is an image of our finished data model. Now there are five parts to this data model that we created. The first one we can't see is the columnar database behind the scenes that compressed and stored the data.

The thing is, we can preview the tables here in the diagram view or in the data view. The third thing is that we create relationships in this data model and then we create dax formulas. In this case we create one, two, three dax measures, now the dax measures. here in powerpivot an f of list of pivot table fields right now we want to jump to the excel worksheet and create our first pivot table data model. I'm going to close the Power Pivot for Excel window with the dialog box says data model pivot table, we just select the location and click ok, now I am going to grab the fields from the pivot table. task pane move it resize expose all tables now this shows you all the tables in the data model plus all the other excel tables that are in this workbook if you hover over for example it tells you it has a range of cell origin, but We know they are in the data model, but they come from a query.

We are now on the All tab, which means we can see all the data model tables and all the worksheets. If we look active, there is nothing here. it still means there are no tables, fields or measures because we haven't added any to the active pivot table, but if it returns all, our first report will be a region report and since we want a region in the rows and that is the default I can I just check and I instantly get a unique list of regions in the row and I see the field down here. Now we want to see the total sales for each region and I'm going to check this because the default measurement comes down to values. and look we don't have to add number format we will check the average and it has our number format but I would like to change the name of this and the format so guess what I can go back to the data from the model data tab data. tools manage the data model in the data view, I select the cell with the measure, zoom to the formula bar.

I'm going to call this average transactional sales. I hope I spelled it right and enter, and I want to increase the decimal, so 1 2. now We don't need to close this, we can actually leave it open. I can just take my cursor and click again on the pivot table and see that measure updated if we had used this in many different reports of the pivot table, they would all be updated and now Let's make a check mark for the number of transactions and there it is for the region. We have three different dax formula calculations. If we search for assets, we can see all the fields we have used.

Now let's go back to everyone and I want to create the next one. pivot table, about four cells below, no alt n v t for the table in the worksheet, we will use alt n v and d for the data model d and because the OK button is selected, we can press Enter now, this time I want a sales representative , but I'm going to right click on the table and force it from the beginning to go to the active transactions tab. Now I can also go active and that's a matter of the beauty of the data model pivot table.

We are allowed to have multiple tables in the pivot table. list of table fields to avoid all those x lookup functions, we definitely hide the fields we don't need which makes this less cluttered and we create reusable formulas called dax measures that even have numeric format so for our report from the sales rep I just check that to get my single list and then check, check, check, I have the three dax measurements calculations. I didn't have to manually format any of those columns. Now let's create our third report for the product and this time we are going to make the alt nvd keyboard. and enter, we're going to send the product to the active f transactions to the active active tab, which is a matter of the beauty of the data model pivot table.

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 here let's drag the year to the rows month under years and what's happening here the months are sorted alphabetically in a standard pivot table there is a custom list behind the scenes that sorts the month when you drag it to a standard pivot table but in a data model pivot table that feature doesn't exist so our months are sort chronologically, we have to go back to the data The tools manage the data model in the data view, we go to the date table, select the month column and remember that this has text, but the month number 1 to 12, yes we could tell this field here to always sort by this field, then of course January would come. first, February 2 and so on in all data models, when you have a date table like this, you have to use the sort by column function in powerpivot, it is the sort and filter button sort by column, you select the column to sort by month and by month number when I click OK when we return to the worksheet, the months are arranged 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 two to that best fits the width of each column. and so, our monthly report is ready, now let's control s to save, now we have to talk about how these two three reusable dax formulas do their calculations in each row of each report if we look at this second report right here, there are total sales. the formula, when we put this measure in the values ​​area behind the scenes, it calculated the correct amount for ahmed shantell gigi and got the correct grand total at the bottom and the way it did that is with something called filter context and what filter context What it does is that when this measure reaches this row, it sees Ahmed's condition that the entire transaction table f is filtered to only Ahmed's records and because the records are only for assembly, when some sums that filtered the sales column, you get the correct amount when you get to Chantel's row, the whole fact table is filtered for Chantel's transactions only and then the measure adds only Chantel's sales, but when the same measure arrives to the grand total row there are no filters in the table, so it adds to get the correct grand total now all the filtering context process is happening behind the scenes in the powerpivot engine and the columnar database, for which there's no way for us to look and see how this works, but guess what the process is exactly like the filtering that we do.

I learned at the beginning of this video. Now I made a visual to help understand how the filter context works in the sheet filter context visual, but remember that when we created this report we dragged the sales rep from the sales reps table. Let's look at the filter context visual. Now here it is. an image of the report we were talking about and here is the sales reps table d and the transactions f, the fact table and remember that from the sales rep ID to the sales rep ID there is a one to many relationship and this is how the filter context works, this measure here we put it in the pivot table, but behind the scenes, as soon as the measure sees this cell, the context is the condition or criterion ahmed because it comes from the representative column of sales in the sales rep table, it is just as we learned at the beginning of the video, the process is filtered to show only one drug, this single condition filter then flows through the relationship from one side to the many sides and, effectively all we see is five, each sale is just for ahmad once the filter has been transferred to the fact table is when the measure kicks in and adds up all the sales.

Now the reason dax formulas are calculated this way is because if you have a lot of data, if we send the filter here, the table is much smaller, guess what it only has this measure. to work on ninety 7694 rows instead of forty-three thousand two hundred and twenty-two, so filter the context and make sure the formulas only work on the necessary number of records to perform the calculation when the measure reaches the second cell here chantal in the sales rep table d is now filtered for chantal the filter flows through the one to many relationship all we see are fours all these sales belong to chantal and that is our result and of course when we remove all the sales reps table filters d there is no filter on the fact table and there is 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 see how many rows there are in the f transactions when there is no filter, it sees forty-three. one thousand two hundred and twenty two when we select gigi much less it looks like three thousand three hundred and sixty three rows so counting rows counts how many rows the sum sums up all the sales and the average formula of course averages them so that the moral of the story filters the context It's amazing because it makes dax measures work more efficiently. Now I want to go over the error on the sheet. Now we want to look at two potential pitfalls when building a data model.

We were careful and built our data model to avoid these two obstacles, but I want to show them to you, so let's go to the data tab, data tools, manage the data model and in the diagram view we very carefully hide the field of date that way, no one will drag it to a report if you drag it to a report, something really happens to the fact table. We also hidden the sales column because we didn't want anyone to touch it and we want them to use measures, but if you don't hide it and someone drags it into the pivot table, another potential problem happens.

I'm going to select sales retention control select date right click show from customer tools. Now let's go back to this sheet and we'll use our alt nvd keyboard and go into the list of pivot table fields. I'm going to right click. If the transaction shows inactive here, we're going to drag the date by mistake and see this when I drag it down to the rows, see how long it takes. Okay, I'm going to drag everything except the year and now I'm going to drag. the sales field, this is not a measure, drag it over the values ​​and it already bothered us because it certainly didn't add any number formatting, but now let's look at the data model that we want to go to in the 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 to a pivot table? You think you want to group the dates. The problem is that there are 43,000 rows for each of these columns. That's too many formulas when using a date table and a relationship, we only have two years so this is only 720 rows here, the other thing is when we drag that sales 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 enabled by default, we're going to click on show implicit measure and sure enough, this is called implicit measure, this is the one that automatically creates the pivot table, this it is called explicit measure we create it ourselves now there are many problems with implicit measures the first one is good it is hidden the second one is this is a read only formula you can't edit the formula you can't add number format please note that this is greyed out, you can't rename it, 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 don't appear so our rule is that we don't use implicit measures we always want to use explicit measures which simply means we create them ourselves now the best thing about implicit measures is that we show them and then right click on delete, yes, I'm going todelete from model, I'm also going to select the date, hold shift, click on the date, month and right click on delete columns, yes, go to home to see the diagram view, select the date and sales and right click hide. from the client tool now there is another way to prevent the date field from creating all those extra columns attached to the fact table if you move to date d and this works in Power Pivot and in Power Bi Desktop we want to go to the design tab. and we'll tell PowerPivot that this is a date table as soon as we do that, it won't add those extra columns to the fact table anymore, so the layout calendars are marked as date table, so I click and I click mark as date table you want. to find out where the unique identifier is, that's our unique list of dates, so that's right, click OK and now no matter what we do, we won't get all those extra columns with 43,000 formulas in each one.

Ok, back to rdm dax. Alright, we saw some big benefits 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 to a pivot table what we have. reusable formatted dax measures we also hide many unnecessary fields we saw that the filter context helps the formulas to be calculated more efficiently and guess what if we have a data model inside an excel file created by powerpivot we can automatically send it to the power bi desktop where we have a lot of interactive visualizations that are actually much better than what we have in Excel, so that's our next task.

Now we want to make sure we save this excel file with a data model and before we move on to Power Bi Desktop which is a different program in this excel file let's go to the bi desktop sheet now power bi desktop does these things first it's a download free from Microsoft just click this link and download power bi desktop to your computer and when you open it 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 images and sharing ability, now Power Bi has more varied visual and reporting tools that in Excel and the images 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 to 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 many people in the world do that that way, but there is a second tool called power bi online and power bi online require purchasing a license now, this is August 18, 2022 and at this time in history many entities purchase power bi online for employees when they purchase microsoft 365 office and this is what makes the online version different from power bi desktop.

You can upload your Power Bi desktop file to Power Bi online and then it's easy to share visual dashboards of reports and data models. You can also load Excel files and PowerPivot data models within Excel files. Now you can share reports and visuals that are on the The information side is when you want to share your results with people, but there is a completely different side of Power Bi online when we load data models from Power Bi Desktop or from Excel PowerPivot , become universally available to assigned groups of workers or here at Highline College. assigned group of students and teachers, that means that when you load data models, groups of workers can access the data from Excel or even from Power Bi Desktop.

This means there is one source of truth, one location for the data, no need to share files via email. Now let me. just show you, we already saw an example of this, if you go up to insert tables, dropdown for the pivot table, there it will be once you upload your data model, all your coworkers will have access to it, there will be no more emails on your file with the tables or the data model, just upload it here and then everyone in your different offices or here in different Highline classrooms just go to Excel, log in and 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, visual dashboards, dashboards and data models and all it involves is simply adding the emails for your coworkers or classmates and then you can share your data models, visual dashboards, reports and dashboards.

Now we'll come back to this Excel file a little bit later and load the data model and then we'll see how awesome it is to have this universal place to save data, but what we'll do next is take our data from Power Pivot. model from this Excel file and import it into a new Power Bi Desktop file and then we will look around and get to know Power Bi Desktop. However, before doing so, check s to save this Excel file and then close this Excel file before Go to Power Bi Desktop now. I already downloaded Power Bi Desktop.

I have an icon to open it on my taskbar and I can see the path of the folder with this file, which is the file that contains the data model, so I open a new Power. bi desktop file now here is the power bi desktop file, it has no name and has not been saved, so we use the same keyboard as Excel to open Save As f12 in the Save As dialog box that we are going to navigate to. our class folder and we'll call it that's what I'm going to name it, you can also name it like that and then click save.

There's our name in the title bar now, before we take a tour of Power Bi Desktop. to import that data model from Power Pivot, to do that we are going to import files and this option Power Query Power Pivot and Power View, we will click on the open dialog box, we navigate to our class folder and there is our file that contains PowerPivot . data model let's click open this message says: "Hey, I can't import everything from an Excel file. I can only import queries and what's in the data model." Now when we click Start here, we will get all the queries for that book. some we don't need, but we will eliminate them easily.

Let's click Start and the migration will be complete. Here are all the queries. Here are the elements of the data model. Let's click Close. Now we can take a proper tour of Power Bi Desktop. This is Power Bi Desktop. ribbon has tabs and here one two three those are the three areas we can work on in power bi desktop if you hover over this model it's like a diagram view in power pivot let's click on oh and there's our imported data model , it has everything, I think if we look around here we adjust things, the table, the fields, the measures, the relationships and there are some differences here.

Look at that icon for a measurement. It's like a little calculator instead of that f of x in Power Pivot and instead of a hidden gray field. There is an eyeball with a line through it, so this for us in Power Bi Desktop is called a model in the Power Pivot diagram view. Now if we hover over here, this is data, it's like the data view in Power Pivot, so when I click I can see the tables here is a list of tables that I can click to expand and I see the fields and measures. I can click f transaction and there's the table.

I also see the measures so the data is like the data view in powerpivot and here this is the report when I click on this it is like the worksheet the worksheet would contain data model pivot tables and charts from Excel and in fact you can put anything else from Excel into the worksheet here in the report view on the right, there are visualizations here that we can choose from. open tables and drag and drop fields and measures in our visualizations, there is a filter area, also some of the possible visualizations and reports, well, there is a bar, there is another type of bar, group, column, grouped column, there is a chart of lines, x y dispersion, there is an interesting map. a table, a slicer and this is similar to a pivot table, a matrix, there are also a ton of other different visualizations and getting more visuals is awesome.

You can download all kinds of amazing images so we have report and model data, but where is the power query? Well, home tab, all in data and queries, that's Power Query in Excel. We have the Queries and Connections panel to open queries here in Power Bi Desktop. Consultations. Transform data. Dropdown menu. Transform data. configuration, that same configuration in Excel is get data and until we get to the data source configuration, let's see the power query editor, it looks almost the same as in Excel if we select the transaction query, there is the name of the preview and steps applied now.

We don't need a bunch of these queries, we're going to collapse this folder before we can delete it, although we have to delete the query with a lot of files from a table because it's using items from that folder, click hold down the shift key, click summer. business scores 101, right click on delete, yes I want to delete queries, right click on folder, delete group, delete now here in Power Bi Desktop, it doesn't say close and load in Excel, we can choose where to upload it here in Power Bi. desktop we can only load it into the database in columns in the data model, so close means close the window.

Apply means apply the steps and load it into the database in columns. There's the dropdown menu, but for us we just click the close and apply button. the steps apply below we want to build our visualizations and reports we want to create this sales report page which shows the product sales of the region in a crosstab report also visualizes the sales for the sales representative using a column chart and then using a line chart we want to see the trends for the last two years, but what sets power bi apart is that if I want to analyze the trends of chantel and see the cross table with just the sales of chantel, I simply click and that's amazing, there are the trends of chantal and all region of the table and product, those are all the number of Chantal, which is approximately 3.9 million, effectively, that is the height of the column and if I want to remove the filter, I just click again.

I come and I am interested in Spitfire when I select Spitfire this is highlighting only Spitfire. sales for each sales rep and here is the trend, click again and the filter will disappear. Now the first visual we will create is a line chart. I'm going to click somewhere in the white area, go to visualizations and there's the line chart. we can resize it, make it a little bit taller and move here it has a different cursor and sometimes it's a little difficult, but it's that white cursor, look at that red line, it means I'm right in the middle, I'm going to leave it. go to the visualizations, when we are in the visual build, we can drag and drop fields and measures into these areas of the chart, so we have the year in month x below the year and then the total sales will go to the y axis now. before we format and do some other things, let's talk about this amazing area here, these three points give us more options, if we click, we can export the data of this particular visual as a csv file which is displayed as a table, yes we click on there's a table down here we have a back to report button that we can delete and the focus doesn't work here because we don't have any other images, but it would highlight this and gray out everything else and then we can uncheck the focus, we have the approach. which expands the particular selected visual, we can click back to report, we have a filter button that will tell you how it is filtered, if it has filters, we don't have any and then we have one, two buttons to go to the next level and one two to go deeper. buttons now we are already drilled down to the highest level if I select drill down this just turns it on so when we click on a particular data point it shows up at the next level all those data points that make up 2021 so when I click there is January to December for 2021.

I will go deeper. I'm going to disable this. Now I will go down to the next level. If I click on this for a year, this may or may not be an appropriate graph because it's every January, every February, etc. deepen this is probably the appropriate will go down a level andit will show year and month now we have the year repeated many times I think I would like to see just one these labels have been concatenated in format which is our little paint brush, there are different areas and if you are coming from Excel it takes a while to get used to where everything is.

We want to do something with the x axis. If this was collapsed, you could open values ​​and actually concatenate tags down here. let's disable that, we can collapse this. It would be nice to have data labels, so down here, data labels, we'll say now let's go back to creating visuals and here's something that exists in Excel in a pivot. table, if you hover over a point, it shows you a tooltip, but here in Power Bi Desktop we can do better than just showing the quantity that is on the vertical axis. Here is some tooltips. We can drag other measures.

Here is the number of transactions. and when we hover over they both appear in the tooltip, we drag the average down below the number of transactions and that's beautiful, each of these tooltips shows three measures and they are calculating the measures based on the conditions and criteria in right now so it would be March 2021 now when we create the tooltip that the title is too long and we don't really need it because we have total sales here so let's move on to the format and there's a visual and overall here's the title that we will select, so we have our line chart below, we want a matrix and I have to show you a problem.

I do this all the time. I want an array right here, but I accidentally left it. the selected line chart, so when I come to create a visual, I think I'm going to get a new array, but when I click on it, it's polite, I thought I want to convert that line chart to a matrix. Fortunately, control z brings us back. For our line chart, the trick is when you create a new image, make sure the white area is selected and now let's make a matrix, we can resize it that way and when we move it we can see those beautiful red lines and under construction. visual, it looks like a pivot table, task board, rows, columns, values, so we will take the region of f transactions that will be in the row, there is our single list of products, we will put it in the column, there it is the single list and We will drag the total sales to the values ​​and there is our crosstab report showing the region and the product.

Now let's click on the blank and create a clustered column. We want a sales representative. We can check this and it will go to the horizontal axis, our x and then total sales. to the and let's add a tooltip here also a number of transactions and average, we don't need this long title, so in general format we will turn off the title again in visual format, we have data labels and now when we hover we can see our three measures and this is one of the things that makes Power Bi Desktop so beautiful when I click on the line chart and so is the crosstab report.

Now we click on Chantel to remove the filter. Now we can control how these visual elements are displayed. we interact, we go up to format and then we click edit interactions so that we have the column chart selected, so I can decide in the other two visuals if I want it to be filtered or not. I want the same to be filtered here. I want it to leak. By selecting the line chart we can decide to filter or not, but here we get a third option highlighted and that's the one we want, that means when I select an item here like Spitfire, it highlights only the quantity leaving the total there for easy comparison and then this, of course, is filtered out when you click Spitfire to turn it off.

We can uncheck the option to edit interactions. On the right we have the filter panel and this filter does three things: You can filter on the selected visual. You can filter the entire page, that is, all visuals. or reports and you can have filters on all pages, so if you have new pages or tabs with other visuals and you report a filter here, it filters all of them if we select the line chart, here is the year for the selected visual, we can open it and select the logical test equal to what is and then type i want to see only 20 21 and then you need to click on apply filter button and bam only this visual is filtered for 2021.

To clear the filter you can use eraser now. I would like to do a few more things in this report. We want to add a title, a slicer, and a card that shows total sales. I'm going to move this a little bit to the left, we're going to move it down, we're going to move it all the way down. here let's click on the white and we will start with a slicer and we want the year, then from the dropdown we can say resize the list, now click on the white and we will have a card and we go to delete the sales measure totals, we can resize it, move it up a little bit, finish in format, call the value and we'll say decimal value, one click on the white text box, we'll try it and we can type Colorado boomerangs, that's the name of the company sales report control let's say 28 bold we can change its size there we go and click on the center to the side now we can select 2020 filter everything on this sheet 2021 and delete so that the sales report looks good but we want to create another page and show some different metrics for the region, then we'll add a new page, double click and let's call it region report.

Well, we click on the white, we go to our visualizations, we want a matrix and we go. to put three variables in the row we'll start with the f transactions region field and then the year we'll drag it down, if we check these they tend to go into columns so now I'm dragging the month under the year yes no yes no see these expand buttons, you can move on to formatting the row headers and turn them back on or off for output. We want to reduce total sales in values. In fact, we can verify these and the number of transactions.

We will expand on this. Let's increase the font size. to get the entire array to reformat the grid, sometimes these things are hard to find, you wouldn't think they were grid options, but sure enough, the global font size and I'm going to increase it to 12, resize it and bring it up to the final. At the bottom, we're actually going to have a title at the top, so maybe right there, right click to expand to the next level, that's how we'll leave it and then as we parse here, we can open up the years. Now let's click on the blank and we want two regional bar charts, one for the product and one for the sales rep.

Now we will see the difference between stacked and grouped. We're going to look at bar charts, but the difference is the same for stacked and grouped columns. column, let's start with the stack bar, the bars are always horizontal, now we want the region on the y-axis and then the product in the legend and then we will check the total sales. There is a stacked bar and it is called a stack because the elements in the legend are stacked on top of each other now you use a stacked bar chart like this when you want to directly compare the variable on the axis using the lengths of the bars it is very easy to see which of these regions is the largest and the smallest, so the emphasis is on the variable on the axis rather than the variable in the legend, but if we switch to the grouped bar now it is very difficult to compare the totals of the variable on the axis, but it is very easy to compare the differences between the product or item in the legend uses the heights of the columns and it is exactly true if we change to the stacked column, it is this variable down here that is emphasized by easily comparing the column heights, if we change to the stacked column, the emphasis is on the legend elements so we can compare within each region the legend elements now let's go back to the stacked bar now if you want to change the column colors, go to the bar format and there you can change all the colors, I'm going to leave these colors as they are, show this by the way, I like the title, I like everything, it came out pretty much perfect, let's add some data labels, a data label , so let's finish formatting the data labels and now look at this, here's a great trick.

I need the exact same chart, but all I'm going to do is change the element in the legend to make it a little less tall control c click on the white control v here we go and with this selected we'll delete the legend and see what happens? There is actually no difference between stacked and grouped when there is only one variable, but we want to go to our sales rep table and check the sales rep and now we have two beautiful charts, region, sales rep, region, product and so on That's how amazing this whole setup is.

If I want to look just to the northwest, there are all the sales reps and the products and it shows me the detail here. I can expand it to see a particular year. Click again. We can click on the outer edge as well to remove the filter now if we are. I'm going to use this as a filter and we just have a bar instead of highlight, let's edit the interactions and filter this. I think it will look better to format the interactions. We have this visual selected, so I'm going to come down here and say filter. and select this one and change it to filter now we can turn it off and now when we click on the northwest, that looks better, click on the side, remove the filter and if we select sue for the west, that looks good and the good thing is that if you add all those amounts together it is exactly equal to 0.62 million or the total down here now I would like to see an Excel chart, do this.

So if we have a data model on an Excel power pivot, we could also send it to the power. bi desktop and take advantage of some of the interactive images. Next, we want to add a title to the text box of our report. I'm going to click on the outer border to the effects and background color. I'm going to add a very light gray because The next thing we want to do is insert the Colorado Boomerang logo into the elements image and in our chapter 4 folder there are Colorado Boomerangs. Double click to resize it and it looks good.

Now there's one last thing. Actually, I'd like to show you something about the format. Now I'm going to change the colors here. I'm going to format down to the bars and I'm going to change this to a mix of blues. I am using all these related blues and it looks pretty good now I do all the formatting manually but in power bi desktop excel word powerpoint all the microsoft tools can be seen and I have no idea why this is not in format and there are themes now themes will add formatting to everything you can see through these you can also search 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 it now. I go to control z and we have our regional sales report finished. 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 appear in power bi online as a report that we also want to publish. Excel workbook file and when we publish it it will appear in power bi online as a workbook, but before publishing any of these files, you must have a power bi pro license or contract and you must be in an organization that assigns emails emails to everyone in the organization, like a company or here at Highline College, and the way you log into Power Bi online is that you have to use your institutional email, so here at Highline, students in the class Business 218 and the teachers and administrators they would have. use your Highline-issued email to not only log in to power bi online but also to power bi desktop and of course your excel file.

Now the dialog to log in to any of the files or power bi online is exactly the same, so we are going to skip and look at Power Bi online first before uploading these files. There are now many ways to access Power Bi online. One of them is to use this address. You can also access 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 here it reminds you of the organization's email, so I have to enter my Highline College email and this is the page where all of them will appear the applications and the website.

To do so, you have to go through your institution's login system. I'm lucky I have an easy password, it's just a bunch of dots, but when I press Enter it opens Power Bi online. Now here is the home page and you may have some recently used ones. reports or workbooks or workspaces here there are also some elements here, but what we really want to do is go to the workspaces and each of theseworkspaces is a location that has designated emails so that certain people can access each of these workspaces now. First I'll show you how to create a workspace that requires just a few clicks.

I'll call it Business 218 Fall 2022. Now I've added a description and uploaded an image. Now I click Save. Now we have no content in our workspace. still to add emails, we come to access and you enter the email addresses. Now I added a person from our entity, linda, and we can also decide what type of participant, if you want to get more information, you can click here and there is a detailed list. We're going to add contributors, which allows us to do all the things we want to create, edit, and upload reports and workbooks so you switch to contributor, click add.

We have two people in the workspace. We could add many more and, in fact, for this class I will. add all students in fall business 218. now let's add some content, which means we'll go back to those two files. Now you need to log in to both files, but once that's done, we save our file, we have our two pages and then on home we have the publish button, click and of course you want to know where the destination is, there is our destination, click select you are posting and if you don't have power bi online open they will provide you with a link you can click here to jump now.

It opens in power bi online and shows you the report that you just published, but here on the left we can click on our new workspace. There is the report which is the power bi file and the data model that was in that power bi file is called data. Set content that will show you your reports and workbooks, datasets and data flows that will show you your data models. Now we can also click to open our workspace. We won't have dashboards later, but that's one place we can pin. reporting stuff, there's our report, we don't have a workbook and there's our data model.

Now let's look at our report here in Power Bi online. Now it works the same as in Power Bi Desktop. We can click to filter and click to unfilter. On the left we see our pages. I select the region. I want to see the quad filters on the east perfectly and then override the filter. Now anyone with access to the workspace can view the report, but there are other options here. You can save a copy that is actually saved. Download this file online. This will download a Power Bi desktop file. You can print the page. Even if you have a code to embed on a website, click here and you will get the code.

There is also a QR code to generate here. Export, we are not going to use this because we will be opening it from Excel, but you can export it to PowerPoint or PDF. You can also share this report. You can copy the link and email it to someone directly from here. Even teams in PowerPoint now want to go back 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 be displayed as a workbook, none of the queries will appear and the data model will be displayed as a set of data that we can use in various ways.

Now in our second Power Query example, I added some formatting and a title at the top just to sort this out because in Power Bi Online we're going to pin this report to an online dashboard now save this and this is how we publish, upload to file publishing and there are 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 very limited version of Excel and of course you can pin workbook selections and share this is what I usually do. This will just load what's in the data model, but I'll show you this one, so before you click on any 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 load at the bottom you can see it is working at the top it shows us a yellow banner and we can jump to power bi online so i click i got this message but i am going to click ok, then we will go to see our workspace, we will open the dropdown menu and I don't know what that message said, now there is our workbook when I click on the workbook again, it will only show us the worksheets, not the queries, and opens in Excel online. This feature is just so we can see things.

You can see all the tabs below that were created with a query, but the query was not. here, but the result is that we return to pq2pt. If we look at the data sets, we now have two and we load the PowerPivot data model into our Power Bi desktop file, so these two are the same and then in Excel we will see that we now have access to these so we can use them as our single source of truth and create pivot tables in excel now before we do that we want to look at one last thing here in power bi online dashboards which is just a location where you can pin anything you want from any of the reports or workbooks job you have now, the word dashboard is very common, it just means a location where we can present information and metrics useful for making decisions, but dashboards are not limited to power bi online, you can absolutely create a dashboard in excel and of course In fact, this page that we created in this Power Bi Desktop report can also be called a dashboard, but when we are in Power Bi, inline dashboards are a specific location where we can pin important information and then share those dashboards easily.

In short, a dashboard is a place where you can present information and metrics useful for making decisions. The information is presented in an orderly and organized manner. Dashboards can contain tables, graphs, visualizations, data validation images and other data visualizations, and when new data arrives, the dashboard can be updated like a dashboard in a car, a dashboard should present the information needed 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 panel and we don't have any panel. but it will ask us to create one, so we go to the top.

More options. We want to pin this to a panel. We don't have any panel. So it's polite. He 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 it and that determines how it will look on mobile devices. There will also be this web view and there is a control panel, so I can click and sure enough, this is set to this panel. It works the same as if we were viewing the actual report.

I'm going to go back to our workspace and the regional report. We'll go ahead and fix this. one also pin to the board, our board pin is active now I'm also going to pin something from an Excel workbook, although the way it works is complicated so I don't normally do this, but you never know when you have something in a Excel workbook. that you want to share I'm going to highlight and this one is over here we see pin and it will set this selected range, we will put it directly into the Colorado reporting dashboard and set now we can go see our dashboard So this is working here, we have this one that is also working and here below we have an image of our report and chart that we created in Excel.

Now, if you click this, it jumps to the Excel workbook, so that part seems clunky to me. Let's go back 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 here above you can save a copy that saves a copy of the panel print it you can share it I'm going to uncheck allow recipients to create 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 invitation.

I'm going to click on it, open this panel now when you click open this panel if it's the first time you're seeing it on your phone it will ask you to download the app and then sign in using your organization email and sure enough I do click on the panel and there it is and I can't really give it a good view, but it's interactive. I opened this one. report and on the phone it's interactive as if we were in Power Bi Desktop or the web version of Power Bi Online, so I'm filtering and then removing the filter.

That's why Power Bi Online is so wonderful to share. Now, one last thing I use. Outlook online all the time and up in the left corner there is an application tile where you can go to Excel online, but that's how I normally get to Power Bi online, so it was a lot of fun with Power Bi Online and Power Bi Desktop. Now that we have two data models stored in Power Bi Online, next we want to look at how to connect to our online data models in Excel and Power Bi Desktop. I opened a new Excel file and saved it with this name in our folder.

I named it leaf and I'm logged in and you won't believe how cool this is. I can go insert tables into the drop down pivot table and there it is or I can use the keyboard remember alt n v which opens the drop down menu and there it is is b is for power bi online so tap b and look at this. A task pane called power bi data sets opens. Now I'm the administrator and I have all kinds of different workspaces there, so I have access to all kinds of data sets. but if you didn't see the data set at the top, just type 04 dash m365 and enter and there it is right at the top, this is from our power bi desktop file and this is from the excel file. to use the Excel data model and click Insert PivotTable and here is our list of pivot table fields and this is the data model that we are connected to online.

These tables were not imported. This is just a connection to that single source of truth that is online in power bi online, if you look in the data model, there is nothing there and this is good, we have the measures at the top of the tables three dimensions and that borrow field from the fact table is listed down here, now I'm going to select the year. The region sales rep year went to the column so I'm going to drag it over the rows and then check check check and there I've created a data model pivot table with that lovely number format that automatically comes from the dax formulas.

Another note, these were explicit measures. that means we are authors of these measures as we mentioned above, if you create implicit measures in Excel file or Power Bi desktop file, those measures will not appear when you connect to this live data model dataset from Power Bi online. We're going to control s and we're going to jump to a new Power Bi Desktop file. I opened a new file and saved it with this name and the data source. Here we are going to get data and there are the Power Bi data sets and we notice it. It says create a live connection to data sets in the power bi service, so I click and I see the one I want right at the top, so I'm going to double click and here in the report view we see all of our tables, including transactions f with one field and three measures, now I'm going to pause and create a new page, you can create any page you want and then I created this dashboard, click and it will be all filtered, click again without filtering and if we go to the model, it shows us an image of the model, but that data model is not actually here, well we have a model and a report, there is no data tab because the data is not here, it is still stored on the website power bi online store, so it's a pretty good use for the power bi online store. an online data model and access it from anywhere now we're back in our original excel file we're on the refresh sheet eight and nine and this is a map of our data connections for example eight and nine here it is the file this file has source data, the Excel tables which we then import into this file, then we build a data model and from Power Bi Desktop we import it into this file.

Then when we published both files to Power Bi online, those data models were stored online and then we connected. both files to online data models, that means if we add some data to this file and update it everything will be updated, so let's open this file, here is our file, we have one, two, three, four source tables and We have two things to do: add new transactions for a new year to the fact table and then we will update the date table. Click a cell. Control the down arrow. Move to down. I put new transactions here.

Click on just one. cell does not includethe names of the fields control the asterisks control c and below the table we control v instantly those records are incorporated into this table object control start now the date table must have a unique list in this first column of each possible date of the fact table, well, right now, if I control the down arrow, this table only goes until the end of 2021. Now I'm going to show you a great trick here when you have your date table in the Excel worksheet. Now these are actual dates, but look in the formula bar, the month and year text, these are all formulas, this is an Excel table, so if I can quickly add the whole new year of dates, everything will be copied automatically.

Here's a great trick: select the last cell with the last date. the fill handle hovers over the cursor and when you see the crosshair cursor, you're not going to click, you're going to right click, so here we go, right click, drag a cell down and when you release it, you will get a secret dropdown menu. go and there it is down in the lower series. Now there are other ways to get series, but this is the fastest. I'm going to click and whatever you do, don't leave them rows because that will trigger the dates on the right that we want. to knock them down the columns, the 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, it's a beautiful thing that uses a series of formulas of tricks and Excel. table and that's it, now we have to save this control.

Two of our tables have been updated. I'm going to close this file now in our Excel file, let's go to the rdm dax worksheet, the data was imported using Power Query, this is all coming from the data model, so all we have to do is click right click on refresh and there we go, there is the full new year and all the quantities have been updated in our power bi desktop file, we go up to home queries and click on refresh, it updates and there it is new totals now I had to adjust the font size, but it looks good, let's go to the sales report.

Look at the trend and for some reason June and July DST was nothing but now it's a big jump in the controls and now we click publish to send the new version to power bi online we select our space work we select it is polite it asks us if we want to replace and we do it we need to republish our excel file publish make sure to select the workspace and upload it definitely want to replace and then the beautiful thing ends in power bi online when I look at our dashboard , I see that it is updated, so it is now when we open our power bi desktop file that was live connected to power bi online, it is already updated in our excel file that is connecting to power bi online, we have to do right-click Refresh and there are our new data controls, so in examples eight and nine we learned all kinds of things, how to use Power Query to import data, how to create data. model import a data model from Excel to Power Bi Desktop, upload it to Power Bi Online, and even connect to Power Bi online datasets.

Now in the following example we will use Power Bi Desktop but we will connect to an online database with 7 million rows, let's go to sheet 10. In this example we will use Power Bi Desktop and Power Query to import 7 million rows of sales data, which is a lot of data for some of you in this class. like we never get that much data, well you do get that data for big companies, seven million rows isn't even big and then we're going to create an interactive gross profit dashboard, now let's move on to the next sheet and let's talk about what we are going to find when we import the data from a structured query language which is an SQL database.

We will have a fact table with the date of the 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 was the discount on income? For this transaction here, for every dollar, the customer got 35.3 cents off.for every dollar and then there is a net standard cost, in this case for every dollar of lookup table cost, the charge is 1.03 , so a dollar three means the price increased three percent here, the price was the same down here, 0.99. It means there is a one percent discount.

Now it is important to understand how these columns work because we are going to have to calculate revenue, a synonym for sales, and the cost of each transaction. The next table, the dimension table, is what is called the primary key country. code that will connect with this foreign key, this is the one to many side and then this is the product table with the retail price, standard cost and category for the foreign key of the product primary key, this is the side of many, this is the side that we I will have to find and use both the sales price to the customer and the cost of the company on the expenses side, on the costs side, how do they calculate expenses now, this table It has 22 rows, this is 126, but it is 7.7 million. rows now let's talk about the column database because when you have 7 million rows, that column database does the impossible because you could never have 7 million rows in an Excel spreadsheet, it makes the impossible possible now, just As a small example, this table here has 606 rows, it has four columns, which means the total number of cells or data bits is 2424, but what a columnar database does is take each column and store them as individual columns, but it only stores a unique list, so the product id will have 7.7 million rows, but when stored in the database in columns they will only store the unique items.

Now there's also a map behind the scenes that determines how many rows there are that you can use to rebuild when you do calculations, but on the storage side, our table here that originally had over 2000 rows, when we count all these unique lists and add them together , there will be 621 bits of data or cells in the database in columns, now this is only with a table that has 600 rows. So you can imagine that with 7 million rows that columnar database will compress and store it efficiently. Now let's move on to the next sheet. This is a diagram that will allow us to calculate the sales of a transaction that we are going to have. use the product dimension or lookup table and we're going to have to use the fact table, so for this yellow transaction here four units were sold, revenue discount only got a discount of 1.9 cents on the dollar , so in the formula take the dollar minus the discount, then in our formula we will have to search based on this foreign key in the many sided product name, we will have to search in the dimension or lookup table for the aspen product and then return the price to the formula and this is how we will calculate the sales for each line in our table.

You can check this out if you want and this will be our formula as we're going to do it in the data model that We're going to use dax formulas now we know how to do it related is like x search x search is more complicated when you do it related to look up the price of a product because there is a relationship, you just have to say what you want, that's it and then we multiply by one and that is the name of the column that has the income discount, so that's really this part and then, multiplied by the column name for the quantity and of course here d retail sales of the product, that's this dimension or search field. table now, the last thing is the credentials, we will log into a SQL database and extract the data online and the best thing about storing the data online is that it is not a premise, we don't have to worry about those text files. or Excel files and what is the file path, Power Query will always be connected to that online database which will not be moved, but to get access we will have to use the server database username and password of credentials.

Alright, now let's open a new Power Bi desktop file. Our goal will be to create these seven measures on this gross profits page and using a visual map will plot the gross profits around the world. Alright, let's start our SQL Power Bi Desktop Adventure by opening a new file saving it with a good name and the first step is to get the data, so we go up to the start data. We could go get data from SQL server, but there's a big button here, so I'm. Clicking on this SQL server database we have to enter our credentials the server is Pawn Dot Highline Dot Edu tab and the database is lower case let's do a direct import bring the data here let's do Click OK, make sure to say database on the left, the username is excel, it's funny, all lower case and this is where most people get into trouble.

You have to write it correctly with the correct capital letters. database now we are going to click connect, this is not encrypted, so we click OK and just like in Excel, the browser window appears and we can choose between several objects, do not import the calendar table d, It doesn't have the correct dates. we want d country d product and f transactions we don't click on load we want to click on transform data now there are three queries on the left f transaction is selected there is our preview the name is perfect and we have these two steps now we have already checked these columns and the data type should be perfect because it comes from a SQL database but we still need to check it and of course the revenue discount is not correct so we will add a step click abc and we definitely want a decimal number as our data type. added that step which we'll go through and when you import data from a relational database, which just means there are relationships, these extra columns come 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 on the first one, hold down the shift key, click on the last one and right click on delete.

There is a new step. The data types are correct. This table looks good. Now we click D. The product data types are all good and this is a column with each one. transaction table transaction f for that product relationship is what brings that table but we don't need this column so right click to delete the name and steps are fine. d country looks good, right click to delete, those are good now that we are. 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 database into columns so that all these millions of records are compressed into a lot of space. smaller and there it is done.

Let's see the data and on the right are our tables d country d product and f transactions if we are going to model it, we already put the relationships there is our f transaction d country from the one to many side of our products table, we have a single list of products from the one to many side now what we are going to need next is a date table and a date table should contain a unique list of dates for each possible year from the date field in the fact table if we look at our tables d country does not have a date column d product does not have one either and in transaction table f we have a single day column and when you only have one date column in the fact table and there are no other date columns in any other tables that will allow us to create our date table in a much easier way.

Now we'll create the date table in a moment, but this is our fact table and we used Power Query to import it into the data model, but I love you. To notice that when you are in data view and you select a column in the preview of this table, we can change the format. Now this is the same in PowerPivot. I also want to display this date as a short date. I don't want to see the name of the day. Also note that here in the data view preview you can change the data type in 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 will have to use this function to make sure that the data type is correct. Now let's create our date table using dax formulas and the way Do It: We can move on to the table tools and this is different than Power Pivot. PowerPivot. We can create measures and calculated columns, but we cannot create tables here in Power Bi Desktop.

We are allowed to create Dax tables and them. will be loaded into the data model so let's create a new dax formula to create a new table in the formula bar we write like in PowerPivot naming operator formula the name for us will be d date and,luckily the assignment operator is just equal sign instead of powerpivot where we had to make two points equal, so there are two calendar functions, calendar is what you use if you have multiple date columns and then you just specify the start and the end and creates a single list but when we only have a date field in the fact table we can use auto calendar if it had a fiscal year it would include it but we don't so it just doesn't enter anything and when I press Enter, search the data model.

It finds the minimum and maximum date and gives you a unique list for each day and each year that it found in the date column in the fact table. Now we select the date field, the name of the auto field is date, we have to set the data type here in the data model because we didn't bring this in using Power Query, we definitely want this as a date and now I'm going to format it as short date . If we look on the right, now we have a new table and it's in the data model, let's open it, we have a field now, when you have a date table, you have this single list, but then you can add as many attribute fields as you want if we want the year and month in our reports and visuals that we have to add them to.

To date table you can also add many other date attributes like fiscal year, fiscal quarter, week and many more. Now we want to go back to the table tools because this button here is really important for date tables that we saw in Power Pivote when you mark a table as a date table, then the automatic date table creation feature is disabled. 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 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 fetch it. in them, so I'll make sure to mark this as a date table and then tell it where the date is from the primary key or the unique list.

Okay, now we can add our additional fields or columns, let's say new column above. in the formula bar we definitely need the equal sign of the month number and the function is the same as in Excel month and we want to access using the row context to the date in each row, so I say dd down arrow, there is the date field tab, close the parentheses and enter This is the first time we've created what's called a Dax calculated column. This here is not a column calculated by Dax. This was a one column table, but it is a column calculated by Dax.

We can see the formula in the formula bar and Dax. Calculated columns are calculated differently than Excel worksheet formulas and Excel table formulas. Notice here that that is the full column reference. If we had a column in the worksheet and used formulas from the worksheet, how would we access each row? We would have to use a relative cell reference. if we were in a table column we wouldn't have the full column reference, we would have the field name in square brackets with an at symbol, but here in dax calculated columns and also later we will see the dax iterator functions that both use. a full column reference and then behind the scenes it is calculated using the context of the row and the context of all the rows means that for a full column like this you can see every element in every row, so from now on when let's create calculated columns with dax or post iterator functions, We will simply put the full column reference and row context we will get the element in each row now for each new column we create, we have to consider the data types in the tools columns.

I'm going to check the data type now, okay, but that's how it is. a number, if we were to drag it into one of the reports, I would try to add it, but this is one of the fields that we are going to hide, so back in the new table tools column, this will be the month and meanwhile in Excel . you use the text function to create a month name or other possibilities like day name, but here at dax they decided not to call it text, they call it format and then what format would it do?

We put a value in the down arrow tab dd to get the full date column and I want to format each one so I type a comma there is the format argument and then in double quotes m means month d means day e y means year now look what happens and it has to be in double quotes, but I'm I'm going to put 1 2 3 4 m's for month n double quotes, 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 back space and I just put three m that will put an abbreviation three. letters now in the column tools, we need to check the data type, text is fine, just like in PowerPivot, this would be sorted alphabetically and look at this.

I'm going to go to report, click on the white and all I'm going to do is check the month and when I do that, it's going to put it in a table which is effectively alphabetical, so we go back to the data and we have to say the month so that it is sorted by month number, so we sort by column again and we definitely mean sort that by month number, you won't see anything here, but in the report view that is sorted chronologically exactly what we want to go back to the data, now we can add our date attribute field nextyear we will click new column above in the formula bar, the name of this column calculated by dax will be year and the function is the same as in Excel year d d down arrow to get the column tab completes and when I close the parentheses and press enter the row context sees the date in each row and gives us the year, now the data type is an integer.

If we accidentally dragged this to the wrong place it would create an implicit measure to add the year, which is not what we want, we could add the text data type and then it would be left aligned, but better than that we can zoom in on the properties and say summary. I don't want you to summarize, that means the whole number is fine. Now we have our date table. To the right is our full date. table look at the icon for calculated columns of dax f It is the only list among many because we hope to have many sales every day.

The next task is to hide the columns that we do not want to appear in the report view. The month number is just an auxiliary column for month, so right-click Hide in the report view. Notice that it is different than in Excel PowerPivot. There is Hide from client tool in fact table. We don't want any of the foreign keys, country code, date and product. We also don't want any of the foreign keys. the numeric fields, although we are going to use all three in the formulas, so I still have control and we are going to hide them all.

Remember that if we don't hide them and leave them in the report view, someone could drag and drop them. and create an implicit measure right click hide in the report view country code right click hide now we are definitely going to use the retail price and standard cost and formulas, but we don't want them in the report view select right click hide in report view We are going to use both in report view so now we have imported the data using Power Query, created the relationships, created a date table with a function of Dax table and then we calculate the columns with Dax and then we hide the fields that we don't want in the report. see now let's create our dax one dax calculated column in the fact table and then a bunch of dax measures let's go to the data select the transaction table f now here is our fact table and when we imported this table it didn't appear with a field that shows us the amount of revenue or sales for each line or row in this table, we also do not have a field for the cost of goods sold, no problem, we can create both and in fact we already saw the formula. to calculate the income before starting the project now I will show you two ways to calculate the income column one is the two step method the other is absolutely amazing and makes Dax amazing it 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 works in each row and then I'll show you the one-step method now to calculate the income notice.

We have the quantity, that is the quantity of the product. They bought and we have their discount, but we don't have the price right, the price ended up in the product table d. Retail price is used to calculate revenue and standard cost is used to calculate cost of goods sold, which is how much each product costs. Just so there is no problem, let's move on to f transactions and because there is a relationship between those two tables and the product appears in each row of the fact table, instead of using x lookup, we just use the lookup function dax related now.

I definitely want a new column and we are going to call this line because we are going to have two income formulas, one to add them and this one is to calculate the income of the line or row and I am going to indicate the dollar sign equal sign and we are going to build this formula one part at a time right now all I want to do is look up the price of each row in this table, let's use related and notice that all it says is give me the name of the column if I put d product and retail price here because there is a relationship that this search function knows every time it sees by row context the product to get the correct price, it closes the parentheses and when I press enter the row context it gives us the price in each row now this table is so big Yes you try to scroll down and find a different product, you won't, but remember the magic of filters that we learned at the beginning of this video.

If you click, you will get a single list and sure enough, this field or column has so 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 in the formula bar, we can put the full column reference. so it is from transaction f and again there is quantity, this is the full column, but the row context will extract the quantity from each row when you press Enter, that is the revenue amount without the discount. Now they give our discount in terms of number of cents per dollar so we want the amount that they pay so in parentheses is the dollar and subtract the transaction discount and then the revenue discount close the parentheses and this is the formula that gives us the income line after the discount now down. below there are some definite decimals that we need to round so before we relate we use round we learned to round in a previous video at the end comma 2 because we're going to close the penny parentheses and enter and that's our dax calculated column formula now If we look transaction f, is the calculated column.

Now when we create the calculated column line revenue, we can see it here. Here is the column that is step one. Now we need to create a measure to add this entire column so we can use it. Measure in report view. Now there are a few places where you can create measures. If you are already somewhere in the table where you want the measure to appear, you can access the table tools, New Measure, but as always you need to make sure you have got used to selecting the correct table because I usually have to come and select the table and then create the measure.

I simply click on the table and right click on the new measure which is right at the top of the formula bar. I'm going to call this measure total revenue and I'm actually going to add the sum function name to this because in this measure we're going to use the sum function in our one-step method, then we're going to use just the total revenue and that's the form we will use in the report view, but inside the sum ft that is the transaction table f and guess what I can use my calculated column, so two steps create a calculated column and then create a measure to add when I press enter, this measure will aggregate Throughout this field we could also have used other aggregate functions like average minimum or maximum.

Now I want to add some formatting to this measure so that when we place it in the visual it will have the correct format now in Power Bi Desktop in the data or table view. we can't see the result of the measure, we see the measure down here, so what I usually do is zoom into the report view, we don't need this anymore, so I'm going to delete, select a table and then resize it to measure that I create each measure. I'll display it here in the report area to make sure it's calculating the correct result andI'll add a field so you can see how the measure works across all the fields. and it will calculate the total at the bottom.

I'm going to check the total income and bam, there's the two-step method. We create the calculated dax column, the measure and there are all the product revenues with a grand total at the bottom. if we were in Power Pivot on the right, we would see the grand total in the measures grid, but here in Power Bi Desktop, when you are in data or table view, we don't have a measures grid, so now that was the two-step method. we can see the amazing one step method. Now remember what we did with the two-step method. Here is the transaction table f and we attach next to the transaction table f a calculated column and then we create a second formula, a measure to add.

Well, in the one-step method we're going to use an awesome iterator function that simulates the row-by-row calculation called sum x and in the first argument of sum x you simply say, hey, which table would you have put the calculated column in so that the first argument? It will have f transactions and then you put in the exact same formula and I'm going to copy this into the second argument of sum x and then sum x performs both steps within the formula; It will actually calculate the 7 million values ​​as if we had a calculated column which is the x part, the iterator part and then the sum part adds the seven million rows to get the total revenue.

Now I'm going to press Enter, zoom into transaction f, right click on new measure, total revenue, equal sign, the sum part will be added. part calculated column control v when I close the parentheses, enter, add some formatting, come see the report, the table is selected, I check the total revenue and you must be kidding me, exactly the same, but with the one-step method no we had to add that calculated column now the difference between these two methods let's go back to data or table view with two step method when you create a calculated column these values ​​are stored in database in columns which means which will take more storage space in the database in columns but these values ​​are only calculated when you create the column or when you update the table, each individual value is not calculated in the report view when we drag and drop the sum measure on reports or visuals with the one-step method, you don't have to store anything in the column database, so it doesn't require additional storage space, but each value in this measure here, which is this entire column of 7 million rows, all those values ​​are calculated inside the formula every time you put them in a visual or report or when you change the conditions or criteria so that The method should be used well, it really depends on preference, since the Dax's grandmasters, Alberto Ferrari and Marco Russo, say a hundred million rows or less and there is not much difference in performance.

Now the rule I use is this one in the report view. Now I am going to uncheck Both measures and performance only means how fast it calculates the measure and gets the answer, so we will add both measures and see if one is faster than the other, so first I will check the two step method like this to verify. Well, that was quick, now let's do the one step method. Well, that was quick. They look exactly the same and my rule is this: if I start dragging and dropping a one-step method and it calculates too slowly, that means there are some calculation-intensive steps in the formula. and maybe you want to move it to a column, what that would do is allow you to calculate once when we update or create the column and then you wouldn't have to recalculate every time we use it in the u report, but again if we only have seven million rows like we have, it probably doesn't matter what method you use now, there are ways to measure speed, there is a method within Power Bi Desktop and there is also a program called Dax Studio with those tools that you can measure performance, but we don't I'll need to do that in this class, but most of the time it doesn't matter.

Now I'm going to drag this 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 the table view or in the report view. The advantage of doing it in report view with an additional sheet like this is that as soon as you calculate it you can see the result, so we'll pretend this is our measurement grid. in power pivot now i am going to move to f transactions, right click on new measure in the formula bar, we are going to create the total cost of goods sold in one step, this measure here will be similar to our total revenue we need to calculate the cost of each line item of goods sold into the fact table and then adding that means we use sum x the table of course is our fact table f transactions comma y expression is the formula we would use in a calculated column now We will have to round each quantity, we will have to look up the standard cost in the product table d and then in the transaction table f, whatever the total number of units and the equivalent net cost, so I'm going to type round to get the price we are going to use related, but wait a second, I thought related had to look good at each row in the fact table when you use sum x, remember this is exactly like a calculated column, so when we put d standard cost of the product close the related parentheses, we will see each row in f transaction, of course you will see the product in each row and through the relationship you will get the standard cost, so we will have our cost multiplied by the transaction quantity times f standard cost transaction net now this is not the discount when you have a discount you have to do one less when you have net cost they already did that calculation less or more for you so in the formula we only use that column now that is the formula for rounding now we can look the hint on the screen to help us because we are a couple of functions deep comma number of digits to round to 2. now look when I close the parentheses, the round is completed, that is the expression or the column calculated now I have to close some x when I press Enter, add some formatting and come and check the total cost of goods sold.

There's our total cost of goods sold for each product and the grand total at the bottom. Now our fourth measure that we want to calculate is a metric called gross profit and gross profit is revenue minus the cost of goods sold manufacturers and retailers like this metric because it takes total revenue minus the total cost of the product, the cost of goods sold does not include all other expenses in the business, its income minus the one-time cost of goods sold, so for example, this is a boomerang manufacturing company, there is a quick gel, there is paint for wood, paper of sandpaper, packaging instructions for maintenance of the machine and maybe a few other things, plus, all of them together constitute the single cost of goods sold, so when we subtract these two things, it gives us the gross profit. sometimes called contribution margin, that means the amount left to cover other expenses and profits, so we right click on the new transaction measure, we will call it gross profit and wait a second, we can take one measure already created minus another, Now look at this.

We are not going to include the square brackets of the table name or the measure. That convention is reserved only for columns. When we type a square bracket, the Dax engine knows that the convention is to use square brackets only for measures, so we can use the down arrow to get the total revenue tab. less brackets, I see the total cost of goods sold, so the tab is our formula. Enter, you better add, enter some numeric format. Now I'm going to go check the gross profit and so for the alpine product, the gross profit was 32 million, now a couple.

A better metric is to take gross profit as the numerator, total revenue as the denominator, and then that's called gross profit percentage, which tells you how many cents on every dollar are left over for gross profit, which is all the other expenses and profits, so right click on new measure. and instead of using the division operator in dax, we can use the division function, it needs a numerator, so in brackets, that will be the gross profit, comma, it needs a denominator, in brackets, the measure of total income. Now we could put a comma and put an alternative result, that's what you put in the cell if there is a division by zero error if you leave this omitted, which means just close parentheses then if there is a division by zero error in the report it will look like an empty cell close parentheses enter now this is going to be a percentage, so we'll add the percentage number format.

I want two decimal places. I could come and check, so crested butte has a 37 percent gross profit, so for every dollar 37 cents left for gross profit, while carlotta doubles a 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 on new total units measure without dollar sign because this is counting some transactions f and up quantity , which is the number of units sold for each transaction. in parentheses and I'm going to make a comma, we definitely want zero decimals because this is counting, we come and check the total units and there is our last measurement and now let's go to the model to admire our handiwork in the transactions, there are a lot of measurements that have our columns hidden our only column where we could include in a report the dimension tables calculated table calculated columns and hidden fields now often the hard work is creating the data model, but getting it right makes viewing, reporting and sharing easy and cash now we can go to the report view, we are going to add a new gross profit report sheet.

I'm going to click on the target. We want a line graph. We'll put it at the bottom. We will put the year in it. x month under the year and we want three lines on our chart, we definitely want to see the total revenue, the total cost of goods sold and the gross profit percentage now, whenever you have a measure like this, this is a tiny number between 1 and 0. These are huge millions. or billion dollar numbers, so when that happens, we go to the measure on the y axis, we click on the dropdown, we move to the secondary y axis, we can see the label here and the two labels here, we definitely want to go down this two levels and show year and month in x axis format we don't want to concatenate that looks much better overall I don't want to see a title that labels this label everything looks good really a graph like this is much easier on power bi desktop than it which finished in Excel now I hit pause and created this second visual, a stacked bar with category on 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 seems like we have a seasonal pattern, Christmas time, Christmas time, Christmas time always ends and over time Christmas sales get 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 the products together, but over the years the trend is downward, from about 44 to 40. That means that during the four years, if the gross profit percentage goes down, they have less left over to cover other expenses and products now if we get up here, if we want to see how this graph changes depending on the beginner, we can compete. They seem like different details, but the general pattern remains the same. The season is Christmas and it seems to increase over time and the gross profit percentage decrease over time. now we can remove the filter, we want to create one more page, we are going to call this page gpmap, we go in, we click on the white and we will see this amazing visual image, a map of the world, which will now show the whole world, often data. the sets will have longitude and latitude, we don't have that here, but it comprises descriptions or names like city, state or country, we have sales worldwide and we have country, so if we drag it down to the location those points immediately appear.

They are all the same size, but we want to see the gross profit, so we will drag it to the bubble size and instantly we can see that all the bubbles are different sizes. The funny thing is that you would think that Australia would have a huge bubble, but it doesn't, it seems that the United States has a big one here, this is Europe if you hover, it seems that Germany 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 onFrance there is a 10-day world boomerang throwing championship.

The US has a couple of teams, certainly France, Germany, Japan and Brazil, so there's our map to compare a particular metric, so we made a page with a map that We made a gross profit report, we had a page where we test all our measurements and in the model is our data model. Now we're not going to post this or share it, and in fact, the main thing we learned in this example is how to import. big data and create a data model here in power bi desktop. That was the most epic video I've ever made.

Three and a half hours. Your one-stop shop for Excel and Power Bi data analysis. We cover all these topics so that you can become amazing and have fun efficiently using Excel and Power Bi. Okay, if you like that video, make sure to give it a thumbs up, leave a comment, and subscribe because there are always a lot more videos coming from Excel. It's fun, okay, see you. next video you