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