 Hello and welcome to this video, actually this is not a new video, it is a compilation of 17 videos that make up the full series of power query basics. If you are following our videos in this channel, this is covering 17 topics, full course, the length of this video is 5 hours, yes 5 hours and 25 or more practical examples. The content of this video will be divided into 3 parts. Part 1 are discussing extract data using power query. Video PQB01 is an introduction, PQB02 how you can extract data from the current workbook, 03 how you can extract data from other workbook and consolidate multiple worksheets, PQB04 how you can extract data from a folder and this folder contains CSV file, PQB05 how you can connect to an access database and load your data to Excel data model, PQB05 exactly the same like PQB05 however we are working on Power BI and at end we are going to create an interactive dashboard on Power BI, PQB06 how you can connect to the web using power query. Part 2 is discussing the data transformation, 7 topics of 7 videos, PQB07 how you can split columns 2 rows and how you can automate and organize chaotic input data, PQB08 append data from 3 different tables, PQB09 through PQB13 we are going to discuss merge queries we are going to start with 09 how you can merge queries and replace VLOOKUP using power query 10 how you can compare between 2 tables 11 how you can solve the duplicate issue when you are using merge queries, 12 how you can replace some if and 13 is how to perform multi dimension or 2 dimensional search using both formulas and power query and for sure using the merge techniques. Part 3 is the main theme is about how to manage queries we're going to look at excel.currentworkbook to extract data and group BI to summarize data this is in PQB14 15 how you can create a dynamic filter using power query the criteria inputs are going to be from the excel itself from the excel sheet itself and then you can also create a customized refresh button and finally we're going to look at duplicate reference or append in video PQB16 and when and how you can use each and every one of them before leaving you with the first topic let me tell you how we can navigate through this long video if you go down in the description if you hit the show more button the full description will be opened you will notice that I put the titles of each and every video on the left of each title of those videos you will see a timestamp if you click on this timestamp it will take you directly to this part or this topic and below you will see that I put also a link that you can use it to download the excel workbook or the working files and finally if you didn't subscribe to the channel please do like the video if you like it and leave me a comment and I hope that you're going to enjoy this very very long video of five hours thank you very much for your time and see you in next videos Welcome to a new video from excel did analysis series PQB power query basics this video PQB01 introduction to power query in this video we are going to try to answer these three questions why power query where we can find it in excel and how power query editor works so before jumping into excel and starting our practical example for today let's have a quick discussion around these three questions first one why power query there's a lot of reasons why we should use power query however I'm listing only three reasons here first one is because power query will help you to extract data from almost everywhere text files csv files excel workbook folders web and online services relational databases such as access sql and many many other sources the second reason is power query will help you to to make your work without using vba or macros and that's why because in the power query editor that you are going to see in a while you will find a section called applied steps and in this section you can find that all the steps that you applied to your query is recorded and you can reuse it again and again either in the same dataset or on another similar datasets the third reason is with power query you are no more limited to the one million rows limits in the normal excel sheet and guess what you should not also worry about the performance of your excel sheet you know that if you are working with 30 or 40 south end records in the excel sheet with a lot of formulas like sumifs and vlookups and all this stuff the performance of excel is very slow however with power query I promise you you will not be worried about the performance even with an excessive or massive number of records now we want to answer the question where we can find the power query in excel it depends on the version that you are working with if you are working on excel 365 like me you will find the power query in the data ribbon you will not find the term power query itself you will find something in the data ribbon called get and transform and in this section you will find everything that you need about power query this is pretty much similar to office 2016 also in the data ribbon you will find a section called get transform but if you are working in excel 2010 or 2013 you need to download the power query add in and install it and in this case you will find in a different ribbon called power query and all the icons you can see it here in the power query ribbon and finally how power query editor works the power query editor is working in a separate window this is the window of the power query editor and we are going to take a quick orientation while working on our practical example but you need to know that the power query editor is working in a separate window you cannot work in your excel workbook while working on the power query editor you have to close it first and then continue working normally in your excel workbook in our practical example today we have a small set of data it comes in column b as you can see here it is also a sales data you have here the dates you have the amount you have the percentage and also you have the sales repname but all of this data as you can see in the formula bar all of them coming in the same cell and there's a separator or the limiter between each kind of data so the dates and then you have a space semicolon and another space then the amount space semicolon space and so on and so forth and what is required we require to prepare a pivot table this pivot table reporting the sales by sales repname as you can see we have here the data for the year 2017 but also we have here another set of data for 2018 so let's start directly with the power query but before start working with power query i need to transform this data into a table format so it will be easy for power query to understand what kind of data exactly so while selecting any cell inside the data set i'm going to use the keyboard shortcut control t it will open a small dialog box confirming the range and ask you if you have headers for this data my answer is okay i'm going to press on okay and you can see the set of data changed to table format in the table design ribbon i need to give a name for this table and this is very important while working with power query you need to make sure that you are giving a meaningful name for every table or every set of data i'm going to to call this sales and enter now i'm ready i can go directly to data ribbon and here from get and transform section i'm going to use this shortcut if i hover over this shortcut you can see here from table or range i'm currently working on a table and i'm selecting a cell inside the table so power query will understand exactly what i want i'm going to click on this icon the power query editor will launch now the power query editor launched and there is four main sections in this editor the top the top portion of the power query is your menus start with file home transform add column and view we will not discuss each and every menu of these five menus however in this example we are going to use some of these buttons and some of these options and we'll discuss each one of them in details the second section on the left hand side there's an arrow here if you click on it it will expand you can see this pane called the queries it lists all the queries that you are working with in our case now we have only one query called sales inheriting the name of the table until you want to change if you jump into the right hand side you will find the query setting sections the first part of it is the properties and you can see here the name the sales as we mentioned we can just call it something else we can call it something like sales and sales rep and enter now we renamed the query and if you look on the left hand side you will see also the name changed here and this is the important section as we mentioned at the beginning the applied steps the first step always the source and if you click on it you will see the source of your data where you can see this you can see it in the formula bar it's pretty similar to excel this is a function called excel dot current book book and you can see the name of the table which is the sales and what we bring from this table the content of the table and in the middle is a preview of your data and I'm saying it's a preview because it is a read-only version of your data so whatever changes you do here it will not impact the original data although the power query is based on a great language computer language called m code however in most of the cases or I can say 95% of the cases you are not required to write any code why because this power query editor or this interface any action you take it will generate the code automatically and you can view the code in two places inside this editor the first place is the formula bar and you can see when I select the applied step change type it generates automatically a function called table the transform column types and here is the parameter of this function if I want to view the entire code of this query I can go to the view ribbon and from the advanced editor button I can click on it and it will open the advanced query editor and you can see the code the entire code of this query written here now let's start transformation required for this data and the first thing I want to do is to just get rid of this action so I'm going to use this red x and I'm going to delete the first thing I want to do is to just split this data into four columns to do this in the power query while selecting this column I'm going to home ribbon and from home ribbon you go to the transform section and you will find a button called split column the small arrow here if you press on it you have all the options for splitting column in our case we want to split by the limiter and our limiter is three characters space and semicolon and then space so there is a standard the limiter is here but not none of them is space semicolon space so I'm going to use custom when I select custom it will open a small window I can write here space semicolon and space and also power query asking you the occurrence of the limiter for me it's for each occurrence of the limiter I want you to spread the column at each occurrence of the limiter and when I press okay it will automatically spread the data as you want exactly not only this the excel trying to be smart and there is a change type action taken this column the first column is date and you can see the icon changed here and you can see something like a calendar and if you check the data type from the home ribbon you will see the type is date it's also detected that the second column contains whole numbers however in this case I want to change I want this data to be decimal number not whole number so I'm going to tell the power query I have decimal numbers here I can do this action from two places from the column itself from this icon I can just click and I can choose the decimal number or from the home ribbon from transform data type I can just click and use decimal number button and you can see it is very polite it is asking you if you want to replace the current step or add a new step for me I'm going to replace the current step and now you have all the transformation for data done now I want to rename each and every column of this query so I'm going to double click and I can start to write now I think I did all the transformation required for my query or my data and now it's time to load as we mentioned the power query is etl tool extract transform and load now we finish the extraction and also we finish the transformation and now it's time to load so to load I have from the home ribbon on the left hand side a button called close and load if I just click on it it will load the data into excel table however I prefer all the time use the close and load too it will give you this small dialog box and ask you where exactly you want to load I want to load to a table in existing worksheet and this will be F7 and I will click on okay it will produce a new table however this table is transformed the way that I did in the power query as you can see here my table now is clean one I have the date I have the amount the percentage as decimal and also I have the name of the sales and all I want to do is now is to create a pivot table it's very easy while selecting any cell inside the new table generated by the query I'm going to table design and I'm going to summarize with a pivot table it will generate a pivot table I want this table to be in the same worksheet I'm selecting this cell J5 and okay it will create the pivot table and let's drop the amount in the value right click and number format let's take the name of the sales rep on the rows and now you have your report done and all automated by power query in order to prove this is automated let's try to get the new data from 2018 let me select all control c going back to the same sheet 2017 I'm going to the original table make sure that you are going to the original table not the query generated table I'm going down to the first empty row after the table control V the table will be expanded automatically and look at this if I select this table right click and refresh the query will be refreshed and you can see the new data for 2018 is coming down here and not only this if I refresh the pivot table as well and now let's take the date in the column you can see here I have the two years 2017 and 2018 and each time you bring new data and refresh your table coming from the query will be updated and your report will be updated automatically that's all for today we try to answer the three questions where is power query why power query and how the power query is working and we saw how we can automate our work our transformation of data with power query without writing any code either in the VBA or in the power query and code itself welcome to a new video from excel data analysis series PQB power query basics video PQB02 extract data from the same workbook in this video we are going to see how we can get data from an unnamed range how we can get the same from a named range and finally how we can get the data from a table during the video we are going to discuss how we can delete columns and how we can delete other columns how we can create a dynamic unique list via removing duplicate rows and also how we can delete or change applied steps and finally how we can combine query output with excel functions in our practical example today we have the monthly sales from triple A bike shops books it comes in three columns month product and sales value you can notice that we have three months January February and March and you can see the month represented by the last date of the month and also you can see I have three copies from the same data first one I put it in a named range called q1 if you use the drop down in the name box you'll find that I have a range called bikes q1 if you select you can see here is the range named bikes q1 and also I have the same data but in unnamed range and also a copy in a table and I give a name to this table the name is bikes and you can see I have two requirements first one is to create a summary by product to do this we have to create a unique list of the products and also I need to create another summary but by month and in this case I want to create a unique list for the month and also I have the q2 sales it's here it's the other three months for q2 and I want to update the data and see if I can update this automatically without redoing any of the steps that we are going to do now so let's start with the first set of data that we have in the named range bikes q1 and while selecting any cell inside this dataset I'm going to use the data ribbon on the left hand side I have my power query section so I'm going to select this icon the small table here if you hover on it you'll see from table or range I'm going to click on this it will launch the power query editor and a new query will be created if you look at the right hand side you will see that the query inherited the name of the range so the name of the query is bikes q1 I have the source here the first step in the applied steps and other two steps applied automatically by excel I'm going to delete them look at this I can just use this red cross and I can just delete the same here the red x I can just delete and let's start to do all the work manually if you look at the data it comes here in three columns and 13 rows however the first row is the header so I want to use the first row to be the header instead of column one column two and column three that I can notice here on the top of the columns so in order to do this I'm going to go to the home ribbon and from home ribbon in the transform section I'm going to select use first row as header once you click it will promote your first row to be a header so in this case in this query I need just the name of the product so I don't need either the month or the sales value I can just select the month and from remove column I can just delete I can select the sales value right click and also remove and now I have the product name only and you can see removed columns a step created here in the applied steps however there is another way I can do this I can just again delete this step via the red x and the columns will come back again for sure don't worry I can play with the data as much as I want because this is a read only version of the data your original data still in the name range in the excel however this is a read only version of your data so let me select the column I want which is the product name I can just right click and I'm going to select remove other columns this is very helpful if you have a lot of columns and you need just a few columns now the last step is to create the unique list via removing the duplicates while selecting the column product name I can just go to reduce rows and from remove rows just to select the small arrow and remove duplicates exactly like what I have in excel now the table reduced to a small list of four rows now my query is ready I can just load it I can go to home close and load close and load two it will open the dialog box asking you where you want to load your data I can select existing worksheet and cell f5 and I want it in a table there's some other options we'll discuss later but for now let's use table and I'm going to select okay it will create a new table and you can see the queries and connection pane opened and I have one query with four rows loaded now I want to create my summary so I can add a column here I can call it sales and enter you will see the table will be expanded like any other normal table I can use a quick sum ifs formula to create my summary my sum range will be the sales value and I'm going to use f4 to fix the reference comma my criteria range will be the product again f4 to fix the reference and finally my criteria will be the product name in the new table close the brackets and enter if you use this small icon you can just overwrite all sales in this column and it will create and it will copy the formula till the end quickly you can select this small column and go to home and you can do a quick number formatting okay now you have your unique list created you have your summary using the sum if let's try to do the same in the unnamed range set of data while selecting any cell again I'm going to use the same button from data get and transform and this icon once I click on this it will open the create table dialog box I should click on okay it will open again the power query editor however it will give an automatic name for the query and this is something I don't want because it will give a name like table 1 table 2 and if you want to change it later it will not be easy because it will it will spoil the source of the query so I'm going to cancel this and advice before loading anything to power query from the same workbook please give it a name or the best way is to put it in a table and now let's do the same for the table data set while selecting any cell inside the table I'm going to use again the same button from get and transform area in the data ribbon once I click on it it will launch the power query editor on the right hand side you can see that the name of the query inherited from the table and I have two applied steps first one is the source we cannot delete it just getting the data or getting the table from the excel and the other one change type I'm going to use the red x to get rid of it and if you look at the at the data it comes here the three columns and only 12 rows why because the headers already in place and why because the table has headers and the column names in the query inherited from the table as well and this is very good because it reduces the steps required to transform your data if you are getting the data from a table so now in in this time I don't need the products I don't need the sales value so I'm going to select the month right click remove other columns you can see now I have the month it comes here with this icon meaning that the type of the data is any I want to change to date just click on this icon and select date now I want to have a unique list of the dates while selecting the column I'm going to remove rows and select remove duplicates now I have a unique list of the month three months I am ready to load my data close and load close and load two same same I'm going to use the same dialog box existing worksheet and table I'm going to queue five this time and okay it will create a new table and if you look here I have another query created called bikes with three rows loaded I can now do the same I can add a column called sales a quick sum ifs formula to calculate the summary of the sales this time my sum range is the same it's coming from the table you can see here I can just select the header and it will put the header name and it will use it in the formula and I have a video about this subject how to use the headers of the columns if you are using tables in the formula I will leave the link in the comment if you want to review and also and also on the top of the screen you will see a link you can use it to go directly to this video comma and then I need the criteria range this time is the month and I want the criteria comma this time is the month in the other table close the brackets and enter quick number formatting and double click and you have your summary ready so now I want to update the data and see what will happen so I have here the query depends on the named range and I have here the query depends on the table and I have here q2 sales let's take copy from it and go down here in q1 sales and control v and what I'm expecting is once I refresh this it will update everything from where can I refresh this I can just select any sale inside this table the table extracted from the query right click and refresh or I can just go directly to the queries and connection pane and refresh when I do this it will refresh but actually nothing happened and what do you think why nothing happened because the named range is not a dynamic range and if you try to select the q the bikes q1 from here it will stop only at row 17 and all the data below is not considered as part of the query this is not the only problem the other problem that if you check your sum if function here it's also stopped at the same place and that's why your information will not be updated so let's try to take another copy of the data and go in the right hand side in the query depending on a table I will go to the first empty cell control v you can see the table expanded automatically and because the table expanded automatically the query also will be updated automatically so if I select the query from the query Spain and I refresh you can see now I have 6 months instead of 3 months and my data updated automatically that's why I do recommend that if you are going to get any data from the same book it's better to put it in a table format because this will help you to update your data automatically that's all for today Welcome to a new video from axility analysis series pqb power query basics this video pqb is year 3 concerted budgets using power query in the live example today we are going to see how we can concerted budget numbers if we are collecting the budget numbers from different departments and we get the numbers in the same workbook but in different excel sheets how we can concerted all these templates in one pivot table using power query so we are going to see how we can get data from another workbook how we can use the sheets name in a new column in the concerted table how we can load power query to pivot table how we can edit query after loading and finally how we can unpivot month columns for better analysis with pivot tables so before we start working let's have a quick look on the data that we have today we have here the six departments of the company finance hr it marketing operations and scm and as you can see this is the opx budget so each department submitted its budget in a different worksheet in the same workbook and you can see here we have the accounts in the rows and also we have the month in the columns but we don't have the name of the department in the template itself we have it only in the name of the tab or on the name of the sheet and we want to concert it all this data in one pivot table actually we aren't going to touch the original data or the data we are going to leave it in the same workbook and we are going to start our work in a new and blank worksheet as you can see here it's a new book it's book 2 and i want to just point to this data from the power query or extract this data from this workbook using the power query so we all know that power query in the data ribbon so i'm going to data ribbon on the left hand side get and transform and i'm going to select get data and from file and from excel workbook it will open the browser and ask you exactly where the file that you want to start to work with my data here in this folder row data i'm going to select and import it will open the power query navigator in the power query navigator you'll see on the left hand side the name of your file row data.xlsx and you can see number 6 here meaning that there is six objects inside this file and you can see here our six sheets for each department and if you select any one of them you will see a preview of the data on the right hand side here now i want to select the six but for the moment i'll just select any one of them and i'm going to click on transform data this will launch the power query editor in the middle you can see a preview of the finance department data as i selected in the navigator so i want to just to do a couple of things before starting working on the data first one or just give a name to the the query it gives the finance as a name because we selected the finance sheet so i want to change this to optics and enter in the applied steps there is four steps starting with the source i want to go directly to the source and you can see here the excel dot workbook function this is the function that power query use to get the data from excel workbooks and you can see here the path of the file that we pointed to and here is the preview of the data in the source and you can see here the name is the six sheets and the nature of each one is table if you select table just from here you can see here all the data in finance if you select this table down here you see the data in the hr and so on and so forth so all i need from this table is the names and the data itself so i don't want to use any of these columns and also i want to cancel all of these automatically generated steps including the navigation that i used to select the finance data so i'm going to select the navigation step right click and delete until end and you can see the excel is polite asking you a confirmation that you want to do the steps it's okay i'm going to select delete and now i need only these two columns i'm going to select from the header name and from header also data right click and remove other columns now i have only these two columns i want to expand this data i can just click on these arrows you can see two arrows in different directions as you can see here just click on it you will see all the columns don't forget to uncheck use original column name as prefix and click on okay and you have all the data in one table here here is the finance hr it and so on and so forth now you can see column one column two column three i need just to use the first row as a header very easy from home ribbon use first row as header click you can see here the headers promoted but the first one still misleading finance i just want to give it a name like cost center and enter now i'm almost ready i just need to get rid of this header because each sheet contains header so i want to just get rid of the second header i can just select from here from january and i can scroll down uncheck and okay now i have all the data is okay the last thing before loading the data i just want to make sure the data types is okay i have the first column cost center abc that's fine because this is text abc account text no problem but now january till december you can see abc and one two three meaning that the excel isn't decided yet it's text or numeric and you can see here data type upside is any so i want just to change this i'm going to select january and click or press on shift scroll till the end select december now i selected the 12 months and from the home ribbon in transform section and from data type i'm going to select decimal number now all my data is ready i can just load i'm going to use close and load close and load two i'm fine with table for the moment i can just select b3 and okay it will load my data as you can see in one excel sheet 36 rows i have the 12 months i have the cost center that was not in the original data and now i have all the data coming together now the data is loaded into a table as you can see on the excel sheet and we have here on the right hand side the queries and connection and i can find my query optics with 36 rows loaded you can check here your six cost center you have no problem so there is two things that i want to do first of all i want to load this directly to a pivot table i can just use this insert button and just insert pivot table but i want to load this directly to a pivot table and the other thing i want to put all this month columns in one column so i have the month in one column one field for the month and also one field instead of 12 fields for the values so in order to do this i want to go again to the query i can just double click from the queries and connection double click on optics query it will launch again the power query editor i have here in the middle app review of the data and i have here the applied steps the last step that we did together was change type i want to add another step so i want to use a very good functionality called unpivot i can find it in transform in the any column you can use unpivot columns but also you can have the the lovely option of unpivot other columns so i can just select the cost center and the account and i want to unpivot the 12 column of the month so i can just go to transform any column unpivot columns and select unpivot other columns you will see quickly all the month came in one column called attribute i can just double click and call it month and enter so i did a quick renaming and also we have the values in one column i can just leave it as value but i can also filter out the zeros so the data i can get rid of the unneeded data and you can see here the three steps i added stored now the unpivot and the rename column and filtering the zeros if you go one step before you can see the data here 12 months in the columns one step after now the data coming in the right direction so i can just close from here from the x on the top right and he will ask you if you want to keep or discard for sure i want to keep this is exactly like saving and you will see here the table dimension changed and you can see now the apex query is 192 rows instead of the 36 rows last one i want to just load this directly to a pivot table instead of just creating a manual pivot table from this table i can go again to the same query and right click and i can use load to it will open the option and i want to choose this time pivot table report instead of table once you click on okay it will ask you if you want to get rid of this table i have no problem and it will create as you can see here a new pivot table i can quickly create a report let me put the cost centers in the rows and the amount or the value in the values you can see here the values and also i can put the accounts on top of the report in the filters so you can see the advertising you can see only in the marketing department look at the insurance it comes in the finance look at the maintenance it comes in more than one cost center right click and quick number formatting and now what if i want to add another cost center let's say that this is the half of the cost centers i'm just doing a dry run and more cost centers will come this should be amended in the original data and let's see what will happen it will be automatically updated but we need to go back to the original data and do some changes and let's put some new data in this original workbook i have here my six cost centers i'm going to add a new cost center just i'm copying the same worksheet i'm i'm going to rename it sales and enter let me delete all these numbers and let's put only one number in sales let's say 1000 and i'm going to copy it till end for the 12 months and now i can just save and close and let's go back to our pivot table i'm going to right click and refresh you can see here the query is refreshing 204 instead of 192 and look at this this is the new 12 000 coming from the sales cost centers meaning that all the transactions all the applied steps that was working in this power query on the right hand side is applied to the new data and i can see my table is coming my pivot table is just one refresh all the information coming correctly and all these applied steps applied to the new data very nice and very quick thank you very much for your time this is all for today welcome to a new video from excelite analysis series pqb power query basics this video pqb04 extract data from folders in this video are going to see how we can extract data from a folder containing csv files how we can use the file names as a new column in the query and how we can merge three columns together to create a new date column and also are going to see how we can load the query directly to a pivot table to format or to create a sales report and finally when we put a new csv file into the folder the report will be automatically updated so before jumping to excel let's have a look on the data that we are going to use to create our sales report through the power query as you can see we have a folder containing three files one of them is text file we are not going to use it we are going to use the other two files which is a csv file containing the sales information for two years sales transactions for 2016 and sales transactions for 2017 both of these two files containing more than one million rows and you can see the size of the file in 34 megabytes each file of them containing data like this the category the name of the category of the product and also the name of the product and also we have the quantity and the sales value and we have partial information about the date we have the number of the day we have the number of the month but we don't have the number of the year which is as you can see we can get it from the name of the file itself so what we want to do is to create a new column here using this information and to create a new column here containing the full date as you can see once we finish the transformation we need to load this data into a pivot table as you can see a pivot table report containing the data for the two years not only this when we add a new file to the folder containing the sales for 2018 and we refresh the query and the pivot table the table or the the pivot table report will be updated with the new year information so in our practical example we are going to start with a new workbook as you can see it's a clean workbook we just give it a name however all the information will be extracted from the folder that containing the csv file so to start doing this we need to go to the data ribbon because the power query we can see in this left section called get and transform we are going to get data and from file this time we are going to go down up to from folder and a small window will pop up asking you where exactly your folder i can use this button to browse or i can just paste copy and paste the path of the folder inside this bar once i click on okay the navigator window will be open the power query navigation window will be opened and as you can see here i have the content of this folder displayed in this navigator and i have a lot of options down here i have combined load and transform data and we usually use transform data once i select transform data the power query editor will be launched in the power query editor i will see only the information about each file inside this folder as you can see i have here the three files one is text file and two is csv files i want to work only with the csv files and whenever i add new files to this folder i need the power query only work with the csv so i'm going to select from this column the extension i'm going to select the filter and i'm going to filter out dot txt once i click and okay you can see here only i have only two files sales for 2016 and for 2017 and you can see in the applied steps i have the first step the source and then filtered rows so what i want to do next is to extract out the 2016 from this text from the name of the sheet or the name of the file so i'm going to select as you can see select this column from the header and i'm going to transform and i'm going to select split columns for this case i'm going to use split by position once i select this it will open a small dialog box asking you how you want to split this i want to split by position and i can use the position of the of the text so the first word is sales t i don't want the first six i want to start after the six so i'm going to write six and then i want to extract one two three four which is six seven eight nine so i want him to split again starting from 10 so i'm going to write six and 10 and then i'm going to click on okay what will happen here you will have 2016 and then dot csv split and 2017 dot csv also split and you can notice that the column header changed to name dot 1 and name dot 2 and also a new step called split column by position and another automatic step called change type i'm going to get rid of this for now because i'm going to change the types later manually now if you look here in the content here is the content here is where the actual data in this two csv files lying if you select in this empty space you can see here here is your csv file and you can see it's 34 megabytes so what i want to do is to extract this data i can use this button which is combining the files together but if i did this it will ignore all this information and you know that i need the year number in order to complete the date fields so i'm not going to do this and instead of doing that i'm going to add new column add custom column once i select add custom column the custom column a window will open ask you exactly what you want to name this column for me i'm going to use custom i'm going not going to change it for the moment and in the formula section i'm going to write a new formula called csv dot document as you can see because i'm using office 65 it gives you a help like this you can just click on it and it will help you but if you don't you can just write it yourself and make sure that you are writing in the right syntax meaning that you are writing in the right spelling and also you have to consider the upper case and lower case because power query is a case sensitive language so i'm going to open a bracket once i open the bracket i want to put inside this formula the content column so i can see all the available columns here i'm going to select content and insert from here then i need to close the bracket you can see here the no syntax error so you are good to go once i click on okay you can see a new column added here called custom as i didn't change the default name given by power query now if you select here you can see two tables if i select the table from the right area here you can see here here is your actual data and now you can just expand and you will get all this data inside the power query edit before doing this i need only in this case this information the information for the year and also the information for the two tables or the custom column that i added so i'm going to select the first one and i'm going to scroll and also i'm going to press on control and i'm going to select name dot one column and then right click and i can use remove other columns now i have the table and i have the name of the year as you can see here and now i can start to split my tables i'm going to use these two arrows from the header of the of the column here are the all all the columns that you have inside the table but don't forget to uncheck use original column name as prefix and then click on okay all data will be expanded the two files together and all information now in one view as you can see so the first thing i want to do now is to promote the headers because you can see column one column two and then you have the headers here so first thing i want to do is to upgrade or use the first row as header for the column i can easily do this from home use first rows as headers now it's okay the only thing that i want to change in the header names is the column of the year because you can see here it's 2016 but let's leave it for now because we are going to merge this with the other information for the date so before doing anything please don't forget to get rid of the second header means what we have two files each file of them containing a header we used the first header the first row of the first file as a header for this combined table but inside this data there's still another header i want to get rid of it if you want to do this let's go to something like category let's open the filter you can see here all the categories we have the four categories you can see also load more because the list may be incomplete because as you can see here the preview on the power queryator is only working for around a thousand rows however i know that inside this file we have more than two million rows so let me do the load more it it will be loading from the two csv files and give you all the option that you may need inside this filter it will take a few seconds as you can see working on the three million records now the list is complete i want to uncheck category because category is the header of the column not data inside the column let me uncheck and click on okay now i want to start to build the date i have the day i have the month and i have also the year but we have to be very careful when we are building a date from separate columns we need to understand or to know first what is the order that will be accepted by power query because some countries are using the day the day first then the month and then the year and some other countries start with the month like the united states so let's check our regional settings first i can do this from file option and settings query options it will open a new window in this new window you can exactly what settings you are using from regional settings here you can check that you are working with the english united states meaning that you need to start with the month first so i'm going to select month first i'm going to use control and then day and finally i'm going to scroll and go to select year now i selected the three columns in the right order now i can go to transform and do something called merge columns it will open a new window asking you what separated you want to put between the data inside these columns for me i'm going to use custom and i'm going to use this dash and i need to give a name for the new column let me call it date then i'm going to click on okay as you can see here i have a column called date and the default is the information is abc as you can see here abc meaning that it is a text this is not good for dates so i need to change this to date now i have all transformation done you can check the data type before loading you have category abc meaning it is text same for product quantity one two three meaning it is whole number one two meaning that it is a decimal number and finally you have this calendar icon meaning that you are looking at dates now your information is ready you can start to load it to the excel sheet you can go to home and from home you can use close and load close and load two it will open the loading window import data window you are going to use the pivot table report and i'm going to use the existing worksheet let's put it in b2 and click on okay once i clicked on okay the query will start to connect to the data source and you can see the rows are loaded like here and the counter is indicating how many rows loading till the moment now we are 130 and it is continue counting till the end of the 2 million rows as you can see here 2 million records more than 2 million records actually loaded and the queries and connection been opened and also the pivot table already created you can see here the pivot table list you can quickly prepare your report i will start by dropping the date inside the rows once i do this the date will be grouped automatically you can see here the dates already grouped i have years and then quarters and then month let me take out the quarters i will also take out the date which is the month currently and i can put the years in the columns then let me drop the sales value in the values i can do a quick number formatting currency zero decimal places and okay then i can start to put the products in the rows and category above the product so i have the products classified by category and now you have your sales report completed and finally let's have the new file for 2018 and drop it in the same folder here is for the data let's go up in the upper folder and let me take from here sales 2018 control x to cut back to our data file control v to paste once i go back to my query my pivot table i can just go to pivot table analyze and refresh or i can go from data and use the refresh all button here it will update the query you can see here the rows start to load again and once the query finished also the pivot table will be updated and this will take something like 15 to 20 seconds once the loading of the 3 million rows done you can see here automatically the information for 2018 automatically updated without repeating any of the steps that we did to perform or to create this report that was all for today welcome to a new video from excel data analysis series pqb or power query basics this video pqb 05 we are going to see how we can extract data from an access database we are going to see how we can extract the sales transaction from the access database and also we are going to build on the relation inside this access so we can retrieve more data from other tables and also we are going to see how we can add custom column to calculate the sales revenue also we are going to load the query into the data model instead of the normal excel table or pivot table and this will speed up the reporting process big time also we are going to check our data inside the power pivot editor so we can check the data it's around 3 million records inside the excel but in the power pivot editor and also we're going to see how we can add measures to calculate sales total sales quantity and average price using the dex formula or data analysis expression formulas and finally we are going to create a pivot table so we can generate our sales report so before going to excel and start working on our example today let's have a look on the access database to start understand our data so we have the in the access database we have three tables first one is the sales table which is the sales transaction as you can see here it's more than 3 million records and the fields that we have in this table is the product ID the region ID the date of the transaction the quantity and finally the discount as you can see here there is no names for the product or for the region and also we don't have the price for the transaction so we cannot using this table standalone table we cannot calculate the revenue therefore we need to capitalize on the two dimensional table or lookup tables first one is the product as you can see here we have the product ID which is the primary key that we are going to use or already used to have a relation with the sales table we have also the product name the category name and the price and we are going to use this price to calculate the revenue the sales revenue and in the region table you will see the region name and the region ID finally you can see here in the diagram view here you can see the product table and the region table both of them are lookup tables and you can see in the middle we have the sales tea table which is the transaction table and you can see the one to many relation coming from the lookup table in both cases to the sales tea table so we have here the primary key on the product ID field and here on the other side we have the foreign key product ID also but in the transaction table same for region we have here the primary key the region ID and the foreign key region ID inside the sales tea so let's go back to excel and see how we are going to extract this data into the power query so as usual when we start work with data from outside excel we normally start with a blank sheet we start with a new workbook and because we are going to work with power query we have to go to the data ribbon on the left hand side you'll see the get and transform data section and from get data we are going to select from database and from microsoft access database it will open a small browser asking you where exactly you store your access database so i'm going to select my access database sales db and click on import once i click on import it will open the navigation window for power query i can see on the left hand side my three tables product region and sales transactions i can check this box and select and select multiple tables but for me i want only to select the sales tea i'm going to select from the left pain on the right hand side app review for the data will be produced as you can see here is my field port id region date and i have the quantity the discount and two other tables that were not in my original table however we are going to see why we have these two tables in a while i'm going to click on transform it will launch the power query editor on the right hand side you can see your applied steps i have two steps first one is the source you can see here the three tables and the navigation when i select the sales tea table and you have here in the center you can see the preview of your data you have your feeds here product id region id date and then quantity discount and you have these two new columns if you remember when we look at the access database there was a relation between the product id or the product id from the sales tea table and the product id inside the product table that's why we have here this column representing the relation that we had in the microsoft access database same for region we have the relation between the region table and the sales tea table based on the region id so if you if you click here on the right hand side on the empty space in the right here you will see the preview of the data for just only this record so you can see here the product id 2009 and here is the match 2009 the name product name category name the price and you can see here sales table so this is coming from the table product and the same for the region if you click here you can see a preview of the equivalent record in the region table so you can see here kairu 2002 2002 this is the region id and here is the region so what i can do how can i use this data i can just click on the expand button so i can get the information that i want so don't forget to uncheck use original column name as prefix unselect all i need only the product name the category and the price i will click on okay it will expand the three columns and this column will disappear and you can see it give automatic names like how it was named in the other table but for this the first column you can see product one because the original column was named product so i want to change this i can just do it from the formula bar here i can just select carefully from upside here and backspace backspace and enter now i have this column renamed i will do the same for the region i'm going to expand this record i'm going to uncheck select all columns i'm going to select only the region and click on okay it will come also like region dot one let me do the same here i will select from the formula bar backspace backspace and enter so now i have three columns coming from the table product and one column coming from region product now i have the price i have the discount and also i have the quantity so i can easily calculate the value of the sales so i'm going to add column custom column let me give a name for this column so i'm going to give the name inside the custom column window let me call it sales revenue so i'm going to go down in the formula section i'm going to just do a very simple calculation i'm going to select from the available columns i'm going to select the quantity i'm going to click on insert and then asterisk to perform a multiplication and i'm going to select the price double click it will go directly here another asterisk open bracket one minus and then i'm going to select discount close the bracket now i'm ready no syntax error have been detected i am ready i'm going to click on okay you are going to see here a new column added with the sales revenue but you can see here that data type is not proper it's abc and one two three together meaning that it is data type of any you can check it here from the home ribbon in the transform section you can see the type is any i want to make sure that the other data types is okay so it's okay for the product id it's better to have it a whole number same for region id let's put it as a whole number for the date it's better to select date instead of date and time for the quantity it's a whole number as well discount i can see a percentage i can change the percentage for the product name and category name it's okay to have a text the price 1.2 meaning it's a decimal which is fine i have the region abc this is okay and the sales revenue i can use currency now we have the sales revenue and the type is currency i think now we are ready we can just load our query inside the data model so now i can choose close and load close and load two it will close the power query editor and it will open a new dialog box asking you where to import we saw together before how we can import a table how we can import to a pivot table but this time we want to import to the data model and this will speed up your reporting a big time but you can see here anything saying that you can load that data model however you can choose uh only create connection and then you can check here add this data to the data model and click on okay once you did that you will see the queries and connection open the queries and connection pane opened and a new query created here called sales t and your data now is loading the three million records are going to be loaded here now your three million records loaded and it's loaded into the excel data model if you want to check where this data loaded or where this information loaded you can go to the data ribbon and from data tools you will find this green icon you can just click on it or from power pivot ribbon you will see the manage in the data model section you'll see the manage icon you can also use it if you don't have the power pivot ribbon in your menu you can just activate it from file then options and then you go to add ins if you scroll down into add ins you will see manage excel add ins from the drop down menu you can select come add ins and go you will see the microsoft power pivot for excel this is the add in you need to activate and then you click on okay so let's click on manage and this will open the power pivot editor here we go there is the power pivot editor you can see here your table sales t and you can see here in the records bar you can see the three million records counter you can scroll down you can view only you can only view your data you cannot edit your data from here you can just add a new column or you can use the section here below the gray line which called the measure grad you can add measures down here so let's try to add a couple of measures so we can use it in the pivot table when we produce the sales report no matter where in the grad you can add everywhere you can add anywhere in the in the grad and if you want to add a measure you can just start type or you can go to the formula bar and you have to start by giving a name to your measure so for this case let's add a measure called total sales revenue and then you have to put double dots or the column here and then equal once you write the equal here you start writing your measure so we are going to use the sum function it is similar like excel but it is a formula let's write sum and you can see like excel it will give you some help so you have plenty of formulas starting with sum will choose the first one i can just double click using the mouse and then it will prompt you to the parameters that required by this function and remember here we don't have cell reference we have only tables and columns so i need to start with my table so my table is sales the only table i have is sales t and i need to select the right column inside this table so i'm going to select sales revenue and double click and then close the parentheses and click on enter the measure will be calculated and here you go if you expand this column you can see this is the total of the column sales revenue the very good thing about this dex formula you can attach a number formatting for for this measure whenever you use the measure in any pivot table after that you will see the same formatting no change in formatting so let's add some number formatting i'm going to use the currency so i'm going to use the english united states and you can see here you have your number formatting applied and i'm going to reduce the decimal places to zero so i don't have any decimal places in my number let's add another one which is total quantity or total q colon equal sum and tap i can use tap again sales i'm going to scroll down with my or go down by the arrows up to quantity and tap close the bracket and enter let's also do some number formatting this time i'm using the southern separator and also zero decimal places here is my total quantity for the entire period and also i can add something like average price colon equal and then i want to i want to divide the total sales over the total quantity so these these are two measures so i can quickly use the square bracket when you when you start with the square bracket you will see your measures here so the first one i want to use is the sales revenue double click and then over another square bracket and i'm going to select this time total q double click and enter this is perfect i need only two decimal places so i'm going to reduce the decimal places to only two and now i am ready this is a little bit different from the power query editor so you can just using the mouse you can toggle between the normal excel window and the power pivot editor or can also use the alt tap so you can toggle between the two windows normally but i prefer when you stop working on your power pivot editor you can just close it from here and don't worry you can still save normally from the normal excel window i think now we are ready we can create our pivot table but from where we are going to create this we have two options we can go back to manage to the power pivot editor and go back to the home ribbon and i have here a pivot table icon i can just use it or i can close this and go to the answer the normal answer to window and i can choose pivot table once you choose the pivot table the normal create pivot table window will open and because i'm not selecting any data and we have a data model in this excel workbook it's automatically directing you to use this workbook data model and in existing worksheet i'm going to use b3 no problem and then i'm going to click on okay it will create a pivot table pivot table one let me give it a name like sales report and enter let me close also queries and connections so we have enough space you can see here the pivot table fields and you can see your table here and you can notice that this small database icon here inside the icon of the table meaning that this pivot table is based on a data model i will expand let's start with our measures you can see here the three measures that we created and the three measures start with a small icon like this f and x meaning that this is a measure so let's start with the total sales revenue i'll put it in the values you can see here it's coming with the formatting no need to reformat your numbers let me put also the quantity and also the average price you have here your three your three measures and let's do some slice and dice i'm going to use now the category let's take the date and put it above the category and the default for this case it will not be grouped and i want to group it by year so i'm going to right click and go to group and i'm going to select years only here you go you have the three years and divided or classified by category and you can see here your measure is working very properly with all the slice and dice in the pivot table even the average price it calculates at each level you put or use in your pivot table let me add a subtotal here i would prefer to put the subtotal above the numbers so let's go to the report layout and show in compact form you have here the subtotal above your group i can just copy this go to the right control v i can quickly adjust the width of the columns and now i have a new pivot table i can quickly change the layout let me take out the category and let me put instead the region so i have the region name here below the years you can see how the pivot table is refreshing very quickly you can see here i have two reports one yearly report categorized by the category and the other one by the region very quick and very efficient and whenever you add new value to your access database you just need to refresh these pivot tables and your report will automatically be updated the query will be updated the pivot table or the power pivot will be updated and then the pivot table will be updated as well i think it's very easy that was all for today welcome to a new video from excel data analysis series pqb or power query basics this video pqb 5.5 why 0.5 because we are going to use the same example that we used in the previous video pqb 05 however this time we are going to use the power query in power bi instead on power query in excel so in this video i'm going to see how we can extract sales transactions from an access database exactly like what we did in the power query in excel also are going to expand the columns and retrieve more data from the same database and also are going to use the add custom column to calculate the sales revenue we are going to load our query finally into a data model the same data model that we have exactly in excel and also we are going to use close and apply instead of close and load two we are going to check our data it's more than 3 million records but in a data view instead of power pivot editor and also are going to add measures using the DAX formulas exactly like the DAX that we are we are using in excel and finally instead of generating our report on a pivot table we are going to report our information in an interactive dashboard using a table map bar chart and a slicer in the access database we have three tables as you can see here we have the sales transactions or sales t we have the product id inside this table the city code the date quantity and discount and also we have two lookup tables as you can see the location and the product in the location you can see the city name and the city code and this relation between the city code in the sales t and the city code in the location table also we have the product table we have the product id the name of the product the category of the product and finally the price and also this relation between these two tables الحلوات المستخدمة للمدعومة أو للمدعومة هو التشكيل الأجول. لذا هناك نحن نذهب. This is the Power BI Desktop. يمكنك أن تدعي this application from Microsoft's website or you can download it from Microsoft Store. And this is much better because the new updates will go directly to your application. Whatever we are going to use it will be only on the power BI desktop however if you look here in the screen this is the screen that prompts whenever you open the power BI desktop you will find that it asks you to sign in when you sign in will have you will have an access to the cloud or the BI services and then you can share your work with your colleagues and also we can use the free trial it's for 60 days if you use the free trial it will ask you to subscribe with a business email and then you can start to use the feature of sharing and creating dashboards on the BI services or BI pro for us we are going to use only the power BI desktop for this video so we can start from here we can start to hit the get data button or we can just close this and start to work on the normal window and this is the normal window whenever you open a blank document you will see a window like this from home ribbon you can go directly to get data and get data is the power query for the power BI so I can just hit get data and I need to find the database or from database you can see here there is no access database in these options so I need to click more and I can choose access database and then connect will open the browser you can just select your access database file and then open it will open a navigator I have only three tables location product and sales tea I'm concerned with only sales tea let me check this box it will start to preview your data inside this preview window and you can see here your columns so you are good to go so I can just start to load but usually we use a transform data it is exactly like edit in the Microsoft Excel power query so I'm going to start with transform it will open the power query editor it is pretty much similar to the power query editor in Microsoft Excel so on the right hand side the query settings the applied steps I have the source which is the access database then the navigation the data I selected for the sales tea on the left hand side I have the query names and in the middle I have all the data or preview of the data the preview of my data I have the four columns coming from the sales tea table from the access database Perth ID city code date quantity discount and also I have two additional columns created because of the relation between the sales tea table and the two other tables the location and the product inside the access database so the access database sent information to power BI telling that there is a relation that you can use and get additional information from the two other tables so from the location I want to get the city so I'm going to open or use the expand button it will open this small window let's uncheck or unselect use original column name as prefix and also unselect all columns and I'm going to select only the city same for product I'm going to expand let me uncheck select all columns I'm going to select only product name category and price and click on OK now I have all the information needed I have also the price of the product I have the discount percent and also I have the quantity I can use all these three information to calculate the sales revenue because I don't have the sales revenue in this table so I'm going to use the add custom column from the add column ribbon custom column exactly like what we are doing in Excel in Excel power query for sure so I'm going to use this window to start with giving a name to this field we'll call it sales value and then going below the new column name to the custom column formula section so I can start to select my fields and do my formula I will start with the quantity I can select and double click it will jump directly to the left-hand side and then I'm going to multiply by the price quantity times price and also I need to multiply by one minus discount open bracket one minus and then I have a discount I can either select the insert here or just double-click and then close the bracket whenever I click on OK it will create the new column however I have an issue a small issue with the date type because you can see it's ABC and 123 meaning that it is any the power query editor see it as any and you can check here also from home you can see the type is any I want to change this to currency let's say so I'm going to select the drop-down I can select currency here and then price 1.2 meaning decimal number it's OK ABC ABC ABC meaning text for the three columns for category product name and city that's fine discount I think it's proper to use a percentage that's fine I can just click on the icon here and change to percentage for the quantity and also the city code and product ID I need all of them to be whole number I can just go to a home ribbon and from data type I will change decimal number to whole number I can change the date and time here to only a date you can see here date and time I will change only to date and this is fine all my data types is fine I am good to go I can load this to the data model I'm going to use close and apply it is pretty much similar to close and load to that we have in Microsoft Excel power query it's in the home ribbon on the left-hand side close and apply it will load all the information to the data model inside the power BI now the three million records already loaded to the data model and you can notice here on the right-hand side you can start to see the fields of the table you can see the sales T table and the fields that we have inside this query inside the table or inside our query and you can see here is the report view we have in power BI 3 views that you can work with on the left-hand side you have the 3 view here the first one is the one that with the icon like a chart or something like this this is the report view and here in this canvas you can start to use the fields and the visualizations and to start to build your dashboard start to build your report you start with page 1 and then you can add page 2 then you can add page 3 it's up to you how many pages in your report for us will have only one page for this report and we can start change the name of this page we can call it sales report and enter exactly like you are giving a name to a sheet or a tab in Microsoft Excel the other view that we can use is the data view if you click on the data view you can see the table we have only one table in this data model because we loaded only one table and you can preview all the data you can scroll down till the end and you can check the number of rows here downstairs you can see table sales T and you can see the 3 million records you can use this view also to add columns if you go to modeling you will have an option to add a new column you can also add a new table and you can add a measure and this is exactly what we are going to do now what we want to do is to have a summation for the sales value and also for the quantity and then to calculate the average price and if you want to add a new measure you just need to click from calculation here from the modeling tab you just need to hit the new measure button it will prompt you inside a formula bar you can see here is the formula bar and by default it will give a name to your measure or your calculation it will call it a measure and you have to give a meaningful name to your measure so we are going to change the default and we are going to write something like total sales and then equal and we want to just have a summation for the sales value we are going to write a simple DX formula it is the function sum it's pretty much similar to the one that we have in Excel whenever you start to write it will give you some assistance double click it will open automatically a bracket and it will give you the options we have only one table you can see here the sales T is our table and here is all the option which is all the fields inside the sales T table so I'm going to just double click close the bracket and hit on enter my measure will be added you can see in the fields here a new field added called total sales and you can notice the calculator icon here indicating that this is a calculated measure I can also add number formatting when you are selecting your measure here you can go to the modeling tab and you can select from formatting whatever formatting you want it's proper to use the currency so English United States is okay and zero decimal places I want to add a new measure no problem just click on new measure again it will prompt you to the formula bar and it gives you again the default and writing measure so I need to change this again let's call this measure total quantity let's try to write the same DX function some tab and then I'm going to select sales T again quantity and then tab close the bracket and enter and let's add some number formatting let's add the final one again I'm going to hit the new measure it will prompt me again to the formula bar and giving me the word measure as a default let me change this to average price and then equal now I want to divide two measures already calculated measures so if I want to call the measures I can start with the open square bracket it will give me a list of all the measures I want to start with the total sales I'm going to select with the arrows and then tab and then over again square bracket now the quantity tab and enter the third measure created you can see here the average price with the calculator sign or calculator icon also total quantity and finally the total sales let me show you the model view if you click on the model view you can see here your table as a small box with all the fields listed below the title of the table or the name of the table and if I have more than one table I will see more boxes like this I can use this window to create relations between my tables exactly like what I'm doing in the excel data model or what I'm doing in the access databases and also from this view I can do some organization to my table because you can see that I have a very long list of fields when I go to the report view it will not be easy to select the correct field so I need my three measures the average price total quantity and sales but there is some field that I don't need like the product ID so I want to hide the product ID from the report view so I'm going to select it right click and I can just hit hide in report view so I can do so for some other fields like sales code like the price I can do also the same for this count the category I need it I don't need the quantity or the sales value because instead of them I'm going to use my measures I can select all of them right click and hide from report view you can see the icon changed and when I go to the report view I will see only the category city date product name and this three measures so now let's go back to the report view you can see a smarter list and shorter list so before start building the report if you look at the date the hierarchy of the date you can just click on this arrow you will see the date hierarchy it's four categories or four levels the higher level is the year then quarter month and day so the benefit of the hierarchy whenever we put it in a table or an individual you can start to drill up and down through this multi levels so it's very useful and the date hierarchy is just created automatically by Power BI whenever it sends that there's a date inside your model so I need to build another hierarchy in my data so I have the category and the product name can find the relation between them because I have like four category and for each category there is a specific number of names related to each category so I can start to build a hierarchy between these two fields so I can start by selecting the category I can use these three dots and it will open a small menu and I can select new hierarchy you can see here a new hierarchy created by default it's called category hierarchy and the first level in the hierarchy is the category field I want to add another field so I'm going to product name again I'm going to use the three dots it will open the same menu and then I will select add to hierarchy and category hierarchy now you can see a new field added to the category hierarchy which is product name and make sure that you have the right order meaning that the highest category should be on top and then the lower category and so on and so forth so category it's correct to be at the beginning of the hierarchy then the product name now I think everything is ready I can start to directly build my dashboard or my sales report I want to start with the sales total so whenever I select the sales total field and just drag it and drop it inside the canvas it will create a graph this is the default whenever you drop value amount and numeric value inside the canvas it will produce this column chart like this so for me I want this to be changed to a table so let me go to the visualization while selecting this visual and I'm going to select the matrix matrix is pretty much similar to the pivot table let me resize it and put it down here because you can see it's very small here I can just go to this focus mode button just click on it it will put the report in the middle so I have here the total sales for the three years this data is for three years you can see total sales 190 million let me start with the new hierarchy created I will take the category hierarchy and just drop it inside here you will see it will start with the highest level of the hierarchy which is the category accessories bikes clothing and components I want to drill it down you can see this small plus it is exactly like the one we have inside the pivot table in Excel so I'm just going to expand the four categories so that's fantastic I have the categories and below every category I have the product names I can scroll down until the end I have the subtotals let's add also the total quantity I'll just drop it inside here it will create a new column with the quantity and also let's drop the average price so I have a very good report let's go back to the report view I think we can just do a quick resizing now we are ready to continue our report so now I have also a city if you look at the city inside the model view you can see the city column if you just use the filter to check you have the city names I have seven city names all inside Egypt if you go back to the report view and you just take the city just select the city and drop it in the cannabis you can't imagine what power BI will do automatically it will create a map like you can see here it's a map for Egypt and you have the seven cities here here you can see Luxor and here you can see شرم شيخ كاير وانجيزا عليكزاندراء and also it's very nice I can also put something like the sales total while selecting the map I can just check the total sales it will add this dimension inside the map you can see here the circles is much bigger the big circle will represent the higher sales volume and the smaller circles will represent a smaller amount of sales so now we have a report a matrix report and also we have a map we can add also another graph or another visual we can again go to the fields and reselect the total sales and drop it inside the empty space again it will create a new chart or a new visual it's a column chart I won't change this to clustered bar chart and now let me add again the category hierarchy I'll just take it and drop it inside the chart you can see it's already created the four categories on the bar chart if you want to expand and see all the products you can use this double arrow here it's connected double arrow this is the expand icon you can just click on it you will see all the products together here I can drill up using the this drill up button it's now back if I want to drill on only one category I can just click on the turn on drill down it will have this black circle around the arrow and whenever I click on any of the categories it will drill down to the category only so I selected the bikes and you can see now it's drilled down to the cargo bikes mountain bikes touring bikes and road bikes and you will notice also as you can see here the table is filtered to bikes only if I want to go the level up back the report will come back I can also filter the chart and the table from the map if I selected Alexandria you will see only all the table will change to Alexandria and the same for chart you can see the highlighted space is the bikes sold in Alexandria I can just select again like I am unchecking the filter I can also do filtration I can just select the mountain bike from this report and all the other charts all the other visuals will be filtered according to my selection another click it will uncheck the filter or unselect the filter I can also add a title to this report so let's add a quick title from the home ribbon I can go to text box it will open a text box this company called triple A bikes and I'm doing a sales report this is a good title I can just select all I can use a bigger font actually let's increase it to 40 that's good I can use italic I can also use the center I can just resize the text box let's put it on top I can also add some background while selecting the text box I can go to visualization here I can choose the background and I can choose a color like the light gray like this one I have here by region or by city and also I have here by category and here I can check also the individual products I need also some controls for this report working with the time because I have inside this report 3 years together so the proper visualization for this will be a slicer and here is the icon for the slicer you can see it here I can just select any empty place inside my report and just click on the slicer icon it will create an empty slicer like this I can go in the date hierarchy and select only the year the slicer for year created but it is not very good like this I want to change the type of this slicer I can just select the drop down and let me select list it will create a list with the years but actually it is in a vertical direction I want them to be on a horizontal direction because I am planning to insert this slicer in here in this small empty space so let's go to the visualization area let's select this brush and we can go directly to general and from general I can see the orientation of the slicer I can change to horizontal it will create this 3 buttons I can just resize and now we have the slicer for the 3 years I can select 2016 the entire report will be filtered for 2016 I can just select bikes so the map and also the report will be filtered for bikes so I can see here Alexandria only for bikes and this report is only for bikes and also for 2016 I can just change again this 2018 everything will be changed all the report will be 2018 and so on and so forth I think it's very nice I can just close this pane and also this one and I can also shrink the menus so you can see the entire report together I think it's very nice and easy and pretty much similar to what we have in the Excel the big difference is only about how to create the visuals inside Power BI but what you have in the Power Query is pretty much similar and also for the Data Model so that was all for today Welcome to a new video from Excel Data Analysis Series PQB or Power Query Basics PQB06 we are going to see how we can connect to the web using Power Query we are going to use Power Query to connect to finance.joho.com we are going to extract the Google's historical share prices we are going to transform the data using the Power Query Editor then we are going to load the transformed data to a table and we will use the amazing stock chart and then we are going to see how we can refresh the query to get the updated stock prices every day and finally we are going to see how we can copy the query change the source and get Facebook historical share price in few seconds so before jumping into Excel let's have a look on the website that we are going to use or we are going to connect to using Power Query here you go this is Yahoo Finance and here is the URL that we are going to copy to use it in the Power Query and you can see here the historical data of Google share price you can see the data coming in six or seven columns on the left-hand side you have the date and then the open price the high price low price close adjusted close and finally the volume and you can see last date was 27 of December and you have the dates it's sorted like descending as you can see here in Excel because we are going to extract data from an external source so we are going to start from a new workbook a blank workbook as you can see here and you know that Power Query is in the data ribbon so we are going to select the data ribbon on the left-hand side we have the get and transform section we are going to use the get data and we are going to select this time from other sources and then from web it will trigger a small window asking you what is the exact URL that you want to use to extract data you can either write in this small window or you can just paste the URL and here you go this is the URL and then I'm going to click OK it will open the Power Query navigator on the left-hand side you can see the name of the URL or the name of the website and then you can see the content of the website we have like one document and 1, 2, 3, 4 table I think our data in table 2 once I select table 2 on the right-hand side you can see a preview of your data you can also go to the web view here so you can view the data in the website to make sure that you are in the correct place inside this website and you can see here our data and from table view you can view your data we are going to select transform so we can do some transformation inside the power query editor on the right-hand side you have the query settings first section is the name of the query and you can see the name is table 2 which is not a good name because we have to give a meaningful name so let me call it Google share price and enter and below you can see the applied steps we have 3 steps first one is the source here is the content of the website and the navigation step when we selected the data from table 2 containing the historical data of the share price and finally change type when excel try to discover the correct data type for each column I think the automatic data type change is not bad I have the date in date format and the other numbers in 1.2 meaning that it is a decimal number and finally the volume 1 2 3 meaning that it's whole number which also I think it is okay and now I want to do some transformation let's start from the date I have the date in descending order I will select this arrow on the right-hand side it will open the filter view it is pretty much similar to what we have normal excel and I'm going to select sort ascending here you go the data is now sorted correctly also I need to have the month name extracted in a separate column because when I put this on a chart it will not be easy to read the entire data together so let me select this column and from add column I can go directly to from date and time I will select date and then month and from month I'm going to select month name it will create a new column on the right-hand side and you can see the new step applied here the applied step is recorded here and also I need to take this column to the left so I'm going to drag it I'm going to select from the header and drag it to the left of the query so it will be the first column the month name and then date also you can see here I have the close and adjusted close I need only one of these two so let me get rid of the close I'm going to select from the header right click and then remove and finally I want to take the volume to be the first column after the date because when I'm going to draw the chart the stock chart it requires a specific order the order should start with the volume and then the open high then low and close so I'm going to take the volume and drag it to put it just after the date so now I have the data in the correct order I think now everything is going good I can now close and load let me go to home and then close and load close and load to it will open the import data window I need it in the existing worksheet and also in a table let me put it in A15 and then okay my query will be loaded and a new table will be created here so the final step is to draw the chart so let me start to select my data I'll start with the date column till the end of the column and then I will select till the end of the table and I'm going to insert ribbon and from insert ribbon in the chart area I can select the waterfall then I'm going to select the arrow the small arrow and I'm going to select from the stock area here I want to select the last one and you can see here if you hover over it here you can see the the order that required by this chart is volume open high low and close and this is exactly what we did inside the power query so I'm going to select this chart by mouse and you can see automatically our chart is created but you can see we have a plenty of data we have a lot of data so the chart is almost not readable so I need something to help me to slice and dice or to divide the data into at least month by month so let me select the table here and from table design let me go to slicer and I need a slicer on the month name field then click OK it will create this small slicer I can select from slicer August you will see that the table filtered on August and also the chart is now much easier to read because it's focusing on the on August I can change my selection we have here December and everything is going very well I can clear the filter from here let me also change the title let me call it Google now I want to add another share so because this report is only for Google let's say that we want also to look at the shares for Facebook company but this time I don't want to repeat all the steps more and more so in this case I can use a very good feature in power query I can just select my query from the queries and connection pane and right click and let me select duplicate once I click on duplicate it will open again the power query editor and it will create a new copy of my query you can see here and instead of having only one query we have two queries with the same name let me rename this to Facebook share price and you can notice here all the steps that I did in the first query all of them copied to the second query so I'm going to the first step which is the source I'm going to select the source and you can see here a small wheel on the right-hand side once I click on this wheel it will open the small window that you can use to change the source of this query as you can see here here is the URL that we used at the beginning so I want to change this URL and just place it with the Facebook URL and in this case all the steps will be applied let's try this together control V to paste the new URL and then I'm going to click on OK the query will connect to the Facebook web page or the Facebook URL inside the finance.yahu web page and if I select the last step you can see all the steps applied to my new query now I'm ready to close and load I can just select close and load to the same window open up again import data existing worksheet let me just put it beside the original table and click on OK the new query will be loaded to a table inside the same worksheet now you have the updated numbers for Facebook and now if you want to update this data it's very easy you can see in both tables we have data up to 27 of December when the new data came let's say for 28 or 29 you can just use this refresh button for the first query and the same for the second query or you can just select the table and right click and click on refresh you can do the same for both tables or you can go to data and select refresh all in this case the new data will be updated automatically into these two tables you can use the same steps to add a new chart and a slicer for the Facebook table that was all for today welcome to a new video from the Excel Analysis Series PQB or Power Query Basics this video PQB07 split columns to rows we are going to see how we can transform chaotic data this is the first video to talk about data transformation in the previous 7 videos we were discussing how we can extract data we saw how we can extract data from the same workbook how we can extract data from another workbook from CSV or text files how we can extract data from folders and also from access database and finally from the internet in this video we are going to solve an issue the issue is that we have all invoice data came in one column meaning that the invoice number the amount and month name all in one cell not only this more than one month in the same column as well and the number of invoices per customer is variable so some customer they will have one invoice some customer we don't know it's variable and is unknown for us and the requirement is to split column with consistent data types and finally report customer invoicing using a pivot table in this video we are going to see how we can separate labels from the values we are going to use split column by the limiter to rows we are going to use split column by digit to non digit and split column by non digit to digit and finally how we can split column by number of characters and at the end we are going to see how we can add more invoices or more data and the update will be with one click in our practical example today you can see here we have two columns of data the first one containing the customer ID and the other one containing invoice details and if we have a closed look inside the invoice details you can notice that we have more than invoice in the same row not only this invoice information contains the invoice number like what we have in the first row here inv-721 all of this is the invoice number then we have a semicolon and the amount 9212 then dash and the name of the month January then we have a space and we start another invoice till March we have here space and then another invoice and space so we have in this row four invoices not only this the number of the invoices in the same row or in the same cell is not consistent so we have four invoices in the first record the second row we have six invoices we have here like five invoices here we have only three and all that you can see here is a very chaotic and unorganized data the requirement is to put this in a nice format when we have the invoice number in a separate column the amount in a separate column and finally the name of the month in a separate column so we can report on this data so we need to use power query to ease and automate this complicated job so while selecting any cell inside the data range I'm going to use the shortcut control T to convert this into a table format it will open this small dialog box asking you or confirming the data range and asking you if you have headers in this range and yes I have headers I'm going to check this and click on okay Excel will convert this directly into a table format from table design ribbon I'm going to the left side and give to this table let me call it input and enter and I want to use power query power query in the data ribbon and on the left hand side I'll find the get and transform section which is the power query I'm going to use this icon from table or range I'm going to use this icon to push my data into the power query editor once I click on this icon the power query editor will launch inside the power query editor I can see my two columns in the center area and on the right hand side the query settings it starts with the name of the query which inherited from the name of the table I'm going to change this to output enter and then I have the applied steps I have only two applied steps first one is the source when Excel connected to my table input and then Excel try to automatically detect the data type but I don't need this step for the moment so I'm going to use the red X and delete it now I have only one step which is the source looking into the data I have only two columns as we saw in the Excel sheet I have the customer ID which is no problem all the problems that I have inside the second column which is invoice details as you can see here I have multiple sets of data related to each invoice each set contains invoice number then semicolon and then the amount and then dash and then the name of the month at end of the name of the month you have a space and then another set of data related to the next invoice so you can see here I have four invoices in the first row what I want to do I want to have every invoice at least in one row so I want to take the second one and put it below the first one and then have the third one and below them and so on and so forth and of course I need also the customer number say for the first one which is ending by 500 I want this to be repeated four times each time I have invoice from this line I have the same customer number in front of each of these four invoices in order to do this let me start by selecting the header of this column and from the ربن I'm going to transform section and then the split column button I'm going to click it will open the menu of the split column let me select this time by the limiter and because I want to separate each invoice so I'm going to use the limiter between each invoice in this case it will be the space each set of invoice data separated by a space here so I want to use the limiter space I can just select it from the drop down menu here I'm going to select space and then I want to split at each occurrence of the limiter so if I have four spaces actually here's three spaces it will separate this column into three and also if you look at the advanced option here there's a very good option that we are going to use let me click this arrow it will open the advanced option I can select between splitting the column into columns or rows yes we can split the column into rows let me select this one and I'm going to click on okay and let's see what will happen yes this is exactly what we want we have the first customer ID ending by 500 repeated four times one two three four and I have the four invoices separated here as you can see here and the table expanded into 48 rows instead of the 10 rows that we started with at the beginning now we can look at the steps we now have three steps source split column by the limiter the one that we just did and the last one again a change type it's already generated by Excel however I will get rid of this for the moment because I'm going to change all the types at the end of the transformation process so now as you can see here I have each invoice in a separate line but all the information still together I have the invoice number and then semicolon and then the amount and then dash and then the name of the month I want to separate each one of them in a separate column I'm going to select the invoice details column and again I'm going to use a split column again by the limiter this time my limiter will be semicolon so I'm going to select semicolon from the drop down again each occurrence of the limiter and then click on okay you will see here the invoice number separated and again I have another column called invoice details.2 but because Excel tried to detect again the type of this column so it's tried to be clever and converted into a date type so I'm going to again get rid of this step so now I have the amount and dash and then the name of the month in one column again let me try to separate this I'm going to select the invoice details.2 this time I can use digit to none digit what this means if you look at this column here I have four digits and then dash if I use digit to none digit at the end of the nine the fourth digit the separation will happen and then the dash and jan together will be in a separate column let me do it digit to none digits now I have the amount in a separate column and then dash and the name of the month together let's try to take out the dash I'm going to select invoice details.2.2 this time I can use by number of characters I need to separate only one character from the left so I'm going to select one in this dialog box and once as far left as possible and click on okay the dash now in a separate column I can just select it right click and delete or remove now I have all the information in separate columns let me double check the data types this one ABC which is fine this one one two three whole number let me convert it to decimal number invoice ABC which is fine and finally customer ID ABC one two three meaning any so let me change it to text and now the final step is to change the headers so let me call the first one invoice number and enter second one will be the amount double click on the header amount and enter last one will be the name of the month so I'm going to type month and enter now you are ready you can just load I'm going to go to home close and load the power query will be closed and the import data window will pop up let me select table and existing worksheet I'm going to select this sheet output on the top left and I'll click on okay the query and connection pane will open and the 48 rows loaded let me go to output sheet I have my data here clean and nice let's prepare a quick pivot table and I'm going to use the keyboard shortcut Alt and V I need the pivot table to be in the existing worksheet let me put it beside the output table and click on okay I have a pivot table created and it gives an automatic name pivot table one let me change it from pivot table analyze on the left I'm going to name it invoice report and enter let me create a quick report I'm going to take the amount in the values right click number format currency zero decimal places and okay month in the column customer ID in the rows let me also separate the invoices below the customer number so I have the customer number and all the invoices related to this customer very nice report coming out of a very chaotic data as you can see here not only this I have some more data for the rest of the year so I can see here the month of December September so this is the second half of the year I can just select Ctrl C to copy going back to customer data tab the first empty row below the table Ctrl V to paste the table range will be expanded automatically let me go to output sheet let me check the filter I have only six months here right click and refresh check the filter again you have the 12 months go to the pivot table right click and refresh or you can use Alt F5 the pivot table refresh and you have all the data inside the new pivot table fantastic job very quick and easy all the data updated why because all the steps are saved inside the power query when I added new data just couple of refresh and all the information updated into report that's all for today welcome to a new video from Excel Data Analysis Series PQB or Power Query Basics this video PQB08 we are continue talking about data transformation in this video you are going to see how we can append data from three different tables in this video we have an example of three year sales in three separate tables number of columns in each table is not the same one of these tables does not have the sales value what we require is to calculate the sales value in one of the tables and then unify the number of columns in all tables so we can append all data together in one single table in the first table you can see the sales for 2017 we have six columns first one is the product ID then the size and then the date then we have the quantity the price and finally the revenue for 2018 we have only four columns why because we don't have a column to calculate the sales revenue for 2019 we have only five columns because we don't have the column for the size however we have the revenue calculated inside this sheet so what we want to do is to get all this data together using the power query and put all of them inside this sheet called all sales so we are going to start with the first one it's already in a table form and you can check from the table design tab that we have a proper name for this data set called sales 2017 so I'm going to go to data ribbon from the left hand side I'm going to use the famous icon from table or range once I click on this icon it will trigger the power query editor on the right hand side I can see the properties started with the name of the query and then I have two steps in the applied steps first one in the source then excel try to detect the data type on the left hand side the first column I have the product ID the type is 1 2 3 meaning whole number which is fine then I have ABC meaning it is text for the size then date and time for the date I can change this I'm going to select date and click power query will ask you if you want to replace the current step or not for me I'm going to replace and then let me check the quantity 1 2 3 whole number is fine price and revenue 1 2 meaning it is decimal which is okay for me as well I think I can go to close and load close and load 2 it will close the power query and trigger the import data window for this query I'm not going to load anywhere because I'm going to use it later in the append so I'm going to select only create a connection in this case this data will be saved in the back end of the excel workbook however it will not be loaded anywhere so I'm going to select only create connection and click on okay as you can see on the right hand side queries and connection pane will appear and you can see one query created called sales 2017 let's do the same for the second table 2018 I'm going to select any cell inside this table I'm going to use the keyboard shortcut alt a p t it will do the same it will open the power query editor the name inherited from the name of the table which is okay and then change type automatically let me change it and type to date only same as the previous one replace now I think I'm ready I can close close and load 2 it will open the import data dialogue box only create a connection and okay on the right hand side you can see you have now two queries the same for the third table 2019 alt a p t it will trigger as you can see the power query editor on the right hand side the name is okay 2019 let's again change the date and time to date only replace close and load then close and load 2 and again I'm going to select only create a connection and okay I have now three queries ready and all created as connection only now we are ready to start the append the final step to append all these three queries into one query in order to do this I'm going to select again data and from get and transform I'm going to get data and I'm going to select combine queries and from combine queries I'm going to select append once I click on append a dialogue box will pop in and will ask you how you want to append your tables or your queries two major options here first one is two tables or three or more tables for me I need more than two tables so I'm going to click on three or more tables on the left hand side you can see your three queries 2017 2018 and 2019 I want to select them all so I'm going to select the first one 2017 then click on shift and select the last one and now the three of them is selected I'm going to click on add you will find the three transformed to the right hand side click on OK the power query editor will launch again on the left hand side if you open this you will see that we have now four queries the three original query and you created one which is append one here in the query settings so I want to change this to have a proper name let me call it all sales and enter and now you can see here is all the data coming from the other three tables and you can see the data here coming in six columns and if we check one of this field like the size if you open the filter button on the top of this column and for sure you'll find that the list is not complete because there is more data than that we have in this preview so in order to check all the data I need to click on load more if you click on load more you will find that power query will try to load the rest of the data and you can see that there is nulls and why we have nulls because we have only the size data for 2017 I'm going to cancel this and let me check something like the revenue if we click on the revenue also I need to first load more once I did the load more I'll find also some nulls as you can see here in this columns and why because 2018 we don't have a column to calculate the revenue so let me get rid of this step I'm going to use the red X to delete the filter now let's try to load this query although there is some issues in this query however we are going to load it so I'm going to close and load close and load too the dialog box will open again import data this time I'm going to select a table existing worksheet let me select all sales sheet on the top left and then I'm going to click on okay the query will be loaded to a table in all sales let me check as you can see a new table created and the 208 rows now loaded we agreed that there is some issues in this data so the revenue column is not complete and also the size column is serving only 2017 so I want to start to edit my data and do some changes so I'm going to double click on the first 2017 from the queries and connection pane once I do this the power query editor will relaunch again in 2017 I need just to get rid of the size column so I'm going to select it from the header and right click and I'm going to select remove column now I have this column removed I need to do some changes in 2018 I can use this arrow on the right hand side to expand and then select 2018 the problem in 2018 that I don't have a column with the revenue but I have the price and the quantity I can do a calculation based on these two columns usually I use add column and then add custom column and from custom column I can add a formula to multiply these two columns together however in this case I'm going to use a different option I'm going to select the two columns from the header I'm going to select the first one and then click on control and select the header of the second one now I have both of the column selected I'm going to add columns from standard I'm going to select multiply once I click on this a new column will be created called multiplication and I have the results of multiplication of the two columns calculated in this field no further changes required in 2018 let's check 2019 I think 2019 also is okay no changes required as well I think now I am ready I can just close the power query editor from the top once I click on this x it will ask you if you want to keep your changes or you want this card for sure I want to keep my changes I'm going to click on keep all the changes will be saved I have all the three queries appended I want to reflect the changes in the appended query so I'm going to select this icon and click on refresh the query will be reloaded as you can see here the 208000 rows will be reloaded and also the table will be updated to reflect the changes happened in the three other queries now the table is updated as you can see here I don't have the size column however I have the revenue on two columns and this is not what I want exactly I think the issue because I don't have the identical name of the headers meaning that if you want to append you need to make sure of two things at least that the headers of the three tables the three appended tables are identical meaning that if you have the header called revenue and the R is capital it needs to be revenue with R capital why because also power query editor is case sensitive and also we need to make sure that the data type is identical otherwise errors will happen or as what happened with us here column will be created and the data will not be appended correctly so now we want to edit the 2018 again I'm going to select and double click the power query editor will launch again the only change I want to make here is just to rename this column double click on the header type revenue and enter now I'm ready to close and load and you can see here all the changes saved I just need to refresh the append query I can just do it from the table this time right click and click on refresh the table will be updated again as you can see the query is loading again the 208 rows and now you can see the changes will happen to the table on the left hand side okay the multiplication column disappeared and all the revenue coming in the same column and if you check the filter there is no anywhere in this column now our data is ready we managed to append the three tables in one big table and all the data is identical and all comes through together with the same number of the columns and also we managed to calculate in the power query we managed to calculate the revenue in one of this table the next step is to create a pivot table however if you look at the left hand side the first column is only the codes of the product if I want to do a proper reporting I need to bring in the names of these products and also maybe the categories but this data will not be in the same worksheet it will be coming from other table and this dimensional or lookup table need to be merged with this query in order to get the correct names in the report and this will be the subject of next video pqb 0 9 we are going to talk about transform data again this time we are going to see how we can merge queries how we can get data from one table to the other table and how we can replace the normal vero cup in the excel inside the power query welcome to a new video from excel series pqb or power query basics this video pqb 0 9 we are still talking about data transformation today we are going to see how we can use merge queries to merge data from two different tables we are going to continue the same example that we started in the previous video pqb 0 8 so let me remind you with the previous video in the previous video we saw how we can use append to bring the sales data from three different tables into one table in order to have accurate or proper append we need to unify the number of columns the header names and the data type in this video we are going to see how we can use merge queries to bring product data into the sales table meaning that we are going to use something similar to the vero cup function and not only this we are going to use a join kind called left outer meaning if we want to use the same functionality of the vero cup inside power query we need to do merge queries with a join kind left outer there is more than one join kind in the merge queries but in this video we are going to focus on the left outer in video pqb 0 8 we started with the three years sales sales for 2017 2018 and 2019 and we managed to get all of them using append in one table as you can see here and if you look at the right hand side we have three queries three different queries for every year 2017 2018 19 and finally we have the 208 rows appended in one query called all years and also loaded into a table as you can see here in the all years tab if you remember that we had 2017 6 columns however 2018 was in only 4 columns and 2019 was in 5 columns and if you check here we didn't have the revenue calculated inside the original data however we managed to calculate the revenue inside the query and we unified the number of columns and the names of the columns in order to get the all years right and correct and all data in one table and we agreed that we need to create a yearly report for this sales however on the first column on the left hand side you can see we have the product ID but we don't have the product name or the product category which is required if you want to create a meaningful report we are going to use merge queries in order to bring the name of the product and the category inside this table so we can create a proper report but this time the data not inside this excel workbook it's saved in a csv file somewhere else so we need power query to go and connect to this csv file and in order to do this we need to go to the data ribbon on the left hand side we can see the get and transform and we can use this icon if you hover over it you will see text slash csv so we can just click on this or you can go to get data from file and you have the second option from text or csv I'm going to click on this it will open the explorer asking you where exactly your file here is my file I'm just going to select it and click on import it will trigger the power query navigator in the navigator window you can see a preview of the data it's coming in three columns product and category and here is exactly what we want to do we want to use the column product and join it with the product ID inside our sales table so we can bring the name of the product and the category inside this table then we can do a quick and accurate pivot table report so we are going to use transform as usual this will trigger the power query editor on the right you can see the name of the query product lookup inherited from the name of the csv file which is fine for me we have three applied steps the source when we point to the data then promoted header you can see here we have column 1 column 2 column 3 and then the first row was the headers of this table if you click on the second step you can see that the headers promoted and finally excel try to detect the data types it's fine because you can see 1 2 3 for product ID which is number and ABC ABC for product and category meaning is text so for me this is okay I am ready to close and load close and load 2 no need for me to load this anywhere so I'm going to select only create connection and click on okay you will see here on the right hand side we have now five queries and here is the new query that we just created now I want to use these two queries to merge them together to bring the data inside one table I can do this again from the data ribbon from get data I can click on the arrow down here and go down to combine queries I have two options append that we used last time this time we can use merge however if I selected merge from here it will create a new query and join the two tables together in one new query but this is not what I want to do I want to bring the data from the product lookup inside this table so I can do this from inside the power query itself so I'm going to select all years and double click it will open again the power query editor and while selecting the all years query I have here all my sales data for three years I can go to and from home the combine section I can use merge queries and click the merge direct box will open and it is divided into two section the upper section includes the first table which is our all years table or query and you can see here a small preview of the data of this table I have here the five columns and like six or seven rows as a preview for the table in the below I can select the second query or the second table that I want to join with the all years table in my case I'm going to select from the drop down menu I'm going to select the product lookup again I can see a preview or a small preview here in the lower section of the window and also in this window you can select the join kind you can see here the join kind by default it's left outer and we said that left outer is doing exactly what we look up in the normal excel formulas and if you select the drop down menu you can see plenty of other join kinds however today we are going to focus on this next time we are going to talk about the rest of the join kinds if you look at these two tables you can find that there is a common column between them which is the product ID so it's making sense that the merge will happen by joining these two columns together so I'm going to tell the power query that I want to join these two tables using the product ID in order to do this let me select from the header product ID in the first table then I'm going to select the product ID also from the header in the second table if you look down you will find something loading and something saying that estimating matches based on data previews now the estimation is done and you can see that 208 rows match it with 208 rows meaning that every product ID that we have in all years table we found a match for it in the product lookup table which is perfect we are ready and we can click on ok once we click on ok a new column will be created and the header of the column will be the name of the other table or the second table and you can see here below the header you can see table and table and table and table and there is no data just the word table but if you select from the white space here from the white area from the empty area here on the right hand side you can see below this is exactly the data inside this small table so inside each cell we have a table and if you want to preview the data of this table you can just select from the empty space and look down you will see the content of this table and doing that for 2023 after doing the match or the join you can see that a small table created containing one record that 2023 in the other table corresponded to the wheels as a product name and category as a component name but it's not exactly what we want we want to bring the data itself here so we need to create two columns instead of this column containing say for 2023 we need to see here in two columns product category the box should be wheels and the category need to be component in order to do this it's very easy if you look at the header of this column you will see two arrows this means that you can expand this column once you select these two arrows it will open a small dialog box and asking you what exactly you want to expand for us we don't need the product ID because it's already here in the first table so I'm going to uncheck the product ID so I need only the product and category and also I'm going to uncheck use original column name as a prefix because it will prolong the header of the column once I click on ok two columns will be created one for the product and one for the category and now I have all the data complete in order to do my report some small transformation also maybe required I want to just get rid of the prefix ID now because it will not be used in the report I'm going to select from the header and remove column also I think the order need to be little bit different so let me select the category from the header press on control and select product from the header and using the mouse I'm going to drag them to the left so we have the category then product date quantity price and revenue I think we are ready we can close and load so I'm going to use this button close and load or I can just use the red X from the top right and just close the window but I have to select keep in order to keep my changes the power query will close and also the all years query will be reloaded and you will see now the 208 rows is loading and also this table will be changed just after the completion of the loading of the query now the 208 thousand rows loaded and the table also updated to reflect our changes you can see on the left hand side the category product date quantity price and revenue I think now we are ready to create our pivot table report if you want to see the example from the beginning to see also the append you will see now a link on top of this video you can just use it to go directly to video PQB08 so let's start to quickly build pivot table report while selecting any cell inside this table I'm going to use shortcut alt and v it will open the create pivot table window it depends on table all years I'm going to select existing worksheet let me put it in h4 and click on ok a pivot table will be created let's quickly build our report I'm going to select date and drop it in the rows it will group automatically and this is exactly what I want so let me take out the date itself I'm going to take out also quarters and I'm going to put the years in the columns then let's take the revenue and drop it in the value let me quickly do some number formatting right click number format currency and zero decimal places and ok let me bring the category in the rows and then product under category and here you go you have a very nice yearly sales report by category and product name the data was in different sources three of them was in three separate tables as you can see here we managed to use append to bring all together and also the product information was in a different CSV file we just connect on this bring all data together and created a very nice and quick report in next video PQB10 we are going to continue with merge however this time we are going to see the full outer the inner lift and right anti why because we are going to compare between two tables we have two different list of names in two different tables and we are going to run a complete report of comparison between the two tables welcome to a new video from excel data analysis series PQB or power query basics this video PQ10 we are continue to talking about data transformation we are going to continue talking about merge queries but in this video we are going to see how we can use merge queries to compare between two tables we are going to use different join types the first join type is full outer which will help us to bring all the values from two tables and then we look at the inner join type which will help us to bring the intersection between two tables and also we are going to look at lift anti when we want to get the values in the first table only and finally the right anti which we are going to use when we are going to bring the data from the second table only in the previous video we looked at the join kind left outer which is performing something similar to the Vlookup function in excel in our practical example today we have two tables as you can see every one of these two tables contains a list of names the first list represent the students in the art class and the second list represents the students in the math class first list contains around 275 names and the second list contains around 273 names there is some names in common and there is some names only in one of the two tables so what is required in this example the first one I need to get the names for the two classes together mean it's an OR function if you are in the art class or in your math class I need your name here in the new list the second one I need only people in the art class and then I want to see people in math only and finally I need the intersection between both tables if you are in the art and in the math class together so we are going to use power query we are going to work on merge and we'll see different kind of joins in order to bring all this information together in order to start I need first to load these two tables inside power query in order to do this I'm going to select any cell inside the first table and then I'm going to data Reben on the left hand side you'll see the get and transform section which is power query and I'm going to use this icon if you hover on this icon you see that it is from table or range icon just click on it and it will trigger the power query editor in the middle you'll see the preview of the table it's only one column table as you can see here on the left hand side you will see that the name inherited from the name of the table which is art and I have two applied steps first one is the source when the power query pointed to my table and then change type and you can see the power query automatically change the type of this column to ABC or text which is good for me I think I'm ready to load this table from the home Reben on the left hand side close and load close and load 2 it will close the power query editor and the import data box will be opened and because there is no need to load this query itself inside any table I'm going to select only create a connection and click on OK the query is in connection pane will open and you can see that you have only one query now in this workbook called art and it is connection only let me do the same for the second table this time I'm going to use the keyboard shortcut alt A P T it will trigger again the power query editor here is the preview of your data the full name column the only column in this table on the right hand side the name of the query math from the name of the table and you can see the same 2 applied steps I'm ready to close and load close and load close and load 2 from the import data dialog box I'm going to select again only create a connection and OK and now you'll see that you have 2 queries now I'm ready to start my first merge and remember that in the first task I need to bring the names from the art and math together and that I need the students that registered in both classes art and math and I need them in one column in one list here below this header in order to do the merge I'm going again to data ribbon from the left hand side get and transform data I'm going to get data and I'm going down to combine queries and then merge once I click on merge the merge dialog box will be triggered and it is divided into 2 sections as you can see in the upper section I'm going to select the first table which is art and in the lower section I'm going to select the second table which is math I have only one field in both tables so I'm going to select from the header the full name field in the first table and the full name field in the second table and automatically Excel will examine the matches you can see here 263 matches out of 275 rows in the first table so now I need to select the proper join kind you can see the join kind here the default is left outer which we used in the previous video in order to perform something similar to the VLOOKUP but this time I need to change this to full outer I'm going to select the drop down menu you can see here the third option is full outer which is all rows from both from both tables for sure now I'm ready I can just click on okay the power query will be triggered again in the power query you'll see that a new query created called merge one and this is a default name given automatically by Excel so let me change this to something that I can remember let me call it art and enter and if you look into the preview section you will find that I have a query with two columns first one coming from the first table which is art table and the second one coming from the math as you can see in the header here so let me first change the name of this header I'll call it art I'm going to select from the header double click and I will write art and enter so I have all the names here coming from the art class and on the right hand side you'll see the matches coming in tables you'll see here the value itself and on the right hand side coming from the math table it's coming in table each match coming in a table if you want to preview the content of this table you can just select the white area the empty area here on the right hand side and you'll see down here the match coming from the math table I have here عاديل أحمد and matching عاديل أحمد from the math class so now I need to expand this data in order to preview it correctly I'm going to use this double arrow here on the top of this column just I'm going to select it and then I'm going to expand don't forget to uncheck use original column name as prefix and then click on OK you can see here the names coming correctly and again I'm going to change the header double click on the header and right math and enter so I have here the names in the art and here you'll find the names from the math class and now I need to bring both together in one column I can do it very simple from the add column ribbon I'm going to select it and on the left hand side you'll see custom column I'm going to select custom column it will open the custom column dialog box first of all I need to give it a name this will be the name of the new column let me call it full name then I need to start to write my formula here in the custom column formula section so I'm going to this section and start to write my formula don't forget that power query editor is case sensitive so I'm going to use statement here so I'm going to start by writing if and then I have the available columns here on the right hand side I need the first column art so I'm going to select and I can click on insert it will put it here in the formula section so my first statement will be if art and then I'm going to use not equal and then I'm going to write null null is a value that our query can understand so if you try to read this carefully you will find that it is saying if the value in the art column is not equal to null please do something I'm going to write then and then I'm going to bring again the art column meaning that if the art value not equal to null then use the art value and then I'm going to write else and I'm going to bring from the available columns the math column meaning that again if the art value not equal to null then bring the value in the art column else bring the value in the math column so if I use this if statement it will bring the value from the art and if any of this value is null it will bring the value from the math and help me to put all the values in one column let me check if there is any error in this formula yes there's an error here the spelling of else is not correct let me rewrite it now I think it's fine and you can see here no syntax errors have been detected I can just click on okay a new column will be created called full name and here is the names coming from both classes together I can just change the type of this data to ABC or text here you go you have your data type is correct no need for the first and second column I need only the last column or the third column let me select from the header and right click and remove other column now I'm ready to load this query from the home ribbon close and load close and load to the power will be closed and the import will be opened this time I'm going to load to a table and existing worksheet let me select E4 and click on okay a new query called art or math will be loaded and you have 285 rows in this new query and you will find a new table created here containing the names that registered in both classes let's continue now we need to change the names that are in art class only so let's start doing the same I'm going to data ribbon from the left hand side get data combine queries merge the merge dark box will be opened first table will be art same same second will be math I'm going to select the only field I have full name and full name the same matches 263 out of 5 the join kind this time will be left anti and you can see here rows only in first then I'm going to click on okay the power query will be opened and the new query will be created called merge 1 let me change to art only and enter if you look on the left hand side you will see that here is the names coming only in the art class at each of these tables it will be all the time null meaning that this is the final list I need just to load so I'm going to get rid of this column I'm going to select the math column from the header and from the home ribbon remove columns now I am ready to load this query close and load close and load 2 from the import data dialog box table existing worksheet and okay a new query will be loaded and you can see the new table coming here with 12 rows next is to bring the students in the math class only and not registered in art class so in order to do this I'm going to do the same from the data ribbon left hand side get transform get data combine queries merge from the merge dialog box I'm going to select art as the first table and math as second table I'm going to select the only field I have in the two tables full name and full name same matches 263 out of 275 this time the join kind will be right anti which is the opposite of the previous one I'm going to click on okay power query will launch and the new query will be created called merge 1 math only and enter if you look at the data preview section you'll see that I have again 2 columns first one is the null because I need the names that is not in the first table so I have on the left hand side I have all the nulls but if you select the empty area here on the right hand side you will see all the names in the math class only I need to get rid of this one let me expand this one I'm going to select from these 2 arrows and always uncheck use original column name as prefix and click on okay you will find that you have the list in the math class only now you are ready you can go to home close and load from the import data dialog box table existing worksheet I4 and okay and new query will be loaded 10 rows and you can see the table here containing names in math class only final one art and math together data ribbon left hand side get and transform get data combine queries merge first table is art second table is math let me select the only field name same matches 263 out of 275 this time the join kind will be enter which is only matching rows I'm going to click on okay power query will be opened and a new query will be created called merge 1 let me change to art and math and enter you will see on the preview data section the names coming from the art class and all the matches coming from the math class and both are identical you will not find any null in the first column or the second column you can check this by expanding this column here take the full name uncheck use original column name as prefix and you will find that there is no any null inside any of this columns so you have here the matches between the two which is the intersection between the two tables or people that inside math class and art class as well I need only one column of these two columns so let me get rid of the second one remove column now you are ready you can use close and load close and load too from the import data dialog box I'm going to select table existing worksheet and K4 and okay the new query will be loaded called art and math and you have 263 rows loaded which is the student registered in both classes you can see here we perform four comparisons between these two tables we bring all names from both tables and then we get the names only in the art class and the names only in the math class and finally we found the matches or names are in the art and math together I think very easy if you try to perform the same using the normal excel formula it will take ages with very complicated formulas welcome to a new video from excel analysis series PQB or power query basics this video PQ 11 we continue talking about merge queries and in this video are going to see how we can solve the duplicate issue when we do merge queries we already discussed before that we can do merge queries to replace the VLOOKUP using the join kind left outer and this will require two tables first table should be the transaction table or the fact table and the second table is the lookup table or the mention table and we want to see what will happen if we have duplicates in the lookup table and how we can solve this issue inside and outside power query in the practical example today we have two tables as you can see on the left hand side we have the revenue by channel but only we have the channel ID information in this table and you can call this the fact table or transaction table on the right hand side we have the lookup table when you have the channel IDs and each channel ID has its corresponding channel name what is required in this example is to report a sales by channel name instead of channel ID and to use power query to do this in order to use power query I need to give names to these two tables which I already did if you go to table design you will see the first table called sales and the second table called channel in order to trigger the power query I need to select any cell inside the first table and go to data ribbon on the left hand side get and transform data and I can use this icon from table or range once I click on this icon it will trigger the power query editor on the right hand side you will see that the name of the query inherited from the name of the table and on the middle you will see the preview of your data and in the applied steps we have only two steps first one power query get the source of the data from the table and the second step when the power query automatically changes the type for me it's okay because 1 2 3 meaning it's a whole number for channel ID and 1 2 meaning it is a decimal number for the revenue which is fine for me now I'm ready I can do close and load from the top left close and load close and load 2 let me put this as create connection only moment on the right hand side you will see that the queries in connection been opened and I have one query called sales and it is a connection only let me do the same for the second table I'm going to select any cell inside the table this time I can use the keyboard shortcut alt a p t it will do exactly the same it will trigger the power query editor the preview in the middle on the right hand side the name of the query inherited from the table and to apply the steps source and change type which is fine also for me 1 2 3 for the channel ID and ABC meaning it is text for the channel name I am ready close and load close and load 2 only create a connection because I don't need to load it anywhere for this time and then click on okay now I want to go back to the sales query let me select it from and connection pane and double click power query editor now launched again and from here I can do the merge in order to bring the name of the channel from the other table here I can do my merge so from home ribbon I can go to merge queries and click on merge queries it will open the merge query window actually this example is a very easy and simple example if you want to see more complicated example about how can we do merge queries you can watch video PQB 09 when we talk about merge queries from the beginning so you will see now on the top of the screen a link you can just click it and watch the video and then you can see PQ 10 and 11 which is also related to merge queries so once you click on merge queries while selecting the query sales you will see that the merge query will be open and because it triggered the merge queries while selecting the sales query you will see it is already selected on the top of the merge query dialog box and you need to start to select the second table you are going to use this dialog box to choose the channel once you did this you will see a small preview from the channel table down here you need to select the field that query are going to use to do the match or to do the join between the two tables in this case it's obviously the channel ID so I'm going to select it from the sales table and also from the channel table and you need to make sure that the join kind selected is the left outer and as we mentioned the left outer is a replacement for the VLOOKUP function in Excel and you will see this tick box initiating or indicating that you have 19 matches out of 19 matches I think now we are ready you can just click on OK you will see that a new column will be added and inside this column you will see table in each row of this column if you select on the empty empty place here on the right hand side you will see that this is the matching record coming from the other table so 4001 here you can see 4001 and the name is Retail if you check here like 4002 here is the match found on the other table which is wholesale in this case and so on and so forth and you can see here a new applied step added called merge queries but I need to get the wholesale inside this column and instead of the table table table written here this is very easy I can use this arrow which is the expand arrow once I click on it I'm going to un select all column and I'm going to select only channel name and don't forget to uncheck use original column name as prefix and then click on OK you will see that you have the channel name now and you have all the names like you can see here now I need only the channel name and the revenue I can select both and from remove columns I can select remove other columns now I am ready I can load this query close and load I have only one option which is close and load I don't have close and load 2 because I previously loaded this in other action so I can just close and load for the moment and I can come here in the query and connection pane and I can change the load option I can just right click and I can go to load 2 and instead of only create connection I can load it to a table existing worksheet I can put it in I1 and OK once I click OK a new table will be generated and you can see here I have the report with the name instead of the channel ID so what is the problem or the issue that we want to discuss what if I have the application inside this lookup table so let's try it let's take the retail I'm going to copy it and go down in the first empty row you will see that the table will be expanded now let's check if we refresh this query I can just refresh from this button let's try once I refresh you will see that the number of columns increased it was 19 and now you can see it's 23 lines and you can see the total was 600 if you check the total now it is more 711.39 let me add another line like the wholesale control C and key accounts as well and control V and let's try another refresh this time I can just try to click and refresh look at the number of rows it's now 33 let's quickly add table row here so now you exceeded 1000 now we have an issue if you have any duplication here the number of the rows will be increased and the total will not be accurate and if you want to know why we can just double click on the sales query and check what happened inside if you go again to the merge query step here you will see this is the table we have only 19 lines as you can see or 19 records but if you check now something like retail which was 4001 the code for retail or the channel ID for retail 4001 if you check now the table how many matches found 2 matches and when you do expand it expand twice so this record will be duplicated and also the second record which is 25.2 4001 this also will be duplicated same will happen for the wholesale if we check wholesale also was duplicated so anytime you add more a recurrence of the same item on the table it will duplicate your data more and more and this is a big issue that we need to solve because this will result in inaccurate reporting and for sure here is very simple to trace it by your eyes because it's a very small data but if you are working on a large set of data it will be really a big problem so let's close this and let's see what we can do if you have the data like this select the table go to table design and you can easily remove the duplicate and this is not a problem at all you can just remove the duplicates from tool and select remove duplicates no problem it will solve the issue for sure however it is much better if you can do it inside the power query actually you can go to channel instead of sale this time and if you look at the preview this query editor I think it's only 4 records because you need to refresh this preview if you just refresh you'll see that you have 7 records it should be only 4 records and this is the problem that we have so you can just do the remove duplicates from here inside the query itself so you need to select both columns together and from home you can go to remove rows when you select remove rows you can just select remove duplicates and once you did this you can see a new recorded step or applied step will be added here remove duplicates and anytime you add duplicates inside the original data it will be automatically removed I can just close this query and click on keep and now when we refresh the sales query from here you will see the number of rows 19 and the total will be 600 exactly like the original data although you have duplicates inside the lookup table it is not a problem at all it's now solved and everything is okay it's very good to be aware of this because it can cause a lot of problems if you are not aware that duplication inside the lookup table will cause problems when you do emerge to replace the lookup using power query welcome to a new video from accelerator analysis series PQB or power query basics this video PQB12 we continue talking about merge queries in this video we are going to see how we can replace some if with power query or with merge queries and also we look at something called aggregate we are going to use it instead of expand while we using the merge queries function and we will see how we can use the sum function to aggregate numeric values while doing the merge queries in our practical example today you can see that we have two tables first table is the sales transactions coming from AAA bike shops you can see that we have more than 69000 rows we have at the first column we have the product ID then we have the date price and revenue for each sales transaction and in the other table we have the product ID and the product name so what is required now is to prepare a summary for the sales for this entire year sale into something similar to this table meaning that we need to summarize the sales of the helmets we put the subtotal of the helmets sales and quantity here and also for the light slots and for every line item on each product of these products we are going to do this using power query and instead of the normal sum if function that we use in excel so in order to start with the power query we are going to go to the data revenue on the left hand side we will see the get and transform data and we need to start to load our tables into the power query in order to do this first of all I need to have the data into a table format which I already did and also to give a meaningful name for each one of these two tables and I did the same also if you look at table design on the left hand side you can see that the table name is sales 2019 and the other table called products which is okay so let's start directly with the first table I'm going to select any cell inside the first table sales 2019 and from data get and transform I'm going to select this icon and click it will automatically trigger the power query editor on the right hand side you can see that the name of the query inherited from the name of the table sales 2019 and I have only two applied steps first one is the source when we pointed the power query to get the data from the table the table called sales 2019 and then the second step Excel try to identify or power query try to identify the proper type for each column of the five columns in the middle you'll see the preview of our data and let's check the data types for product ID 1 to 3 meaning whole number which is fine date and time for the date column it's okay but it's better to change to date only I can just select the icon from the top left of the column here and select date only this is much better I can click on replace current instead of inserting a new applied step and also for quantity 1 to 3 meaning whole number which is fine price and revenue 1.2 meaning it's a decimal number which is okay as well for this query I am ready I can just close and load close and load close and load 2 the power query editor will close for data dialog box will pop in let me choose only create a connection and click on okay the queues and connection will open I'll see that I have only one query and it is connection only let's go to the other table I'm going to select any cell inside the products table and again from data I'm going to use the same icon from table or range once I click it will trigger the power query editor on the right you'll see the name inherited from the name of the table products and applied steps again I have only 2 applied steps the source when the excel pointed to the table the targeted table and then change type when the excel try to identify the proper data type for each column as you can see 1 2 3 is good for product ID and ABC is not bad for the product name why because ABC is text so it's okay now I am ready to do my merge inside this query you'll see that I have 25 rows and I want to summarize the revenue and quantity in front of this rows how I'm going to do this it's very simple I'm going to home and from home a combine section and from combine section I'm going to select merge queries once I click on it it will trigger the merge query dialog box by default the upper half of the merge query dialog box will select automatically the query I am already selecting now which is the products query and here is the preview of the 2 columns inside this query on the bottom half of the screen I'm going to select sales 2019 which is the transaction table and you'll see here is the preview of the data inside this query I want to do the join now don't forget that you need the join kind to left outer now I want to select the column that the query will use to do the join or the match which is obviously the product ID so I'm going to select from the upper table and also from the lower table and if you look down here you'll see that the selection matches 25 of 25 meaning that each work ID here in the upper column there is a match for it inside the sales 2019 table which is okay I can just click on okay and new column will be created called sales 2019 and inside each row inside this column are going to see something called table if you select the white area here not the table itself just because if you select the table here it will open the table itself as you can see here and this is not what you want just you can get rid of this and delete you want to select the empty space to preview the data inside this table and if you look down here you will see that the query bring all the matches inside this table all the records matching 2001 which is helmets are coming inside this table all the records from the other table and it is a lot of records and that's why if you are going to expand if you did the expand like here like what we usually do this table will be expanded it will be more or less the same size as the sales 2019 table which is not what we want to do exactly we want to do just the summary or the aggregate of each line of those lines so let's try another one let's try 2004 tires and tubes if you just select on the right here you will see all the matches coming on this so let's do our new magic I'm going to use the same button here the two arrows in the two different directions here I'm just going to select once I click on it it will give you the normal expand window but instead of using expand and select the fields you are going to use aggregate instead once you select aggregate you will see some other options don't forget to uncheck use original column names as prefix and look at the options that you have I can sum the quantity which is required for me I can also sum the revenue which is fine for me I can sum the price but it will not give me something that is reasonable or making sense so I'm not going to use it you can also sum the product ID which is also something that is not useful or meaningless and also you can count the date because date is not a value that you can sum so you can do just accounting for the date for me it's not required as well so I'm going to stick only to sum of quantity and sum of revenue and that was my original requirement at the beginning let me click on okay you will see that new columns created the first one is the sum of quantity and the other one is sum of revenue but you'll see that the type is not correct because if you see the icon of the type is ABC and one to three in the same box meaning that Excel did not identify exactly what is the type of this two columns I can select the first one and from this icon I'm going to choose whole number for the revenue column let me select decimal number there is no need for the product ID column now I have the name the sum of quantity and the sum of revenue so I'm going to select it from the header and remove column now this query is ready to load close and load close and load to the power creator will be closed and the import data dialog box will be opened let me select existing worksheet and let us put it in K1 and click on okay a new table will be created and here you go here is the summary that is required you have the product name you have the sum of quantity and you have the sum of revenue you can just do a very quick number formatting and now you are simply done welcome to a new video from excelete analysis series pqb or power query basics this video pqb13 we are going to see how we can perform two dimension search using formulas and power query first of all what we mean by two dimension search this means that you can search for a value in a table using two criteria and we are going to use formulas to perform this in the first section so we are going to see how we can use vlookup plus match so we can perform the two dimension search inside lookup table and also in the second half we are going to see how we can use power query and we are going to learn how we can do unpivot columns and again merge with left outer join kind in our practical example today as you can see we have two tables the first table on the left hand side this is the daily sales from triple A bike shops you can see in the first column we have the dates and then we have the product name and then the color of the product then the quantity and if you look at the number of the rows you will find that we have around 984 rows or records and on the right hand side we have the price list you will see that we have the products the list of the products it's only four products and here is all the possible of these products and in the intersection between the product and the color you will see the price of each product so you can see that something like mountain bikes we have 200 and 200 for white and black but for the other cars there is different pricing and what we want to do is to bring the prices here in this column we need to search this table and bring here the correct price for each combination of product and color and finally we just calculate the revenue by multiplying the price times the quantity so as you can see we have two tabs first one called formula and the other one called power query in the first half of this video we are going to start with how we can do this using formulas and for sure the most famous formula for search in excel is the VLOOKUP so we are going to start using the VLOOKUP but first of all let me just quickly try to zoom in in order to see the formula together so I am going to start in cell E2 I am going to start with the first throw let me try to bring the correct price for mountain bikes the red color I am going to use VLOOKUP as I mentioned so I am going to type equal at the beginning so I have here in the formula bar and inside the cell itself equal and let me try to find VLOOKUP formula so if you type just VL you will see this is the first option that you are going to have so you can just double click by the mouse or just tap with your keyboard and it will write the correct formula VLOOKUP and open bracket and you can start to write to put your parameters inside the formula first thing as you can see here is the lookup value where is the lookup value for us this will be in B2 which is mountain bikes I am going to select by the mouse B2 as you can see here and then let me put comma to start the second parameter or the second part of the formula now he is asking for the table array where is the table that I want to search in for me for sure it will be the second table so I am going to select by the mouse from H1 up to M5 you can see here H1 to M5 this is the correct range however I want to make it absolute reference meaning that if I copy the formula right or down the range should not be changed that's why I need to do it as an absolute reference I can do this simply by just pressing F4 from my keyboard and you will see it write the dollar sign dollar sign so now I am ready I have my table array and I use the absolute reference then I am going to press on comma again and now is asking me for the column index on which column I want to retrieve the correct answer so the equation will search in the first column for sure and whenever it will find the lookup value which is mountain bike here it will going to retrieve a value from one of these columns for me I know that the red color in the 1, 2, 3, 4 column so I can just write 4 for the moment I am going to write 4 by myself and now asking you if you want the match to be approximate or an exact match for us exact match is what we want so it should be false I can replace it by zero but false will do I can just type it or double click here it will write false and then close the brackets and press on enter you will see that the value retrieved is 220 which is fine if you search the table yourself you will find that mountain bike is the second line and if you go to the red column you will see the amount is 220 which is fine however the issue now I wrote number 4 myself and if I try to copy the formula down it will always bring the red color even for this row road bikes with blue it will bring again the red not the blue and if you check here you will see that the road bikes in the third column and if you go to the blue you will find that the blue is 320 however it brings the wrong answer for the red column why because inside the formula we hard coded the number 4 so each time it will bring the color red now let me delete this one and let's think about another formula that will help us to bring the correct color or the correct column index inside the VLOOKUP for me I'm going to use match so let's try to write match alone in this cell so I'm going to press on equal and then let me try to write match MAT you will see the first option here is match just tap with your keyboard and then you can start to put your parameters inside the formula first parameter as you can see lookup value for me this time I'm going to go to the colors not the product name let me try to find where is blue so I'm going to select c3 and then comma and again the lookup array so there is a difference between lookup array in VLOOKUP and in match lookup array in VLOOKUP can be multi rows or multi columns so you can select a table like this however for match it will be only a single array meaning that you can take only one row or only one column so you cannot do only take one column like this or one row like this for us we are going to search only the headers so I'm going to search the array h1 to m1 which represent the headers for this table again don't forget to press F4 in order to make this reference absolute reference now we are ready comma again the match type exact or less than or greater than for us should be the exact match so you can use 0 this is the symbol for exact match and close the brackets and enter you will see that this formula returns 5 and 5 meaning that this is the position of the blue color inside these headers product white black red blue 1 2 3 4 5 so the position in this table for blue is number 5 so this is exactly what I want inside the formula so if I replace the number 4 the hard coded number 4 with this match formula this will give the video cup the correct column index and all the retrieval will be automatic so I can just copy the formula from here control C and I'm going to go to replace the number 4 carefully I'm going to select the number 4 the hard coded number 4 here and control V just to paste and don't forget to change the C3 here to C2 I can do it by my hand or I can just drag it from here and now I think I'm good to go if I press on enter mountain bikes red 220 let me check mountain bike red 220 this is correct let's try for the second cell I'm going to just copy one below road bikes blue here is road bikes blue and it is correct also 320 I can just do for the rest of the table if I just double click here it will copy down till the end now I can simply calculate the revenue equal the quantity times price and enter I can just use the keyboard I can go down to the bottom of the table the empty cell at the bottom of this column control and shift together and arrow up so I selected starting from the first equation control D to copy down so I have my revenue ready and all is good so let's go to the second tab and try to see how we can perform the same using power query so as you can see I have a copy here from the same data but I put it inside a table format in order to send them directly to the power query and also I give meaningful name to these two tables so if you check if you select any cell inside this table the first one on the left hand side and you check the table design tab you'll see that I give name sales T or sales transaction and also the same for the second table I give a meaningful name price list for this table so I can just can quickly start and put this inside the power query I'm going to start with the second table the price list and I'm going to data ribbon and on the left hand side get and transform data this is the power query section and I can use this icon if I hover over it you'll see that it is from table or range I can just click on it it will trigger the power query editor on the right hand side you'll see the query settings name is inherited from the name of the table and then I have two applied steps first one is the source and second one is change type excel automatically try to give the correct data type for each column let me check quickly ABC meaning it is text good for the product name 1 to 3 for all the colors meaning that the prices is whole number which is fine for me however I want to do some transformation to this table this type of tables is not for data analysis it's good for reporting and we call this kind of tables cross-tabler meaning that you have some information in the column and some information in the rows and the data is in intersection between the columns and the rows and this table format is not very good for data analysis and for sure it's better to put the table in a proper data format to ease your work inside power query so what I want to do is to unpivot this table meaning that I want to bring all these headers inside the rows meaning that I'm going to have much more rows so I will have one column for the products and one column for the colors and then one column for the value and this we call it table form or proper table format so it's much easier when we deal with data analysis in order to do this it's very easy from transform ribbon you will see something called unpivot columns I want to unpivot all the colors so I'm going to select first one press on shift and select last one I can just press on unpivot columns or I can just select the first one the product that I don't want to unpivot and I can go to unpivot columns and select unpivot other columns once I press this button you'll see that it's automatically done in less than a second and you'll see that I have one column for the products and one column for the colors but automatically Excel gave it a name called attribute let me change this to color and enter and one column for the value as you can see and this for me is the price double click type price and enter and you'll see that I have additional to apply the steps first one is unpivot other columns if you just go back you'll see the original format one step ahead you'll see that unpivot other columns and finally the change of the headers you can just delete any of these steps and go back and rework if you need to change anything so now we are ready we can just load this I can go to file close and load to it will close the power queryator and this window will pop up the import data dialog box and for me I need it as a connection only so I'm going to select only create a connection and click on okay you will see that I'll have the queries and connection pain triggered and I have only one query called price list and it is connection only now let's try to work with the other table the sales transaction table again while selecting any cell inside the table I'm going to data ribbon on the left-hand side get a transform I'm going to select this icon just I'm going to click on it it will trigger again the power query editor on the right-hand side the name inherited from the name of the table and again the two steps the source and change type when excel automatically try to detect the type of the proper type of the data first column is but you can see it is date and time together it's better to keep it like date only I'm going to select from this icon and select date instead of date and time it will ask you if you want to change do this change by replace the current step or add new step for me it's better to replace the current step because it will reduce the number of steps in your applied steps here and it will be much faster in performing your analysis and then the ABC ABC it is as you know it is just the symbol for text and it is good for the perk ID and the color and finally one two three for quantity and it is okay also because one two three is a whole number now I want to start to do my merge I want to search the data I want to search mountain bikes red and bring the correct price inside this query from the other table I'm going to do this using merge so while selecting this query I'm going to home ribbon and from combined section I'm going to select merge once I click on merge queries it will trigger the merge query dialogue box because I'm working from the sales table right now so it's by default the first table on the top half of the screen I'm going to select the other table from this drop down list so I'm going to select the price list and I will see a small preview as you can see here for the other table and now I'm going to tell power query where is the column or the columns that the query are going to use to between this or join between these two tables so obviously first one will be the product and the second one for sure will be the color so I'm going to select the product and then press on control and then select color again if you look now if we zoom in you see that there is one and two and this is the order that I started to select at the beginning the product ID and then the column color and here is the order that I used then I'm going to do the same in the second table so I'm going to select product and then press control and hold and then color and you'll see that one and two also so one is the product ID two is the color here one is product and two is the color which is okay and if you check the join kind is left outer and if you check here this green tick telling you that the selection matches 983 of 983 from the first table meaning that Excel managed to find a match for each row in this table from the other table or from the lookup table which is very good for us I can just click on okay a new column will be created and it called price list this is the name of the other table now you can see here every cell or every record in this column contains something called table if you want to preview what inside this table don't select from the table word itself just select from the white area here on the right hand side on the right of the cell and you will see below the preview of this table and you will see here is the match that found on the other table so if you look here we have the mountain bikes we have the mountain bikes red if you go down we have the mountain bike red and here is the price 220 second row road bikes blue and if you select here from the white area you will see here is the match road bikes blue and here is the price for road bikes blue now and instead of this word table table table I just need to expand and see the price in this column inside the same query so I can easily select the expand button just I click on it it will give you some options I want only the price so I'm going to uncheck color I need only the price here is the price checked and don't forget to uncheck use original column as prefix and then click on ok a new column will be created called price and here all the prices that matches your criteria from the other table final step I need to just calculate the revenue while selecting the price column I'm going to press on control and select also the quantity column and from the add column menu or add column ribbon you will see something called from number and here is something called standard just click on it and select multiply once you do this a new column called multiplication will be created and here is the multiplication between these two columns the default name is multiplication I think is not proper for this purpose so I'm going to double click and call it revenue I think now we are ready you will see here all the steps here the source change type then we merge the query we did the expansion then we inserted the multiplication and finally we did the rename of the last column to be revenue instead of multiplication I think we can just load this to excel so from home close and load close and load 2 be closed and the import data dialog box will be popped up I'm going to put it in a table in this existing worksheet a new table will be created containing the last result or the final result you can see here the same table that we started with however we have two additional columns one for the price and the second one for the revenue I think it is very easy how we can perform two dimensional search inside power query and we saw how we can do a merge using more than one column inside power query using the join kind left outer and I can tell you if you have more than 3 or 4 or even 6 or 7 columns that you want to use as a join criteria or merge criteria it is easy you can perform it the same way inside power query welcome to a new video from excel analysis series pqb or power query basics this video pqb 14 we are going to see how we can use excel.com to workbook in order to extract data and also we are going to see how we can use group buy to summarize data inside power query in the practical example we will have 3 tables containing sales data for the first 3 weeks from January and what is required in this example is first is to collect data in one table using power query this is similar to the append that we did in video pqb 08 however we are going to use different technique this time using the excel.com to workbook function in order to bring all data from different tables inside one table second we are going to prepare the data that is required only for the report at end of the video that we are going to build a report and we are going to select the data that is required for this report only and will exclude the unwanted data and then we are going to see something called auto detect data type instead of having the data type amended manually for each column we are going to select the entire table and ask excel to help us to detect the correct data type for each column and then we are going to look at group buy which is a very important tool and very important skill that you need to be aware of it will help you to summarize data like what you are doing using the sumf in excel or the normal pivot table also helping you to do something similar to group buy however inside power query we use group buy and it is very helpful especially when you are dealing with a very large set of data then we are going to prepare a dashboard to present our daily sales and we will use a slicer and two charts a very simple and small dashboard will be interactive using the slicer and will present the data inside two charts one for the quantity and one for the net sales and finally you are going to update the dashboard automatically when we receive the data for week 4 and week 5 just we are going to do one refresh and the data will be updated automatically as we mentioned here is the data that we are going to use in our example today we will have three weeks of sales data you can see each week in a single table as you can see gen 17 week 1 gen 17 week 2 and gen 17 week 3 and we want to blend all these together and prepare a very simple daily trend chart using this data before going further into this example I want to show you the end result and how we want to present our data at the end of this work here you can see the end result at the end of our example you can see here this is the summarize table you will see here it's only 124 rows however if you look at the data at the beginning it was like 1000 rows for each week so we are going to summarize the data and also we had a lot of columns we will select only the columns that is wanted and we are going to prepare these two charts one for the quantity the daily quantity sold and also the net sales value and we have here a slicer if you select any category here you will see that the charts are changing according to my selection and by end of the video also are going to see you can see here I have the 5 weeks not only 3 and we will see when we add the week 4 and week 5 in our original file it will update the charts and the table automatically back to our start file as you can see here I have the 3 weeks each one in a single table and I already transformed the data into a table format so I can directly place to power query to do this I am going to go to the data ribbon from the menus and on the left hand side I have the power query and if you remember in previous videos we usually use this icon which is from table or range in order to send the data into power query editor and I am going to use this icon this time as well just to show you how this works so I am going to select this icon it will trigger the power query editor inside power query editor you will see a preview of your table here but if you focus on the right hand side here in the properties and apply the steps I have only 2 steps first one is the source and then change type which is done automatically by excel let me x out the change type step and I will have only the source step I am going to select it and let's go back and look into the formula bar you can see here is the formula bar for power query exactly like in excel it starts with equal and then our function excel.currentworkbook open and close brackets and then you can see here the name of the table gen1 17 week 1 that was the table that I started selecting it at the beginning and then another parameter called content between 2 square brackets as you can see what I want to do here is to delete the parameters after the excel.currentworkbook function so let me select from here and then backspace and enter you will see that I have a list of 3 tables you can see the first column the content table and here is the name of this table if I select the empty space here beside the table the table written here for gen 17 week 1 you will see a preview of your data down here as you can see if I did the same in the second table I will see the data for second table and so on and so forth and that's how I can get the data using the excel.currentworkbook but suppose that you don't want to use the icon and then delete and do all of this you want to start from the beginning so let me close this window close the power query editor and I'm going to select this card because I'm not going to save anything for this back to the excel the normal excel window I'll go again to data ribbon and from data ribbon on the left-hand side again get and transform data to get data and then from other sources and I'm going to select blank query it will open again the power query editor but this time it will open a blank query and the default name will be as you can see on the right-hand side query 1 let me change this to something that we can remember so let's call it daily sales and enter now I have my query named a daily sales and then I have one step and apply the steps called source but nothing here to show because it's it's still blank and you have the formula bar empty here so I'm going to start like the normal excel with the normal function I'm going to start with equal and then space and because power query is case sensitive so I need to make sure that I write it in the right case meaning that if you're going to write excel dot work book excel should start with capital letter and as I'm typing you can see that power query is helping you you can just select from here but in some other version this helper is not working so you have to write it completely yourself so I'm going to write dot and then current will start again with capital letter and then workbook again starts with capital letter and then open and close parentheses and enter you will see that the three tables are coming again your way as we did last time using the icon or the shortcut for data from table or range so what I want to do now is to expand this data because I need the content that I have here I need to expand this data and usually I use this icon to do the expansion however I need to decide what to do with the names actually for the report purpose I don't need this table in this column I will not use this name for any for anything so I can just delete it but there is something very useful using this column let's together start to anticipate what will happen after we load this query into excel it will create a new table called daily sales which is the name of the query itself so when you refresh this query again if you add more data and you want to refresh this query again a new table will added here called daily sales and for sure because it's the output table it will go again into the input data so it will cause some problems so I need to make sure that excel will exclude any table called daily sales when it refresh the query again so let me go to the properties and I'm going to select the name here daily sales control C to copy and then I'm going to select the header of the column the arrow in the header of the column and I'm going to filter it out so I'm going to select text filter then does not equal it will open the dialog box for the text filter in front of does not equal I'm going to control V in order to paste and then okay notice that nothing happened here because there is no table called daily sales now however in the future when we do another refresh it will appear here so this recorded step as you can see here it will prevent having any troubles when you refresh this data and having the output table daily sales now no need anymore for this column I can just select from the header and click on remove columns now let's expand our data we are ready we can just click on this arrows the double arrows that you have on the top of this column the content column just click on it it will open this dialog box and ask you what exactly you want to expand I don't need all this data you can see that there is a lot of columns I need just 4 columns so I'm going to uncheck select all columns let me select the date and then the category is the way I'm going to classify my data and my report and then I need the quantity the sold quantity and the net sales and don't forget to uncheck use original column name as prefix then click on okay your data will be expanded as you can see here I have my 4 columns and all the data for the 3 tables are appended together in one query or one table you will see here all the steps are recorded the source the filter that we did and then the column for the name of the table and finally the expanded the content of the 3 tables now I want to adjust or make sure that I have the correct data type for these 4 columns if you look at the headers here you'll see the icon for ABC123 for the 4 columns meaning that Excel didn't decide yet what is the correct data type for each one of them and usually we did this manually in the previous videos we selected each and every column and did the data type just from here but for this time I want to show you something different I can select the 4 columns together I'm going to select the first one and press on shift and then click on the header of the last one so now I have the 4 columns selected if you go to transform you will see an icon called the data type and this will help you to find the correct data type for each column automatically just click on it and you'll see that the data type has changed first one 1.2 for net sales meaning that it is decimal number which is good for sales 123 whole number good for quantity and ABC which is text good for the category last one is date but it is date with time you can see the icon here a calendar and a clock meaning it is date and time and you can see the time is written after the date here I want this to be only date from here it will ask you if you want to replace the current step or add new one for me I want to replace no need to add extra step now I have my data types are good I'm good to load this table so I'm going to home close and load it will close the power query editor and a small direct box will be popped up called import data as you can see here I have 4 options to load I'm going to select the first one which is table and new worksheet is good for me let me click on OK it will create a new worksheet called sheet 1 as you can see here and the queries in connection will be opened and your query will be loaded it is 4000 rows for the data coming from the 3 tables let me give a good name for the sheet 1 I'm going to borrow the name from the table double click here in the table name Ctrl C to copy and then go down in the sheet 1 double click and Ctrl V and enter I have now a proper name for the daily sales I think the last thing that we want to do here is doing the grouping we need to do the grouping for this data but before doing this directly I want to try to help you to understand what is the concept of doing a group by in power query let's have a look on the following example if you look at this small table in this power point you'll see that I have only 4 rows in the 4 rows I have the same date however in the category column you have 2 categories accessories and bikes what I want to do I want to summarize this table into this small table meaning that I have only one line for each category with its corresponding date so for January 1st 4 accessories I have only 4 quantities which is the submission of these 2 rows 2 plus 2 and then it says of 33.3 which is the submission of 15.5 and 17.8 same for bikes I have 2 records for bikes I want to summarize them in one line quantity is 6 which is the submission of these 2 records and also the same for the value which is 996.1 which is submission of these 2 numbers this is exactly what we want to do using group buy as I mentioned it is similar to some if and also I can do the same using pivot table however in some cases you will have a lot of data 10 million records or maybe more than 10 million records that can be handled with power query and you cannot do it using the pivot table it will be much easier if you take out the details that you don't want for your report using the power query and summarize your data using group buy now let's see how we can do the group buy in our example so I am going to the queues and connection plane I have my query daily sales here I am going to double click it will open again the power query editor inside power query editor I can see the group buy tool in 2 menus first one in the home menu you can see it here in the middle group buy and also in the transform we will find it at the most left section in the transform as you can see on the left hand side let me try to click on it and see what will happen when you click on the group buy it will open a small dialog box which is the group buy dialog box for sure you will see that you have 2 options basic group buy or advanced group buy if you have the basic group buy meaning that you will have only 1 option for grouping and also 1 column for aggregation so you can only have 2 columns if you use the basic option in the group buy you will have only 2 option 1 will be the categorization or it can be the date or anything else you can select any column here and also you can do a calculation in only 1 column so the end result will be only 2 columns which can be if you want but in our cases we need to have the 4 columns as is however it will be summarized so I am going to advanced from advanced you can add more grouping and also you can add more aggregation so you can group using more than 1 column let me press on escape and let me show you something different if I select the date column and then press on control and select category column so I selected the 2 together when I click on group buy it will select both together as you can see so you are going to group by 2 columns date and category because I select both before clicking on group buy and then you can add your aggregation here so my first aggregation will be as you can see the quantity so I am going to replace this column with a new aggregated column and I am going to give it the same name so first I need to give a name for the new column I will give it the same name quantity and then I need to decide the operation you will see here the options you have the sum, average, median, min, max and some other options for me I am going to use sum and the column that I want to do submission 4 will be quantity column I am going to select also from this drop down I am going to add another aggregation will be for the net sales so I am going to keep the same name for the new column it will be also net sales and then the operation will be again sum column I am going to select will be the net sales this time then I am going to click on ok you will see that I will have only 84 rows as you can see instead of the 4000 rows I had in the previous time I got the aggregation and you will see that a new step added to my applied steps called grouped rows we can just close the power query editor from the red X here and don't forget to keep you have to select keep not discard this time I am going to select keep so it will save the changes that you did into this query you will see also in the queries and connection pane the daily sales query will be reloaded only 84 rows and you will have the new table summarized as you can see here only 84 rows now we are ready we can start to do our report as I showed you in the previous example or the complete example I am going to draw a trend line by category for both quantity and sales I am going to select the date control shift and arrow down to select the entire row and then press on control and with the mouse I am going to select the quantity column I am not going to select the header only the data and from insert I am going to select the lines and let's have this line you will see that the chart is meaningless you can see this it's a mess why because I have recurrence for the dates I have four times for 1st of January but each time I have different category so in order to have something meaningful here I need to do the filtration on category so I am going to select only bikes then you will see that the chart is meaningful but look if I try to do this selection like this like bikes you will see that the chart will disappear so I need to do something before doing this filtration control z to undo first let me also close this to have some more space right click on the chart area format chart area you need to go to this square at the most right and then from properties don't move or size with cells now if you are going to select category and filter the table on the bikes so you will see that you have a good trend let me try something different something like accessories I think it will be also another good trend or another good chart this chart is for as we mentioned is for quantity so I am going to double click on the chart title and write quantity let's try to complete the dashboard I want to do my filtration in a good way I don't want to go every time to the header of the column so I need to do it visually as you know I can do this using a slicer while selecting any cell inside the table I am going to table design and from table design insert a slicer it will ask you in which column exactly you want to insert the slicer for sure I need it for category and click on okay it will create this small slicer as you can see I can put it on the right here and if you do the filtration using the slicer it will work perfectly with this chart now let's do some formatting usually I don't like the grid lines I am going to select and delete I am going to select the line itself and if you go down in the format data series you will see something called smooth line I am going to select you will see that the edges disappeared it is visually it is better also if you select the line again you can change the color let me give something like a gray for the color this is not bad and also the slicer itself I need to change the colors to be something suitable for the gray here so I am going to select the slicer from the header I can go to slicer menu I can select from slicer styles I am going to select the gray one I think it is good now I can also make the slicer a little bit smaller so let me show something very nice if you press on alt and then you select the slicer itself you can just move it to have it aligned with the grid you can see here when I move with alt I can just put it in the corners with the grids so now I have it here I think it is good I can also do the same while resizing so I am going to press on alt and then I am going to use this dot to resize and you will see it will be aligned same if I want to increase the height I can just put it aligned with the grid like this and also I can do some other changes for the buttons I can go to slicer menu again while selecting the slicer itself you will see here in the button section I can change the height so I am going to increase the height like this and I don't need the headers for the slicer because it is category so no need to show something called category so let me right click and select slicer settings and you will see something called display header uncheck and ok you will see that you have only the buttons now I can take the chart to be aligned with the slicer I will do the same pressing on alt and drag also let me resize to the left also let me decrease the height it is too high so let me do it like this I think it is very good now I think we are almost ready we can copy this and do another one for the net sales select the chart area Ctrl C to copy and go below and Ctrl V to paste I have identical chart here I can just move the selection area with my mouse here to just point to the net sales I think it is ok I can just change the title double click and call it net sales value and we can change the color of the line I am going to choose the dark red and here you go you can just go to view ribbon and uncheck the grid lines and you see here we go here is the dashboard that I showed you in the example you can just select and the two charts will change according to your selection now I want to add more data so let's see how we can add more data to this I have already my data I am ready in another file I have another two tables for week 4 and week 5 let's go and bring them here we go we have the two weeks in a separate file Jan 17 week 4 and Jan 17 week 5 I want to send these two tabs inside the original file the file that we are working on so let me select the week 4 right click, move or copy I will take a copy to end and ok you will see the new tab coming here let me do the same for week 5 I have all the files here let me go to the daily sales report I want to refresh my query I can just do it from the table or I can just do it here from the query menu you will find something called refresh but let's do it right click and refresh you will see that I have data for both charts and if you check you will see that more lines added up to 124 as we mentioned before now you have all the data updated I want to reopen the query again and show you something so let's edit you can use while selecting the table from the query menu you can just press on edit it will trigger again the power query editor let's go back to the source step at the very beginning let me refresh preview what you will see here you have 6 tables the 5 weeks that we have table week 1 up to week 5 but you have also the daily sales table as we mentioned at the beginning but because we did this the second step which is filter draws if you select the second step and you check you will see that the daily sales appeared and the rest of steps is applied correctly and you have no issue that's why we did the filtration with you I think it's very important and very easy you can add a lot of years here if you add the entire year of or maybe 5-6 years no problem you will have your trend updated with only one refresh that was all for this video welcome to a new video from xcelete analysis series PQB or power query basics this video PQB 15 we are going to see how we can create a dynamic filter using power query this filter will take the input from the excel sheet and send it to the power query then the power query are going to filter the data in the background and send it back to excel in our practical example today we are going to start with the data coming from template workshop representing the sales for q2 and this data is in around 17,000 rows and we want to have an output sheet like this this output sheet containing a subset of the data actually filtered based on these two values first value is the date value and then a category value and we have a refresh button when we change the selection from the drop down and change the date we can write the date and then we click on refresh the subset is changed based on our selection and also the summary here changing based on the refresh and the selection and all this using power query while working on this example we are going to see a lot of things we start with the excel.currentworkbook function that excel use to send to power query and then we are going to see how we can duplicate queries and extract a dynamic unique list from a query and then we are going to see how we can use data validation to create a drop down list to use it in the filter and we will see how we can convert an excel table to a single value inside power query using a function called drill down and then we are going to use this single value as an input for the filter so we start with the excel we convert to table send it to power query and then convert to a single value use the single value in the filter and finally we send it back and automatically when we update the data the report will be updated automatically and at the end we create a refresh button and link to a recorded macro when we click on this refresh button all the data all the action going to be refreshed so in the excel sheet you can see that we have the sales for q2 as we mentioned at the beginning so I want to send this directly to power query so let me go to data ribbon and from left hand side I have the get and transform section I can just select the button from table arrange once I click on it it will trigger the power query editor on the right hand side you can see that the name of the query inherited from the table so let me change this I will call it filtered report I have two steps you can see the source and change type I don't need the change type for the moment so let me now I have only the source if you go to the formula bar you will see that I have a function called excel.currentworkbook this is the function that excel used to bring the data from the current workbook and you can see it specify the name of the table and the content of the table as well if I want this function to take all the tables in this current workbook I need to delete the rest of the formula so I'll just leave it as excel.currentworkbook I'm going to select carefully and backspace and then enter you will see that I'll have a list as you can see down here list of all the tables in this workbook I have currently only one table which is sales 2017 q2 but when I copy or send more tables inside this workbook it will be listed down here so I want the query just to deal with the tables that contains or start with the name sales so if I'm going to create any other table inside this workbook start with any other name should not be considered inside this query in order to tell the query to do so I need to do a filter I'm going to select the bottom in the top of the column name here and I'm going to text filter and I'm going to select begins with it will trigger the filter dialog box let me write here sales so I'm telling excel please do not consider any table inside this query except the tables start with the word sales and then I'm going to click on okay nothing will happen but this will help me when more tables come into this query you will see that I have now two steps the source and then filtered rows now I don't need the column name let me extend out remove column and then I can expand the content of the table if I select in the empty area here you see a preview of the table down here I want to expand this and bring it inside the query itself so I'm going to select this double arrow here and I'm going to uncheck use original column as prefix and select all the columns click on okay now you have all the data I want to fix the data types so I'm going to transform detect data type once I click on it it will automatically try to detect the type of the data for each column I want to change the date and time to only date so I'm going to do so and replace current also I can change sales value to decimal 1.2 instead of 1.2.3 replace current now my data table is ready as I told you this query will be the output of the filter so I want to do some filtration here the ultimate objective is to bring an input from the excel sheet itself and give it to this query as an input for the filter but for the time being let me just do a filter I'm going to start with the filter on the date so I'm going to do a normal filter using this arrow on top of the column I'm going to select any date just any date I want just to create any filter so let me select first of April as a filter for the date and then let me do do so in the category itself so I'm going to select the arrow on top of this column and let me select any category let's say bikes and click on okay you will see that I have now only 39 rows instead of the 17000 rows as indicated here 6 column with 39 rows and it is filtered as you can see on the first of April only one day and the category is bikes now this query is ready for the time being I can just close and load go to home close and load close and load 2 from input data let me select table and existing worksheet let me select output on the top left I'm going to select a1 and click on okay the queries and connection pane will be opened and the 39 rows query will be loaded you can view it here in the output tab as you can see now I want to have a place that I can put my inputs in and then I can send it directly to power query so let me come here in this area and I'm going to create a small table was only one header and one row so let me start with date as a header and let me put any input date let's take the same date which is first of April 17 and enter here is the date and then I need another input for category so let me create another small table with one row and one header I'll call the first header category and let me also select the same category which is bikes so I'm going to send these 2 pieces of info to power query in order to do this we mentioned before that it's better to convert it to table type so I'm going to select any cell inside the first table and click on control T or press on control T my table has headers and okay from table design on the left hand side let me give it a name like date input the other one I'll do the same table design on the left hand side let me call it category input now I have these 2 tables I can send them directly to power query let me start with the first one while selecting any cell inside this table I can just go to data on the left hand side from this icon I can just click on it it will trigger the power query again name inherited from the name of the table which is fine I leave it on the score input and then I have 2 steps the source and the change type source fine no problem change type it converted the data to date and time I need it only date so let me change to only date what I want to do now is to convert the value that I have here into a single value or convert the entire query which is only one row and only one column to a single value and this value should be the first of April 2017 in order to do this I can just right click here and select you will see that the column header disappeared and I have only a single value called first April 2017 if I can take this value and send it to the other query I have all queries here I have the first query is filtered query if I can send this value inside filtered query and use it as an input for the filter it can dynamically change when I change the selection inside the excel sheet in order to do this I'm just going to copy the date underscore input the name of the query and I'm going to filter query and in the last step the filtered rows that we did the filter together I can just jump the formula bar and I'll see that I have a formula called table dot select rows this is the formula that power query used to filter the table and you can see the parameter date here here is the date representing the date column the criteria is equal this function date 2014 4 and 0 1 and it is a function represent the static filter that I did manually in this query I can just replace all this with the name of the other query date underscore input which give me a single value and then press enter here I'm ready I need to load the query that I just did the new query date not input I need to load it back so I can just go to home and load close and load to from input I can just click on only create a connection if I change the date now let me try to change the date so instead of first of April let me select first of May and enter nothing will happen because I need to refresh I can just select any cell inside the output table right click and refresh you can see here the filter change the data changed background filter worked and give me the new input still bikes but I have the new date first of May I need to do the same in the bikes I'm going to select any cell inside this table and let me use the keyboard shortcut alt a pt on the right hand side category input is the name of the query coming from the name of the table I have the two steps source and change type change type actually is not very important for me let me take it I need to do the same right click and drill down let me copy the name of the query control C and go back to filtered report and again jump to the formula bar this time category equals bikes I need to change this static selection to this dynamic selection category dot input and then press on enter now I can load the new query category dot input let me go back to home close and load close and load 2 only create a connection and ok now I can change the selection from here but the problem that I don't know exactly the names of the categories or I don't know how to write the category name so I need something to help me take the input from the query itself and help me how to select typically I need to do a drop down menu using validation but I need at the beginning to create a list or a unique list of all the possible categories coming from the original data inside the query so how can I do this I can just take a copy from filtered report just right click and duplicate once I click on duplicate will trigger again the power query editor and it will create identical copy of the query if you open on the left-hand side you'll see that I have filtered report and filter report 2 it is identical copy of the first query but I need to do some changes inside this query I have the source I have no issue with the source you can see that I have more table came here so that's why it was very important to do the filter step now I have only the table with the data of the sales and then remove column this is fine ok with me then I need to do some changes inside the expand content after expand content I need to delete I can just select the change type afterwards and right click and I can select delete until end and let me do some changes I can just select the wheel on the right-hand side of the name of the step and in order to create a unique list from categories I don't need all these selections I need only the category item or the category field and click on ok let me change type to text quickly quickly from here and then I need to remove the so I can get a unique list of this category column from home and from remove rows I can just select remove duplicates I have only the 4 categories I can go to close close and load 2 from input data dialog box I can select existing worksheet table for sure and then go to resources on the top left A1 and click on ok and you query will be created filtered report 2 this is a very bad name I need to change so let me right click and I can just do renaming from here let me call it category underscore list and enter and also because I'm going to write the dates here I need something to help me to understand where is the beginning date and where is the end date where is the start and end date so I can select the right date so I can do something similar to the category list query let me have another duplicate from this query right click and duplicate it will trigger again the power query editor now I can change the name of this query let me call it start underscore date and enter I don't need the remove duplicate let me take it out change type also not important for me let me take it out expanded data I can again change the settings for this step and instead of selecting category I'm going to select date and okay remember that this is a copy of the query so category list still as is this is a copy of the category list I just change some steps and I have now list of all dates let me change type this time I'm going to select date and instead of remove duplicate as I did in the category list query let me do something different I'm going to transform and to date let me select a list so only one value will appear here which is the first April 17 so this is the first date I have the start date I have inside my data or inside the table I send at the beginning to power query so this is very good I can just load this but instead of loading this directly let me do another copy right click and duplicate I can do duplication from here and let me change the name of this query right click and rename I'm going to call it end underscore date and enter now I have a duplicate I want to change the start date to the end date if you look at the formula bar you will see that power query use the function called list dot min if I change min to max it will give me the last date so I'm going to write max manually and enter you will see that the date now is 30 June instead of first of April which is the last date of Q2 now I'm ready I can just load these two new queries close and load close and load 2 in the important dialog box I don't have the option of existing worksheet so let me only create a connection at the beginning and click on okay you will see that I have the two queries here let me go to the first one right click and load 2 this time I can load it to table and existing worksheet c1 and okay we'll do the same for end date now I have the start date and the end date let me go again to the output and let me do the validations in these two cells first one I'll start with the date I can just go directly to data and from data I have data tools then validation this will be a date so let me select date and date between I have the start date and the resources sheet so here is the start date and then I need to put the end date I have here the end date that's fine I can just click on okay now I cannot select anything outside this range of dates so let me select 2016 it will refuse so I'm good to go let me do something similar in category but what I want to do I want to typically create a drop down menu that is referring to this list of categories in order to do this let me start to reference at the beginning this list so I'm going to write equal I know that I have a table called category list so whenever I write category I have the two tables start with category my one category list I have only one column in this table which is category let me select and tap close the square bracket and enter because I'm working in office 365 you'll see that the entire list spilled here I just do it because I need the address or the reference here so let me control X and enter and let me select the cell let me go to data again and from data tools data validation next time I'm going to select list and here is the source of the list control V the equation that I did or the reference that I did and let me try to click on OK it will give you an error why because if you try to reference directly a column of a table inside the data validation it will not accept you have to do a small trick you have to wrap it with an indirect function so I'm going to write in direct and double quote and then double quote at the end and close bracket why double quotes because indirect need to have text so I'm going to change this address into a text then in that automatically will change to the correct reference when I click on OK the drop down is created as you can see here I can select from this drop down and this drop down is dynamic if my input include another category it will appear automatically here once I refresh my data so now I have my validation is OK and I can change my selection let me select accessories and let me again change the selection here let's try first of June right click refresh here you go everything is OK and updated let me add a small summary down here I need a total revenue so let me write a small sum function sum and then tab now I need to bring the data from this table I know the name of the table you can see it here in the list of the queries filtered so I'm going to write it will give you an option for the table it's like a table icon here you can just double click and then I need the column called sales value I can just select with the arrows and tap close the square bracket and close bracket and enter here is the total of this table if you need to know more about the structure difference what we did now call the structure difference when you refer to a table or do a formula inside a table if you want to know more about the structure difference please review the video that we did about one year ago about the structure difference and you'll see the link on the top of the screen right now let me do the same for the quantity and the average price is basically the revenue over the quantity quick number formatting and we are good to go now last step let's add the refresh button so at the beginning let me record a macro in order to record a macro I'm going to the developer tab on the left-hand side in the code area let me click on record macro it will open this small box let's give a name for the macro I'll call it refresh I'm not going to give it a shortcut no for this and I'm going to save it to this workbook click on okay now you can start to record your macro only one action I'm going to data ribbon and I'm going to select refresh all now you can go again to the developer ribbon and stop your recording now your macro is ready let's add the button from developer ribbon as well I can go to insert controls area and insert I can select my button let me put it in this area and let's assign a macro to this button the only macro that we have is refresh let me just click on okay and I have here the name the automatic name button 3 I can just rename it let me call it refresh now I can just right click to format control I can just select something like area black it is a bigger font and also I can select bigger font size click on okay now you have your button ready let's do another quick trial bikes another date let me select 15th of June and refresh your filters is working you have bikes and 15th of June and for sure refresh button is working and the summary also automatically updated the last thing is let's try to add more data to q2 I have another file containing some data so I have here as you can see the sales for q3 and q4 I'm going to send these two tables or these two sheets to the other file the working file that are working together on now we have the 3 quarters q2 q3 and q4 inside the same table if you look at your resources you have the start date is 1st of April and the end date is 30 June if we add up to q4 if we do a refresh now you'll see that this date will be updated so let me go to the output sheet and use my refresh button I'm going to click on refresh all the queries will be refreshed if we check the resources again you'll see that the last date is 31st of December let me copy this go again to output let me select this date I'm not going to change the bike selection refresh look at your table now you have the latest date and the latest information 31st of December 2017 and for sure your summary correctly updated I think it's very useful to filter a large amount of data inside a small table and have your summary and you have your controls you can just select whatever you want and one click you'll see your data updated hello and welcome to a new video from excel data analysis series pqb or power query basics this video pqb16 and this will be the last video in this series we are going to talk about how to manage queries we are going to look at how we can duplicate reference or append queries in previous videos we talked about duplicate and append but today we see how we can choose between these 3 when we should do a duplication for a query when we should do a reference and when we should append let's go directly to excel and see our examples for today in this excel workbook it called sales 2017 it includes the daily sales data for trip a bike shop coming in 5 columns date category product channel and sales revenue and as you can see it is approximately 69,000 throws not only this if you look at the folder you will see that we have another 2 files another 2 excel workbooks and we are going to connect the sales for 2017 and 2018 the requirement is to do some reporting on the sales and based on each report we are going to see when and how to use reference append or duplicate because we are going to connect 2 3 different workbooks I am going to start from a blank workbook if you look at the top you will see it is book 1 and I am going to use power query to connect the data in the 3 different from data ribbon on the left hand side get and transform here is your power query get data from file from workbook I can just copy and paste the address of the folder and enter here are my 3 files I am going to select 2016 and import the navigator window will pop up here is my workbook sales 2016 only contains one sheet sheet 1 if I click on this sheet on the right hand side I will see the preview of the data I can just click on transform data the power query editor will be launched the name of the query on the right hand side sheet 1 inherited from the name of the sheet I can change this let me call it all sales and enter the preview of the data in the middle and I have here the upper steps first one is the source it use the excel workbook and then the path of the folder 2016 in the formula bar you can see it here then the navigation when we selected the data or the sheet itself and then promoting the header and checking the data type if you check quickly I have first column date that's right ABC ABC for category product and channel ABC meaning text which is ok 1.2 decimal number is fine for the sales revenue I think this query is good to load I can go directly from close and load close and load 2 I'm going to use the import data to tell excel please import to a pivot table report in the existing worksheet I can go on the top left corner A1 and ok the query loaded and the pivot table created let's give it a name like sales report I can copy it also to the sheet name now I can build a quick report category in the rows date in the columns it's grouped by month I can just select any month right click and group I need only the years so I can uncheck uncheck and select only years and ok we have only one year 2016 that's right I can go to the sales revenue and drop it in the values right click quick number formatting currencies places and ok now I have a very quick report on the sales data by category so suppose that I received the file for 2017 and I need to duplicate the report do another report exactly same report but on 2017 data and instead of doing all or repeating all the steps I can just go directly to the all sales query double click in order to edit from the power query editor on the right hand side I can select the first step which is the source if I go up on the formula bar you will see the name of the excel workbook if I select the 6 and carefully change it to the 7 so it's now sales 2017 and enter then you can go to the last step you will see that you have no errors and on the left hand side you have the data for 2017 if you close this and click on keep the query will be reloaded and the pivot table updated with the 2017 report if you are not very comfortable by changing the data inside the formula bar no problem there is another way I am going to double click again you can just go again to the source this time I am going to use this wheel just one click it will open the query settings window you can browse again and select the new file name that you want this time let me put it back to 2016 import okay go to the last step now you have 2016 back no problem close, keep query is reloading pivot table updated back to 2016 suppose now I want to put the 2017 data in the same pivot table so I need to do a comparison or I see the 2 years together in the same report no problem double click again inside the power query editor I can just open the queries from the left hand side I can just do a right click and duplicate or I can go to the home ribbon in the query section I have the manage button here just one click and you'll see that you can duplicate from here I'm going to duplicate it will do another query identical query and called all sales 2 selecting all sales 2 on the right hand side let's change the name of the query to sales 2017 and enter going back to the source I'm going this time to change as we did the previous time the 2016 to 2017 and enter if I set the last step I have the data for 2017 now I need to append both queries together I have 2 separate queries one containing 2016 and one containing 2017 if I go to all sales and after the last step I added a new step called append I can select append from home combine and then append just one click on append it open a window the append window it will ask you 2 tables or 3 or more tables for this case I need only 2 tables table to append I can just select 2017 current one is all sales I'm just selecting the other one sales 2017 and okay a new step added and if you check in the filter here you'll see that you have 2017 and 2016 all together I can just go to close close and load 2 I already loaded the first one close and load 2 now to load the other one the sales 2017 the new one no need to import it to a pivot table report to any other place inside this workbook I can just make it only a connection click on okay now the all sales will be reloaded and you will see that the pivot table will be updated with the 2017 sales data as you can see 2017 and 2016 in the same pivot table report just a quick sorting from oldest to newest now suppose I need to do another set of reporting not only this pivot table I need to do some different reporting and I want to do it on the query itself let's say that we are reporting on the bikes on monthly basis the sales of the bikes itself so we are going to again to the all sales query double click inside the power query editor on the left hand side you can just open the queries you have now 2 queries all sales 2017 I'm going to right click here on the all sales I have something new called reference or I can again do it from manage and reference it will do another copy but this time if you look at the new copy of the all sales there is no steps here only the source and if you look in the formula bar at the source you can see it's like equal all sales meaning that it will take the end result of the all sales query so whatever happening here if I add like another steps here it will be reflected again in all sales too let me change this to something like bikes report and let's do some transformation here first one the date I need a monthly report so need for the daily dates I can just replace all dates with the date of end of the month I can do it from transform going to date month I have end of month once I select end of month it would change all the dates to the date of end of month so all January will be 29 and so on and so forth category I need only the bikes I can just select the bikes from the filter only bikes so the entire report now looking at the bikes and no need for this column anymore remove column and don't worry the filter will remain no problem product I'm going to keep it channel no need for the channel again remove I want to summarize this data so I'm going to use the group buy I'm going to select date and then press on control and product I'll find the group buy in the home ribbon in the transform section or in the transform ribbon on the left hand side so let's select because I already selected these two columns you see the these two columns represented here this meaning that the grouping will be by date and then by product and the column that will be used for the some operation will be the sales revenue so the new column I can do it the same I can call it let's say value operation itself it will be summation I can just select the sum and the column to be summed will be for sure the sales revenue the power query is going to aggregate the sales revenue based on date and product once I click on OK the sales revenue column will disappear a new column created called sales value and now I have only 96 rows instead of I think it was more than 134 4000 rows in order to prepare the bikes report I need to pivot the product column and instead of having the products in the lines as you can see in the rows here I need to put it in the header of the columns how to do this just by selecting the product I can go to transform ribbon and I'll see that I have an option called pivot column from the any column section if you click on this it will ask you one question where is the value how can I do the calculation I need to know where is the value for sure the value is in the sales value so I can just select the sales value and click on OK the names of the products are now the headers of the columns and you see the dates you can see here the dates so you have only one line for each month and you have the sales for each and every product in the intersection between the month and the name of the product now we are ready we can just load this to a table so going back to home close and load, close and load 2 this is a ready report I can just put it in a table new worksheet in A1 no problem OK and here you go you have your report quick number formatting for the values and I can do something for the dates instead of having the end of the month I can just present the month itself from custom M M M Y Y OK so it appears like only a month not a date I think this is perfect now suppose that I received the sales for 2018 and I want to update my sales report to have another column for 2018 it's very easy now I can just go to sales 2017 right click duplicate this time no problem it will trigger the power query editor now I have the new query called sales 2017 2 let me change it to 2018 and enter going back to the source very easy just change 2017 to 2018 and enter going back to the last step 2018 is OK and no errors we are good to go I can just go back to all sales going to append query let me change the settings for this append step now I have 3 or more tables and now I have all the tables on the left and the appended tables on the right just take 2018 and add and OK and here you go you have all the data now in this query in all sales query I can just go to close close and load 2 only create a connection for the new query the sales 2018 all sales already loaded I can just click on OK the report will be updated and the query is loading as you can see and the report updated with 2018 that's perfect if you go back to bikes if I do right click and refresh on this table the query will be reloaded and the table will be updated with 2018 data why because we used reference so anything that we did on all sales the end result of all sales will jump into and update the bikes report that's why we used reference instead of duplicate and I think in this discussion we understood when to use duplicate and when to use reference and how to append that was all for today and actually all for the power query basic series you can go to the description section and you'll see the link you can start to watch the entire series from the beginning and before leaving you if you subscribe to the channel please do like the video if you like it and leave a comment and see you in the next series and bye