Hello and welcome to this video, actually this is not a new video, it is a compilation of 17 videos that make up the full series of power query basics. If you are following our videos in this channel, this is covering 17 topics, full course, the length of this video is 5 hours, yes 5 hours and 25 or more practical examples. The content of this video will be divided into 3 parts. Part 1 are discussing extract data using power query. Video PQB01 is an introduction, PQB02 how you can extract data from the current workbook, 03 how you can extract data from other workbook and consolidate multiple worksheets, PQB04 how you can extract data from a folder and this folder contains CSV file, PQB05 how you can connect to an access database and load your data to Excel data model, PQB05 exactly the same like PQB05 however we are working on Power BI and at end we are going to create an interactive dashboard on Power BI, PQB06 how you can connect to the web using power query. Part 2 is discussing the data transformation, 7 topics of 7 videos, PQB07 how you can split columns 2 rows and how you can automate and organize chaotic input data, PQB08 append data from 3 different tables, PQB09 through PQB13 we are going to discuss merge queries we are going to start with 09 how you can merge queries and replace VLOOKUP using power query 10 how you can compare between 2 tables 11 how you can solve the duplicate issue when you are using merge queries, 12 how you can replace some if and 13 is how to perform multi dimension or 2 dimensional search using both formulas and power query and for sure using the merge techniques. Part 3 is the main theme is about how to manage queries we're going to look at excel.currentworkbook to extract data and group BI to summarize data this is in PQB14 15 how you can create a dynamic filter using power query the criteria inputs are going to be from the excel itself from the excel sheet itself and then you can also create a customized refresh button and finally we're going to look at duplicate reference or append in video PQB16 and when and how you can use each and every one of them before leaving you with the first topic let me tell you how we can navigate through this long video if you go down in the description if you hit the show more button the full description will be opened you will notice that I put the titles of each and every video on the left of each title of those videos you will see a timestamp if you click on this timestamp it will take you directly to this part or this topic and below you will see that I put also a link that you can use it to download the excel workbook or the working files and finally if you didn't subscribe to the channel please do like the video if you like it and leave me a comment and I hope that you're going to enjoy this very very long video of five hours thank you very much for your time and see you in next videos Welcome to a new video from excel did analysis series PQB power query basics this video PQB01 introduction to power query in this video we are going to try to answer these three questions why power query where we can find it in excel and how power query editor works so before jumping into excel and starting our practical example for today let's have a quick discussion around these three questions first one why power query there's a lot of reasons why we should use power query however I'm listing only three reasons here first one is because power query will help you to extract data from almost everywhere text files csv files excel workbook folders web and online services relational databases such as access sql and many many other sources the second reason is power query will help you to to make your work without using vba or macros and that's why because in the power query editor that you are going to see in a while you will find a section called applied steps and in this section you can find that all the steps that you applied to your query is recorded and you can reuse it again and again either in the same dataset or on another similar datasets the third reason is with power query you are no more limited to the one million rows limits in the normal excel sheet and guess what you should not also worry about the performance of your excel sheet you know that if you are working with 30 or 40 south end records in the excel sheet with a lot of formulas like sumifs and vlookups and all this stuff the performance of excel is very slow however with power query I promise you you will not be worried about the performance even with an excessive or massive number of records now we want to answer the question where we can find the power query in excel it depends on the version that you are working with if you are working on excel 365 like me you will find the power query in the data ribbon you will not find the term power query itself you will find something in the data ribbon called get and transform and in this section you will find everything that you need about power query this is pretty much similar to office 2016 also in the data ribbon you will find a section called get transform but if you are working in excel 2010 or 2013 you need to download the power query add in and install it and in this case you will find in a different ribbon called power query and all the icons you can see it here in the power query ribbon and finally how power query editor works the power query editor is working in a separate window this is the window of the power query editor and we are going to take a quick orientation while working on our practical example but you need to know that the power query editor is working in a separate window you cannot work in your excel workbook while working on the power query editor you have to close it first and then continue working normally in your excel workbook in our practical example today we have a small set of data it comes in column b as you can see here it is also a sales data you have here the dates you have the amount you have the percentage and also you have the sales repname but all of this data as you can see in the formula bar all of them coming in the same cell and there's a separator or the limiter between each kind of data so the dates and then you have a space semicolon and another space then the amount space semicolon space and so on and so forth and what is required we require to prepare a pivot table this pivot table reporting the sales by sales repname as you can see we have here the data for the year 2017 but also we have here another set of data for 2018 so let's start directly with the power query but before start working with power query i need to transform this data into a table format so it will be easy for power query to understand what kind of data exactly so while selecting any cell inside the data set i'm going to use the keyboard shortcut control t it will open a small dialog box confirming the range and ask you if you have headers for this data my answer is okay i'm going to press on okay and you can see the set of data changed to table format in the table design ribbon i need to give a name for this table and this is very important while working with power query you need to make sure that you are giving a meaningful name for every table or every set of data i'm going to to call this sales and enter now i'm ready i can go directly to data ribbon and here from get and transform section i'm going to use this shortcut if i hover over this shortcut you can see here from table or range i'm currently working on a table and i'm selecting a cell inside the table so power query will understand exactly what i want i'm going to click on this icon the power query editor will launch now the power query editor launched and there is four main sections in this editor the top the top portion of the power query is your menus start with file home transform add column and view we will not discuss each and every menu of these five menus however in this example we are going to use some of these buttons and some of these options and we'll discuss each one of them in details the second section on the left hand side there's an arrow here if you click on it it will expand you can see this pane called the queries it lists all the queries that you are working with in our case now we have only one query called sales inheriting the name of the table until you want to change if you jump into the right hand side you will find the query setting sections the first part of it is the properties and you can see here the name the sales as we mentioned we can just call it something else we can call it something like sales and sales rep and enter now we renamed the query and if you look on the left hand side you will see also the name changed here and this is the important section as we mentioned at the beginning the applied steps the first step always the source and if you click on it you will see the source of your data where you can see this you can see it in the formula bar it's pretty similar to excel this is a function called excel dot current book book and you can see the name of the table which is the sales and what we bring from this table the content of the table and in the middle is a preview of your data and I'm saying it's a preview because it is a read-only version of your data so whatever changes you do here it will not impact the original data although the power query is based on a great language computer language called m code however in most of the cases or I can say 95% of the cases you are not required to write any code why because this power query editor or this interface any action you take it will generate the code automatically and you can view the code in two places inside this editor the first place is the formula bar and you can see when I select the applied step change type it generates automatically a function called table the transform column types and here is the parameter of this function if I want to view the entire code of this query I can go to the view ribbon and from the advanced editor button I can click on it and it will open the advanced query editor and you can see the code the entire code of this query written here now let's start transformation required for this data and the first thing I want to do is to just get rid of this action so I'm going to use this red x and I'm going to delete the first thing I want to do is to just split this data into four columns to do this in the power query while selecting this column I'm going to home ribbon and from home ribbon you go to the transform section and you will find a button called split column the small arrow here if you press on it you have all the options for splitting column in our case we want to split by the limiter and our limiter is three characters space and semicolon and then space so there is a standard the limiter is here but not none of them is space semicolon space so I'm going to use custom when I select custom it will open a small window I can write here space semicolon and space and also power query asking you the occurrence of the limiter for me it's for each occurrence of the limiter I want you to spread the column at each occurrence of the limiter and when I press okay it will automatically spread the data as you want exactly not only this the excel trying to be smart and there is a change type action taken this column the first column is date and you can see the icon changed here and you can see something like a calendar and if you check the data type from the home ribbon you will see the type is date it's also detected that the second column contains whole numbers however in this case I want to change I want this data to be decimal number not whole number so I'm going to tell the power query I have decimal numbers here I can do this action from two places from the column itself from this icon I can just click and I can choose the decimal number or from the home ribbon from transform data type I can just click and use decimal number button and you can see it is very polite it is asking you if you want to replace the current step or add a new step for me I'm going to replace the current step and now you have all the transformation for data done now I want to rename each and every column of this query so I'm going to double click and I can start to write now I think I did all the transformation required for my query or my data and now it's time to load as we mentioned the power query is etl tool extract transform and load now we finish the extraction and also we finish the transformation and now it's time to load so to load I have from the home ribbon on the left hand side a button called close and load if I just click on it it will load the data into excel table however I prefer all the time use the close and load too it will give you this small dialog box and ask you where exactly you want to load I want to load to a table in existing worksheet and this will be F7 and I will click on okay it will produce a new table however this table is transformed the way that I did in the power query as you can see here my table now is clean one I have the date I have the amount the percentage as decimal and also I have the name of the sales and all I want to do is now is to create a pivot table it's very easy while selecting any cell inside the new table generated by the query I'm going to table design and I'm going to summarize with a pivot table it will generate a pivot table I want this table to be in the same worksheet I'm selecting this cell J5 and okay it will create the pivot table and let's drop the amount in the value right click and number format let's take the name of the sales rep on the rows and now you have your report done and all automated by power query in order to prove this is automated let's try to get the new data from 2018 let me select all control c going back to the same sheet 2017 I'm going to the original table make sure that you are going to the original table not the query generated table I'm going down to the first empty row after the table control V the table will be expanded automatically and look at this if I select this table right click and refresh the query will be refreshed and you can see the new data for 2018 is coming down here and not only this if I refresh the pivot table as well and now let's take the date in the column you can see here I have the two years 2017 and 2018 and each time you bring new data and refresh your table coming from the query will be updated and your report will be updated automatically that's all for today we try to answer the three questions where is power query why power query and how the power query is working and we saw how we can automate our work our transformation of data with power query without writing any code either in the VBA or in the power query and code itself welcome to a new video from excel data analysis series PQB power query basics video PQB02 extract data from the same workbook in this video we are going to see how we can get data from an unnamed range how we can get the same from a named range and finally how we can get the data from a table during the video we are going to discuss how we can delete columns and how we can delete other columns how we can create a dynamic unique list via removing duplicate rows and also how we can delete or change applied steps and finally how we can combine query output with excel functions in our practical example today we have the monthly sales from triple A bike shops books it comes in three columns month product and sales value you can notice that we have three months January February and March and you can see the month represented by the last date of the month and also you can see I have three copies from the same data first one I put it in a named range called q1 if you use the drop down in the name box you'll find that I have a range called bikes q1 if you select you can see here is the range named bikes q1 and also I have the same data but in unnamed range and also a copy in a table and I give a name to this table the name is bikes and you can see I have two requirements first one is to create a summary by product to do this we have to create a unique list of the products and also I need to create another summary but by month and in this case I want to create a unique list for the month and also I have the q2 sales it's here it's the other three months for q2 and I want to update the data and see if I can update this automatically without redoing any of the steps that we are going to do now so let's start with the first set of data that we have in the named range bikes q1 and while selecting any cell inside this dataset I'm going to use the data ribbon on the left hand side I have my power query section so I'm going to select this icon the small table here if you hover on it you'll see from table or range I'm going to click on this it will launch the power query editor and a new query will be created if you look at the right hand side you will see that the query inherited the name of the range so the name of the query is bikes q1 I have the source here the first step in the applied steps and other two steps applied automatically by excel I'm going to delete them look at this I can just use this red cross and I can just delete the same here the red x I can just delete and let's start to do all the work manually if you look at the data it comes here in three columns and 13 rows however the first row is the header so I want to use the first row to be the header instead of column one column two and column three that I can notice here on the top of the columns so in order to do this I'm going to go to the home ribbon and from home ribbon in the transform section I'm going to select use first row as header once you click it will promote your first row to be a header so in this case in this query I need just the name of the product so I don't need either the month or the sales value I can just select the month and from remove column I can just delete I can select the sales value right click and also remove and now I have the product name only and you can see removed columns a step created here in the applied steps however there is another way I can do this I can just again delete this step via the red x and the columns will come back again for sure don't worry I can play with the data as much as I want because this is a read only version of the data your original data still in the name range in the excel however this is a read only version of your data so let me select the column I want which is the product name I can just right click and I'm going to select remove other columns this is very helpful if you have a lot of columns and you need just a few columns now the last step is to create the unique list via removing the duplicates while selecting the column product name I can just go to reduce rows and from remove rows just to select the small arrow and remove duplicates exactly like what I have in excel now the table reduced to a small list of four rows now my query is ready I can just load it I can go to home close and load close and load two it will open the dialog box asking you where you want to load your data I can select existing worksheet and cell f5 and I want it in a table there's some other options we'll discuss later but for now let's use table and I'm going to select okay it will create a new table and you can see the queries and connection pane opened and I have one query with four rows loaded now I want to create my summary so I can add a column here I can call it sales and enter you will see the table will be expanded like any other normal table I can use a quick sum ifs formula to create my summary my sum range will be the sales value and I'm going to use f4 to fix the reference comma my criteria range will be the product again f4 to fix the reference and finally my criteria will be the product name in the new table close the brackets and enter if you use this small icon you can just overwrite all sales in this column and it will create and it will copy the formula till the end quickly you can select this small column and go to home and you can do a quick number formatting okay now you have your unique list created you have your summary using the sum if let's try to do the same in the unnamed range set of data while selecting any cell again I'm going to use the same button from data get and transform and this icon once I click on this it will open the create table dialog box I should click on okay it will open again the power query editor however it will give an automatic name for the query and this is something I don't want because it will give a name like table 1 table 2 and if you want to change it later it will not be easy because it will it will spoil the source of the query so I'm going to cancel this and advice before loading anything to power query from the same workbook please give it a name or the best way is to put it in a table and now let's do the same for the table data set while selecting any cell inside the table I'm going to use again the same button from get and transform area in the data ribbon once I click on it it will launch the power query editor on the right hand side you can see that the name of the query inherited from the table and I have two applied steps first one is the source we cannot delete it just getting the data or getting the table from the excel and the other one change type I'm going to use the red x to get rid of it and if you look at the at the data it comes here the three columns and only 12 rows why because the headers already in place and why because the table has headers and the column names in the query inherited from the table as well and this is very good because it reduces the steps required to transform your data if you are getting the data from a table so now in in this time I don't need the products I don't need the sales value so I'm going to select the month right click remove other columns you can see now I have the month it comes here with this icon meaning that the type of the data is any I want to change to date just click on this icon and select date now I want to have a unique list of the dates while selecting the column I'm going to remove rows and select remove duplicates now I have a unique list of the month three months I am ready to load my data close and load close and load two same same I'm going to use the same dialog box existing worksheet and table I'm going to queue five this time and okay it will create a new table and if you look here I have another query created called bikes with three rows loaded I can now do the same I can add a column called sales a quick sum ifs formula to calculate the summary of the sales this time my sum range is the same it's coming from the table you can see here I can just select the header and it will put the header name and it will use it in the formula and I have a video about this subject how to use the headers of the columns if you are using tables in the formula I will leave the link in the comment if you want to review and also and also on the top of the screen you will see a link you can use it to go directly to this video comma and then I need the criteria range this time is the month and I want the criteria comma this time is the month in the other table close the brackets and enter quick number formatting and double click and you have your summary ready so now I want to update the data and see what will happen so I have here the query depends on the named range and I have here the query depends on the table and I have here q2 sales let's take copy from it and go down here in q1 sales and control v and what I'm expecting is once I refresh this it will update everything from where can I refresh this I can just select any sale inside this table the table extracted from the query right click and refresh or I can just go directly to the queries and connection pane and refresh when I do this it will refresh but actually nothing happened and what do you think why nothing happened because the named range is not a dynamic range and if you try to select the q the bikes q1 from here it will stop only at row 17 and all the data below is not considered as part of the query this is not the only problem the other problem that if you check your sum if function here it's also stopped at the same place and that's why your information will not be updated so let's try to take another copy of the data and go in the right hand side in the query depending on a table I will go to the first empty cell control v you can see the table expanded automatically and because the table expanded automatically the query also will be updated automatically so if I select the query from the query Spain and I refresh you can see now I have 6 months instead of 3 months and my data updated automatically that's why I do recommend that if you are going to get any data from the same book it's better to put it in a table format because this will help you to update your data automatically that's all for today Welcome to a new video from axility analysis series pqb power query basics this video pqb is year 3 concerted budgets using power query in the live example today we are going to see how we can concerted budget numbers if we are collecting the budget numbers from different departments and we get the numbers in the same workbook but in different excel sheets how we can concerted all these templates in one pivot table using power query so we are going to see how we can get data from another workbook how we can use the sheets name in a new column in the concerted table how we can load power query to pivot table how we can edit query after loading and finally how we can unpivot month columns for better analysis with pivot tables so before we start working let's have a quick look on the data that we have today we have here the six departments of the company finance hr it marketing operations and scm and as you can see this is the opx budget so each department submitted its budget in a different worksheet in the same workbook and you can see here we have the accounts in the rows and also we have the month in the columns but we don't have the name of the department in the template itself we have it only in the name of the tab or on the name of the sheet and we want to concert it all this data in one pivot table actually we aren't going to touch the original data or the data we are going to leave it in the same workbook and we are going to start our work in a new and blank worksheet as you can see here it's a new book it's book 2 and i want to just point to this data from the power query or extract this data from this workbook using the power query so we all know that power query in the data ribbon so i'm going to data ribbon on the left hand side get and transform and i'm going to select get data and from file and from excel workbook it will open the browser and ask you exactly where the file that you want to start to work with my data here in this folder row data i'm going to select and import it will open the power query navigator in the power query navigator you'll see on the left hand side the name of your file row data.xlsx and you can see number 6 here meaning that there is six objects inside this file and you can see here our six sheets for each department and if you select any one of them you will see a preview of the data on the right hand side here now i want to select the six but for the moment i'll just select any one of them and i'm going to click on transform data this will launch the power query editor in the middle you can see a preview of the finance department data as i selected in the navigator so i want to just to do a couple of things before starting working on the data first one or just give a name to the the query it gives the finance as a name because we selected the finance sheet so i want to change this to optics and enter in the applied steps there is four steps starting with the source i want to go directly to the source and you can see here the excel dot workbook function this is the function that power query use to get the data from excel workbooks and you can see here the path of the file that we pointed to and here is the preview of the data in the source and you can see here the name is the six sheets and the nature of each one is table if you select table just from here you can see here all the data in finance if you select this table down here you see the data in the hr and so on and so forth so all i need from this table is the names and the data itself so i don't want to use any of these columns and also i want to cancel all of these automatically generated steps including the navigation that i used to select the finance data so i'm going to select the navigation step right click and delete until end and you can see the excel is polite asking you a confirmation that you want to do the steps it's okay i'm going to select delete and now i need only these two columns i'm going to select from the header name and from header also data right click and remove other columns now i have only these two columns i want to expand this data i can just click on these arrows you can see two arrows in different directions as you can see here just click on it you will see all the columns don't forget to uncheck use original column name as prefix and click on okay and you have all the data in one table here here is the finance hr it and so on and so forth now you can see column one column two column three i need just to use the first row as a header very easy from home ribbon use first row as header click you can see here the headers promoted but the first one still misleading finance i just want to give it a name like cost center and enter now i'm almost ready i just need to get rid of this header because each sheet contains header so i want to just get rid of the second header i can just select from here from january and i can scroll down uncheck and okay now i have all the data is okay the last thing before loading the data i just want to make sure the data types is okay i have the first column cost center abc that's fine because this is text abc account text no problem but now january till december you can see abc and one two three meaning that the excel isn't decided yet it's text or numeric and you can see here data type upside is any so i want just to change this i'm going to select january and click or press on shift scroll till the end select december now i selected the 12 months and from the home ribbon in transform section and from data type i'm going to select decimal number now all my data is ready i can just load i'm going to use close and load close and load two i'm fine with table for the moment i can just select b3 and okay it will load my data as you can see in one excel sheet 36 rows i have the 12 months i have the cost center that was not in the original data and now i have all the data coming together now the data is loaded into a table as you can see on the excel sheet and we have here on the right hand side the queries and connection and i can find my query optics with 36 rows loaded you can check here your six cost center you have no problem so there is two things that i want to do first of all i want to load this directly to a pivot table i can just use this insert button and just insert pivot table but i want to load this directly to a pivot table and the other thing i want to put all this month columns in one column so i have the month in one column one field for the month and also one field instead of 12 fields for the values so in order to do this i want to go again to the query i can just double click from the queries and connection double click on optics query it will launch again the power query editor i have here in the middle app review of the data and i have here the applied steps the last step that we did together was change type i want to add another step so i want to use a very good functionality called unpivot i can find it in transform in the any column you can use unpivot columns but also you can have the the lovely option of unpivot other columns so i can just select the cost center and the account and i want to unpivot the 12 column of the month so i can just go to transform any column unpivot columns and select unpivot other columns you will see quickly all the month came in one column called attribute i can just double click and call it month and enter so i did a quick renaming and also we have the values in one column i can just leave it as value but i can also filter out the zeros so the data i can get rid of the unneeded data and you can see here the three steps i added stored now the unpivot and the rename column and filtering the zeros if you go one step before you can see the data here 12 months in the columns one step after now the data coming in the right direction so i can just close from here from the x on the top right and he will ask you if you want to keep or discard for sure i want to keep this is exactly like saving and you will see here the table dimension changed and you can see now the apex query is 192 rows instead of the 36 rows last one i want to just load this directly to a pivot table instead of just creating a manual pivot table from this table i can go again to the same query and right click and i can use load to it will open the option and i want to choose this time pivot table report instead of table once you click on okay it will ask you if you want to get rid of this table i have no problem and it will create as you can see here a new pivot table i can quickly create a report let me put the cost centers in the rows and the amount or the value in the values you can see here the values and also i can put the accounts on top of the report in the filters so you can see the advertising you can see only in the marketing department look at the insurance it comes in the finance look at the maintenance it comes in more than one cost center right click and quick number formatting and now what if i want to add another cost center let's say that this is the half of the cost centers i'm just doing a dry run and more cost centers will come this should be amended in the original data and let's see what will happen it will be automatically updated but we need to go back to the original data and do some changes and let's put some new data in this original workbook i have here my six cost centers i'm going to add a new cost center just i'm copying the same worksheet i'm i'm going to rename it sales and enter let me delete all these numbers and let's put only one number in sales let's say 1000 and i'm going to copy it till end for the 12 months and now i can just save and close and let's go back to our pivot table i'm going to right click and refresh you can see here the query is refreshing 204 instead of 192 and look at this this is the new 12 000 coming from the sales cost centers meaning that all the transactions all the applied steps that was working in this power query on the right hand side is applied to the new data and i can see my table is coming my pivot table is just one refresh all the information coming correctly and all these applied steps applied to the new data very nice and very quick thank you very much for your time this is all for today welcome to a new video from excelite analysis series pqb power query basics this video pqb04 extract data from folders in this video are going to see how we can extract data from a folder containing csv files how we can use the file names as a new column in the query and how we can merge three columns together to create a new date column and also are going to see how we can load the query directly to a pivot table to format or to create a sales report and finally when we put a new csv file into the folder the report will be automatically updated so before jumping to excel let's have a look on the data that we are going to use to create our sales report through the power query as you can see we have a folder containing three files one of them is text file we are not going to use it we are going to use the other two files which is a csv file containing the sales information for two years sales transactions for 2016 and sales transactions for 2017 both of these two files containing more than one million rows and you can see the size of the file in 34 megabytes each file of them containing data like this the category the name of the category of the product and also the name of the product and also we have the quantity and the sales value and we have partial information about the date we have the number of the day we have the number of the month but we don't have the number of the year which is as you can see we can get it from the name of the file itself so what we want to do is to create a new column here using this information and to create a new column here containing the full date as you can see once we finish the transformation we need to load this data into a pivot table as you can see a pivot table report containing the data for the two years not only this when we add a new file to the folder containing the sales for 2018 and we refresh the query and the pivot table the table or the the pivot table report will be updated with the new year information so in our practical example we are going to start with a new workbook as you can see it's a clean workbook we just give it a name however all the information will be extracted from the folder that containing the csv file so to start doing this we need to go to the data ribbon because the power query we can see in this left section called get and transform we are going to get data and from file this time we are going to go down up to from folder and a small window will pop up asking you where exactly your folder i can use this button to browse or i can just paste copy and paste the path of the folder inside this bar once i click on okay the navigator window will be open the power query navigation window will be opened and as you can see here i have the content of this folder displayed in this navigator and i have a lot of options down here i have combined load and transform data and we usually use transform data once i select transform data the power query editor will be launched in the power query editor i will see only the information about each file inside this folder as you can see i have here the three files one is text file and two is csv files i want to work only with the csv files and whenever i add new files to this folder i need the power query only work with the csv so i'm going to select from this column the extension i'm going to select the filter and i'm going to filter out dot txt once i click and okay you can see here only i have only two files sales for 2016 and for 2017 and you can see in the applied steps i have the first step the source and then filtered rows so what i want to do next is to extract out the 2016 from this text from the name of the sheet or the name of the file so i'm going to select as you can see select this column from the header and i'm going to transform and i'm going to select split columns for this case i'm going to use split by position once i select this it will open a small dialog box asking you how you want to split this i want to split by position and i can use the position of the of the text so the first word is sales t i don't want the first six i want to start after the six so i'm going to write six and then i want to extract one two three four which is six seven eight nine so i want him to split again starting from 10 so i'm going to write six and 10 and then i'm going to click on okay what will happen here you will have 2016 and then dot csv split and 2017 dot csv also split and you can notice that the column header changed to name dot 1 and name dot 2 and also a new step called split column by position and another automatic step called change type i'm going to get rid of this for now because i'm going to change the types later manually now if you look here in the content here is the content here is where the actual data in this two csv files lying if you select in this empty space you can see here here is your csv file and you can see it's 34 megabytes so what i want to do is to extract this data i can use this button which is combining the files together but if i did this it will ignore all this information and you know that i need the year number in order to complete the date fields so i'm not going to do this and instead of doing that i'm going to add new column add custom column once i select add custom column the custom column a window will open ask you exactly what you want to name this column for me i'm going to use custom i'm going not going to change it for the moment and in the formula section i'm going to write a new formula called csv dot document as you can see because i'm using office 65 it gives you a help like this you can just click on it and it will help you but if you don't you can just write it yourself and make sure that you are writing in the right syntax meaning that you are writing in the right spelling and also you have to consider the upper case and lower case because power query is a case sensitive language so i'm going to open a bracket once i open the bracket i want to put inside this formula the content column so i can see all the available columns here i'm going to select content and insert from here then i need to close the bracket you can see here the no syntax error so you are good to go once i click on okay you can see a new column added here called custom as i didn't change the default name given by power query now if you select here you can see two tables if i select the table from the right area here you can see here here is your actual data and now you can just expand and you will get all this data inside the power query edit before doing this i need only in this case this information the information for the year and also the information for the two tables or the custom column that i added so i'm going to select the first one and i'm going to scroll and also i'm going to press on control and i'm going to select name dot one column and then right click and i can use remove other columns now i have the table and i have the name of the year as you can see here and now i can start to split my tables i'm going to use these two arrows from the header of the of the column here are the all all the columns that you have inside the table but don't forget to uncheck use original column name as prefix and then click on okay all data will be expanded the two files together and all information now in one view as you can see so the first thing i want to do now is to promote the headers because you can see column one column two and then you have the headers here so first thing i want to do is to upgrade or use the first row as header for the column i can easily do this from home use first rows as headers now it's okay the only thing that i want to change in the header names is the column of the year because you can see here it's 2016 but let's leave it for now because we are going to merge this with the other information for the date so before doing anything please don't forget to get rid of the second header means what we have two files each file of them containing a header we used the first header the first row of the first file as a header for this combined table but inside this data there's still another header i want to get rid of it if you want to do this let's go to something like category let's open the filter you can see here all the categories we have the four categories you can see also load more because the list may be incomplete because as you can see here the preview on the power queryator is only working for around a thousand rows however i know that inside this file we have more than two million rows so let me do the load more it it will be loading from the two csv files and give you all the option that you may need inside this filter it will take a few seconds as you can see working on the three million records now the list is complete i want to uncheck category because category is the header of the column not data inside the column let me uncheck and click on okay now i want to start to build the date i have the day i have the month and i have also the year but we have to be very careful when we are building a date from separate columns we need to understand or to know first what is the order that will be accepted by power query because some countries are using the day the day first then the month and then the year and some other countries start with the month like the united states so let's check our regional settings first i can do this from file option and settings query options it will open a new window in this new window you can exactly what settings you are using from regional settings here you can check that you are working with the english united states meaning that you need to start with the month first so i'm going to select month first i'm going to use control and then day and finally i'm going to scroll and go to select year now i selected the three columns in the right order now i can go to transform and do something called merge columns it will open a new window asking you what separated you want to put between the data inside these columns for me i'm going to use custom and i'm going to use this dash and i need to give a name for the new column let me call it date then i'm going to click on okay as you can see here i have a column called date and the default is the information is abc as you can see here abc meaning that it is a text this is not good for dates so i need to change this to date now i have all transformation done you can check the data type before loading you have category abc meaning it is text same for product quantity one two three meaning it is whole number one two meaning that it is a decimal number and finally you have this calendar icon meaning that you are looking at dates now your information is ready you can start to load it to the excel sheet you can go to home and from home you can use close and load close and load two it will open the loading window import data window you are going to use the pivot table report and i'm going to use the existing worksheet let's put it in b2 and click on okay once i clicked on okay the query will start to connect to the data source and you can see the rows are loaded like here and the counter is indicating how many rows loading till the moment now we are 130 and it is continue counting till the end of the 2 million rows as you can see here 2 million records more than 2 million records actually loaded and the queries and connection been opened and also the pivot table already created you can see here the pivot table list you can quickly prepare your report i will start by dropping the date inside the rows once i do this the date will be grouped automatically you can see here the dates already grouped i have years and then quarters and then month let me take out the quarters i will also take out the date which is the month currently and i can put the years in the columns then let me drop the sales value in the values i can do a quick number formatting currency zero decimal places and okay then i can start to put the products in the rows and category above the product so i have the products classified by category and now you have your sales report completed and finally let's have the new file for 2018 and drop it in the same folder here is for the data let's go up in the upper folder and let me take from here sales 2018 control x to cut back to our data file control v to paste once i go back to my query my pivot table i can just go to pivot table analyze and refresh or i can go from data and use the refresh all button here it will update the query you can see here the rows start to load again and once the query finished also the pivot table will be updated and this will take something like 15 to 20 seconds once the loading of the 3 million rows done you can see here automatically the information for 2018 automatically updated without repeating any of the steps that we did to perform or to create this report that was all for today welcome to a new video from excel data analysis series pqb or power query basics this video pqb 05 we are going to see how we can extract data from an access database we are going to see how we can extract the sales transaction from the access database and also we are going to build on the relation inside this access so we can retrieve more data from other tables and also we are going to see how we can add custom column to calculate the sales revenue also we are going to load the query into the data model instead of the normal excel table or pivot table and this will speed up the reporting process big time also we are going to check our data inside the power pivot editor so we can check the data it's around 3 million records inside the excel but in the power pivot editor and also we're going to see how we can add measures to calculate sales total sales quantity and average price using the dex formula or data analysis expression formulas and finally we are going to create a pivot table so we can generate our sales report so before going to excel and start working on our example today let's have a look on the access database to start understand our data so we have the in the access database we have three tables first one is the sales table which is the sales transaction as you can see here it's more than 3 million records and the fields that we have in this table is the product ID the region ID the date of the transaction the quantity and finally the discount as you can see here there is no names for the product or for the region and also we don't have the price for the transaction so we cannot using this table standalone table we cannot calculate the revenue therefore we need to capitalize on the two dimensional table or lookup tables first one is the product as you can see here we have the product ID which is the primary key that we are going to use or already used to have a relation with the sales table we have also the product name the category name and the price and we are going to use this price to calculate the revenue the sales revenue and in the region table you will see the region name and the region ID finally you can see here in the diagram view here you can see the product table and the region table both of them are lookup tables and you can see in the middle we have the sales tea table which is the transaction table and you can see the one to many relation coming from the lookup table in both cases to the sales tea table so we have here the primary key on the product ID field and here on the other side we have the foreign key product ID also but in the transaction table same for region we have here the primary key the region ID and the foreign key region ID inside the sales tea so let's go back to excel and see how we are going to extract this data into the power query so as usual when we start work with data from outside excel we normally start with a blank sheet we start with a new workbook and because we are going to work with power query we have to go to the data ribbon on the left hand side you'll see the get and transform data section and from get data we are going to select from database and from microsoft access database it will open a small browser asking you where exactly you store your access database so i'm going to select my access database sales db and click on import once i click on import it will open the navigation window for power query i can see on the left hand side my three tables product region and sales transactions i can check this box and select and select multiple tables but for me i want only to select the sales tea i'm going to select from the left pain on the right hand side app review for the data will be produced as you can see here is my field port id region date and i have the quantity the discount and two other tables that were not in my original table however we are going to see why we have these two tables in a while i'm going to click on transform it will launch the power query editor on the right hand side you can see your applied steps i have two steps first one is the source you can see here the three tables and the navigation when i select the sales tea table and you have here in the center you can see the preview of your data you have your feeds here product id region id date and then quantity discount and you have these two new columns if you remember when we look at the access database there was a relation between the product id or the product id from the sales tea table and the product id inside the product table that's why we have here this column representing the relation that we had in the microsoft access database same for region we have the relation between the region table and the sales tea table based on the region id so if you if you click here on the right hand side on the empty space in the right here you will see the preview of the data for just only this record so you can see here the product id 2009 and here is the match 2009 the name product name category name the price and you can see here sales table so this is coming from the table product and the same for the region if you click here you can see a preview of the equivalent record in the region table so you can see here kairu 2002 2002 this is the region id and here is the region so what i can do how can i use this data i can just click on the expand button so i can get the information that i want so don't forget to uncheck use original column name as prefix unselect all i need only the product name the category and the price i will click on okay it will expand the three columns and this column will disappear and you can see it give automatic names like how it was named in the other table but for this the first column you can see product one because the original column was named product so i want to change this i can just do it from the formula bar here i can just select carefully from upside here and backspace backspace and enter now i have this column renamed i will do the same for the region i'm going to expand this record i'm going to uncheck select all columns i'm going to select only the region and click on okay it will come also like region dot one let me do the same here i will select from the formula bar backspace backspace and enter so now i have three columns coming from the table product and one column coming from region product now i have the price i have the discount and also i have the quantity so i can easily calculate the value of the sales so i'm going to add column custom column let me give a name for this column so i'm going to give the name inside the custom column window let me call it sales revenue so i'm going to go down in the formula section i'm going to just do a very simple calculation i'm going to select from the available columns i'm going to select the quantity i'm going to click on insert and then asterisk to perform a multiplication and i'm going to select the price double click it will go directly here another asterisk open bracket one minus and then i'm going to select discount close the bracket now i'm ready no syntax error have been detected i am ready i'm going to click on okay you are going to see here a new column added with the sales revenue but you can see here that data type is not proper it's abc and one two three together meaning that it is data type of any you can check it here from the home ribbon in the transform section you can see the type is any i want to make sure that the other data types is okay so it's okay for the product id it's better to have it a whole number same for region id let's put it as a whole number for the date it's better to select date instead of date and time for the quantity it's a whole number as well discount i can see a percentage i can change the percentage for the product name and category name it's okay to have a text the price 1.2 meaning it's a decimal which is fine i have the region abc this is okay and the sales revenue i can use currency now we have the sales revenue and the type is currency i think now we are ready we can just load our query inside the data model so now i can choose close and load close and load two it will close the power query editor and it will open a new dialog box asking you where to import we saw together before how we can import a table how we can import to a pivot table but this time we want to import to the data model and this will speed up your reporting a big time but you can see here anything saying that you can load that data model however you can choose uh only create connection and then you can check here add this data to the data model and click on okay once you did that you will see the queries and connection open the queries and connection pane opened and a new query created here called sales t and your data now is loading the three million records are going to be loaded here now your three million records loaded and it's loaded into the excel data model if you want to check where this data loaded or where this information loaded you can go to the data ribbon and from data tools you will find this green icon you can just click on it or from power pivot ribbon you will see the manage in the data model section you'll see the manage icon you can also use it if you don't have the power pivot ribbon in your menu you can just activate it from file then options and then you go to add ins if you scroll down into add ins you will see manage excel add ins from the drop down menu you can select come add ins and go you will see the microsoft power pivot for excel this is the add in you need to activate and then you click on okay so let's click on manage and this will open the power pivot editor here we go there is the power pivot editor you can see here your table sales t and you can see here in the records bar you can see the three million records counter you can scroll down you can view only you can only view your data you cannot edit your data from here you can just add a new column or you can use the section here below the gray line which called the measure grad you can add measures down here so let's try to add a couple of measures so we can use it in the pivot table when we produce the sales report no matter where in the grad you can add everywhere you can add anywhere in the in the grad and if you want to add a measure you can just start type or you can go to the formula bar and you have to start by giving a name to your measure so for this case let's add a measure called total sales revenue and then you have to put double dots or the column here and then equal once you write the equal here you start writing your measure so we are going to use the sum function it is similar like excel but it is a formula let's write sum and you can see like excel it will give you some help so you have plenty of formulas starting with sum will choose the first one i can just double click using the mouse and then it will prompt you to the parameters that required by this function and remember here we don't have cell reference we have only tables and columns so i need to start with my table so my table is sales the only table i have is sales t and i need to select the right column inside this table so i'm going to select sales revenue and double click and then close the parentheses and click on enter the measure will be calculated and here you go if you expand this column you can see this is the total of the column sales revenue the very good thing about this dex formula you can attach a number formatting for for this measure whenever you use the measure in any pivot table after that you will see the same formatting no change in formatting so let's add some number formatting i'm going to use the currency so i'm going to use the english united states and you can see here you have your number formatting applied and i'm going to reduce the decimal places to zero so i don't have any decimal places in my number let's add another one which is total quantity or total q colon equal sum and tap i can use tap again sales i'm going to scroll down with my or go down by the arrows up to quantity and tap close the bracket and enter let's also do some number formatting this time i'm using the southern separator and also zero decimal places here is my total quantity for the entire period and also i can add something like average price colon equal and then i want to i want to divide the total sales over the total quantity so these these are two measures so i can quickly use the square bracket when you when you start with the square bracket you will see your measures here so the first one i want to use is the sales revenue double click and then over another square bracket and i'm going to select this time total q double click and enter this is perfect i need only two decimal places so i'm going to reduce the decimal places to only two and now i am ready this is a little bit different from the power query editor so you can just using the mouse you can toggle between the normal excel window and the power pivot editor or can also use the alt tap so you can toggle between the two windows normally but i prefer when you stop working on your power pivot editor you can just close it from here and don't worry you can still save normally from the normal excel window i think now we are ready we can create our pivot table but from where we are going to create this we have two options we can go back to manage to the power pivot editor and go back to the home ribbon and i have here a pivot table icon i can just use it or i can close this and go to the answer the normal answer to window and i can choose pivot table once you choose the pivot table the normal create pivot table window will open and because i'm not selecting any data and we have a data model in this excel workbook it's automatically directing you to use this workbook data model and in existing worksheet i'm going to use b3 no problem and then i'm going to click on okay it will create a pivot table pivot table one let me give it a name like sales report and enter let me close also queries and connections so we have enough space you can see here the pivot table fields and you can see your table here and you can notice that this small database icon here inside the icon of the table meaning that this pivot table is based on a data model i will expand let's start with our measures you can see here the three measures that we created and the three measures start with a small icon like this f and x meaning that this is a measure so let's start with the total sales revenue i'll put it in the values you can see here it's coming with the formatting no need to reformat your numbers let me put also the quantity and also the average price you have here your three your three measures and let's do some slice and dice i'm going to use now the category let's take the date and put it above the category and the default for this case it will not be grouped and i want to group it by year so i'm going to right click and go to group and i'm going to select years only here you go you have the three years and divided or classified by category and you can see here your measure is working very properly with all the slice and dice in the pivot table even the average price it calculates at each level you put or use in your pivot table let me add a subtotal here i would prefer to put the subtotal above the numbers so let's go to the report layout and show in compact form you have here the subtotal above your group i can just copy this go to the right control v i can quickly adjust the width of the columns and now i have a new pivot table i can quickly change the layout let me take out the category and let me put instead the region so i have the region name here below the years you can see how the pivot table is refreshing very quickly you can see here i have two reports one yearly report categorized by the category and the other one by the region very quick and very efficient and whenever you add new value to your access database you just need to refresh these pivot tables and your report will automatically be updated the query will be updated the pivot table or the power pivot will be updated and then the pivot table will be updated as well i think it's very easy that was all for today welcome to a new video from excel data analysis series pqb or power query basics this video pqb 5.5 why 0.5 because we are going to use the same example that we used in the previous video pqb 05 however this time we are going to use the power query in power bi instead on power query in excel so in this video i'm going to see how we can extract sales transactions from an access database exactly like what we did in the power query in excel also are going to expand the columns and retrieve more data from the same database and also are going to use the add custom column to calculate the sales revenue we are going to load our query finally into a data model the same data model that we have exactly in excel and also we are going to use close and apply instead of close and load two we are going to check our data it's more than 3 million records but in a data view instead of power pivot editor and also are going to add measures using the DAX formulas exactly like the DAX that we are we are using in excel and finally instead of generating our report on a pivot table we are going to report our information in an interactive dashboard using a table map bar chart and a slicer in the access database we have three tables as you can see here we have the sales transactions or sales t we have the product id inside this table the city code the date quantity and discount and also we have two lookup tables as you can see the location and the product in the location you can see the city name and the city code and this relation between the city code in the sales t and the city code in the location table also we have the product table we have the product id the name of the product the category of the product and finally the price and also this relation between these two tables الحلوات المستخدمة للمدعومة أو للمدعومة هو التشكيل الأجول. لذا هناك نحن نذهب. This is the Power BI Desktop. يمكنك أن تدعي this application from Microsoft's website or you can download it from Microsoft Store. And this is much better because the new updates will go directly to your application. Whatever we are going to use it will be only on the power BI desktop however if you look here in the screen this is the screen that prompts whenever you open the power BI desktop you will find that it asks you to sign in when you sign in will have you will have an access to the cloud or the BI services and then you can share your work with your colleagues and also we can use the free trial it's for 60 days if you use the free trial it will ask you to subscribe with a business email and then you can start to use the feature of sharing and creating dashboards on the BI services or BI pro for us we are going to use only the power BI desktop for this video so we can start from here we can start to hit the get data button or we can just close this and start to work on the normal window and this is the normal window whenever you open a blank document you will see a window like this from home ribbon you can go directly to get data and get data is the power query for the power BI so I can just hit get data and I need to find the database or from database you can see here there is no access database in these options so I need to click more and I can choose access database and then connect will open the browser you can just select your access database file and then open it will open a navigator I have only three tables location product and sales tea I'm concerned with only sales tea let me check this box it will start to preview your data inside this preview window and you can see here your columns so you are good to go so I can just start to load but usually we use a transform data it is exactly like edit in the Microsoft Excel power query so I'm going to start with transform it will open the power query editor it is pretty much similar to the power query editor in Microsoft Excel so on the right hand side the query settings the applied steps I have the source which is the access database then the navigation the data I selected for the sales tea on the left hand side I have the query names and in the middle I have all the data or preview of the data the preview of my data I have the four columns coming from the sales tea table from the access database Perth ID city code date quantity discount and also I have two additional columns created because of the relation between the sales tea table and the two other tables the location and the product inside the access database so the access database sent information to power BI telling that there is a relation that you can use and get additional information from the two other tables so from the location I want to get the city so I'm going to open or use the expand button it will open this small window let's uncheck or unselect use original column name as prefix and also unselect all columns and I'm going to select only the city same for product I'm going to expand let me uncheck select all columns I'm going to select only product name category and price and click on OK now I have all the information needed I have also the price of the product I have the discount percent and also I have the quantity I can use all these three information to calculate the sales revenue because I don't have the sales revenue in this table so I'm going to use the add custom column from the add column ribbon custom column exactly like what we are doing in Excel in Excel power query for sure so I'm going to use this window to start with giving a name to this field we'll call it sales value and then going below the new column name to the custom column formula section so I can start to select my fields and do my formula I will start with the quantity I can select and double click it will jump directly to the left-hand side and then I'm going to multiply by the price quantity times price and also I need to multiply by one minus discount open bracket one minus and then I have a discount I can either select the insert here or just double-click and then close the bracket whenever I click on OK it will create the new column however I have an issue a small issue with the date type because you can see it's ABC and 123 meaning that it is any the power query editor see it as any and you can check here also from home you can see the type is any I want to change this to currency let's say so I'm going to select the drop-down I can select currency here and then price 1.2 meaning decimal number it's OK ABC ABC ABC meaning text for the three columns for category product name and city that's fine discount I think it's proper to use a percentage that's fine I can just click on the icon here and change to percentage for the quantity and also the city code and product ID I need all of them to be whole number I can just go to a home ribbon and from data type I will change decimal number to whole number I can change the date and time here to only a date you can see here date and time I will change only to date and this is fine all my data types is fine I am good to go I can load this to the data model I'm going to use close and apply it is pretty much similar to close and load to that we have in Microsoft Excel power query it's in the home ribbon on the left-hand side close and apply it will load all the information to the data model inside the power BI now the three million records already loaded to the data model and you can notice here on the right-hand side you can start to see the fields of the table you can see the sales T table and the fields that we have inside this query inside the table or inside our query and you can see here is the report view we have in power BI 3 views that you can work with on the left-hand side you have the 3 view here the first one is the one that with the icon like a chart or something like this this is the report view and here in this canvas you can start to use the fields and the visualizations and to start to build your dashboard start to build your report you start with page 1 and then you can add page 2 then you can add page 3 it's up to you how many pages in your report for us will have only one page for this report and we can start change the name of this page we can call it sales report and enter exactly like you are giving a name to a sheet or a tab in Microsoft Excel the other view that we can use is the data view if you click on the data view you can see the table we have only one table in this data model because we loaded only one table and you can preview all the data you can scroll down till the end and you can check the number of rows here downstairs you can see table sales T and you can see the 3 million records you can use this view also to add columns if you go to modeling you will have an option to add a new column you can also add a new table and you can add a measure and this is exactly what we are going to do now what we want to do is to have a summation for the sales value and also for the quantity and then to calculate the average price and if you want to add a new measure you just need to click from calculation here from the modeling tab you just need to hit the new measure button it will prompt you inside a formula bar you can see here is the formula bar and by default it will give a name to your measure or your calculation it will call it a measure and you have to give a meaningful name to your measure so we are going to change the default and we are going to write something like total sales and then equal and we want to just have a summation for the sales value we are going to write a simple DX formula it is the function sum it's pretty much similar to the one that we have in Excel whenever you start to write it will give you some assistance double click it will open automatically a bracket and it will give you the options we have only one table you can see here the sales T is our table and here is all the option which is all the fields inside the sales T table so I'm going to just double click close the bracket and hit on enter my measure will be added you can see in the fields here a new field added called total sales and you can notice the calculator icon here indicating that this is a calculated measure I can also add number formatting when you are selecting your measure here you can go to the modeling tab and you can select from formatting whatever formatting you want it's proper to use the currency so English United States is okay and zero decimal places I want to add a new measure no problem just click on new measure again it will prompt you to the formula bar and it gives you again the default and writing measure so I need to change this again let's call this measure total quantity let's try to write the same DX function some tab and then I'm going to select sales T again quantity and then tab close the bracket and enter and let's add some number formatting let's add the final one again I'm going to hit the new measure it will prompt me again to the formula bar and giving me the word measure as a default let me change this to average price and then equal now I want to divide two measures already calculated measures so if I want to call the measures I can start with the open square bracket it will give me a list of all the measures I want to start with the total sales I'm going to select with the arrows and then tab and then over again square bracket now the quantity tab and enter the third measure created you can see here the average price with the calculator sign or calculator icon also total quantity and finally the total sales let me show you the model view if you click on the model view you can see here your table as a small box with all the fields listed below the title of the table or the name of the table and if I have more than one table I will see more boxes like this I can use this window to create relations between my tables exactly like what I'm doing in the excel data model or what I'm doing in the access databases and also from this view I can do some organization to my table because you can see that I have a very long list of fields when I go to the report view it will not be easy to select the correct field so I need my three measures the average price total quantity and sales but there is some field that I don't need like the product ID so I want to hide the product ID from the report view so I'm going to select it right click and I can just hit hide in report view so I can do so for some other fields like sales code like the price I can do also the same for this count the category I need it I don't need the quantity or the sales value because instead of them I'm going to use my measures I can select all of them right click and hide from report view you can see the icon changed and when I go to the report view I will see only the category city date product name and this three measures so now let's go back to the report view you can see a smarter list and shorter list so before start building the report if you look at the date the hierarchy of the date you can just click on this arrow you will see the date hierarchy it's four categories or four levels the higher level is the year then quarter month and day so the benefit of the hierarchy whenever we put it in a table or an individual you can start to drill up and down through this multi levels so it's very useful and the date hierarchy is just created automatically by Power BI whenever it sends that there's a date inside your model so I need to build another hierarchy in my data so I have the category and the product name can find the relation between them because I have like four category and for each category there is a specific number of names related to each category so I can start to build a hierarchy between these two fields so I can start by selecting the category I can use these three dots and it will open a small menu and I can select new hierarchy you can see here a new hierarchy created by default it's called category hierarchy and the first level in the hierarchy is the category field I want to add another field so I'm going to product name again I'm going to use the three dots it will open the same menu and then I will select add to hierarchy and category hierarchy now you can see a new field added to the category hierarchy which is product name and make sure that you have the right order meaning that the highest category should be on top and then the lower category and so on and so forth so category it's correct to be at the beginning of the hierarchy then the product name now I think everything is ready I can start to directly build my dashboard or my sales report I want to start with the sales total so whenever I select the sales total field and just drag it and drop it inside the canvas it will create a graph this is the default whenever you drop value amount and numeric value inside the canvas it will produce this column chart like this so for me I want this to be changed to a table so let me go to the visualization while selecting this visual and I'm going to select the matrix matrix is pretty much similar to the pivot table let me resize it and put it down here because you can see it's very small here I can just go to this focus mode button just click on it it will put the report in the middle so I have here the total sales for the three years this data is for three years you can see total sales 190 million let me start with the new hierarchy created I will take the category hierarchy and just drop it inside here you will see it will start with the highest level of the hierarchy which is the category accessories bikes clothing and components I want to drill it down you can see this small plus it is exactly like the one we have inside the pivot table in Excel so I'm just going to expand the four categories so that's fantastic I have the categories and below every category I have the product names I can scroll down until the end I have the subtotals let's add also the total quantity I'll just drop it inside here it will create a new column with the quantity and also let's drop the average price so I have a very good report let's go back to the report view I think we can just do a quick resizing now we are ready to continue our report so now I have also a city if you look at the city inside the model view you can see the city column if you just use the filter to check you have the city names I have seven city names all inside Egypt if you go back to the report view and you just take the city just select the city and drop it in the cannabis you can't imagine what power BI will do automatically it will create a map like you can see here it's a map for Egypt and you have the seven cities here here you can see Luxor and here you can see شرم شيخ كاير وانجيزا عليكزاندراء and also it's very nice I can also put something like the sales total while selecting the map I can just check the total sales it will add this dimension inside the map you can see here the circles is much bigger the big circle will represent the higher sales volume and the smaller circles will represent a smaller amount of sales so now we have a report a matrix report and also we have a map we can add also another graph or another visual we can again go to the fields and reselect the total sales and drop it inside the empty space again it will create a new chart or a new visual it's a column chart I won't change this to clustered bar chart and now let me add again the category hierarchy I'll just take it and drop it inside the chart you can see it's already created the four categories on the bar chart if you want to expand and see all the products you can use this double arrow here it's connected double arrow this is the expand icon you can just click on it you will see all the products together here I can drill up using the this drill up button it's now back if I want to drill on only one category I can just click on the turn on drill down it will have this black circle around the arrow and whenever I click on any of the categories it will drill down to the category only so I selected the bikes and you can see now it's drilled down to the cargo bikes mountain bikes touring bikes and road bikes and you will notice also as you can see here the table is filtered to bikes only if I want to go the level up back the report will come back I can also filter the chart and the table from the map if I selected Alexandria you will see only all the table will change to Alexandria and the same for chart you can see the highlighted space is the bikes sold in Alexandria I can just select again like I am unchecking the filter I can also do filtration I can just select the mountain bike from this report and all the other charts all the other visuals will be filtered according to my selection another click it will uncheck the filter or unselect the filter I can also add a title to this report so let's add a quick title from the home ribbon I can go to text box it will open a text box this company called triple A bikes and I'm doing a sales report this is a good title I can just select all I can use a bigger font actually let's increase it to 40 that's good I can use italic I can also use the center I can just resize the text box let's put it on top I can also add some background while selecting the text box I can go to visualization here I can choose the background and I can choose a color like the light gray like this one I have here by region or by city and also I have here by category and here I can check also the individual products I need also some controls for this report working with the time because I have inside this report 3 years together so the proper visualization for this will be a slicer and here is the icon for the slicer you can see it here I can just select any empty place inside my report and just click on the slicer icon it will create an empty slicer like this I can go in the date hierarchy and select only the year the slicer for year created but it is not very good like this I want to change the type of this slicer I can just select the drop down and let me select list it will create a list with the years but actually it is in a vertical direction I want them to be on a horizontal direction because I am planning to insert this slicer in here in this small empty space so let's go to the visualization area let's select this brush and we can go directly to general and from general I can see the orientation of the slicer I can change to horizontal it will create this 3 buttons I can just resize and now we have the slicer for the 3 years I can select 2016 the entire report will be filtered for 2016 I can just select bikes so the map and also the report will be filtered for bikes so I can see here Alexandria only for bikes and this report is only for bikes and also for 2016 I can just change again this 2018 everything will be changed all the report will be 2018 and so on and so forth I think it's very nice I can just close this pane and also this one and I can also shrink the menus so you can see the entire report together I think it's very nice and easy and pretty much similar to what we have in the Excel the big difference is only about how to create the visuals inside Power BI but what you have in the Power Query is pretty much similar and also for the Data Model so that was all for today Welcome to a new video from Excel Data Analysis Series PQB or Power Query Basics PQB06 we are going to see how we can connect to the web using Power Query we are going to use Power Query to connect to finance.joho.com we are going to extract the Google's historical share prices we are going to transform the data using the Power Query Editor then we are going to load the transformed data to a table and we will use the amazing stock chart and then we are going to see how we can refresh the query to get the updated stock prices every day and finally we are going to see how we can copy the query change the source and get Facebook historical share price in few seconds so before jumping into Excel let's have a look on the website that we are going to use or we are going to connect to using Power Query here you go this is Yahoo Finance and here is the URL that we are going to copy to use it in the Power Query and you can see here the historical data of Google share price you can see the data coming in six or seven columns on the left-hand side you have the date and then the open price the high price low price close adjusted close and finally the volume and you can see last date was 27 of December and you have the dates it's sorted like descending as you can see here in Excel because we are going to extract data from an external source so we are going to start from a new workbook a blank workbook as you can see here and you know that Power Query is in the data ribbon so we are going to select the data ribbon on the left-hand side we have the get and transform section we are going to use the get data and we are going to select this time from other sources and then from web it will trigger a small window asking you what is the exact URL that you want to use to extract data you can either write in this small window or you can just paste the URL and here you go this is the URL and then I'm going to click OK it will open the Power Query navigator on the left-hand side you can see the name of the URL or the name of the website and then you can see the content of the website we have like one document and 1, 2, 3, 4 table I think our data in table 2 once I select table 2 on the right-hand side you can see a preview of your data you can also go to the web view here so you can view the data in the website to make sure that you are in the correct place inside this website and you can see here our data and from table view you can view your data we are going to select transform so we can do some transformation inside the power query editor on the right-hand side you have the query settings first section is the name of the query and you can see the name is table 2 which is not a good name because we have to give a meaningful name so let me call it Google share price and enter and below you can see the applied steps we have 3 steps first one is the source here is the content of the website and the navigation step when we selected the data from table 2 containing the historical data of the share price and finally change type when excel try to discover the correct data type for each column I think the automatic data type change is not bad I have the date in date format and the other numbers in 1.2 meaning that it is a decimal number and finally the volume 1 2 3 meaning that it's whole number which also I think it is okay and now I want to do some transformation let's start from the date I have the date in descending order I will select this arrow on the right-hand side it will open the filter view it is pretty much similar to what we have normal excel and I'm going to select sort ascending here you go the data is now sorted correctly also I need to have the month name extracted in a separate column because when I put this on a chart it will not be easy to read the entire data together so let me select this column and from add column I can go directly to from date and time I will select date and then month and from month I'm going to select month name it will create a new column on the right-hand side and you can see the new step applied here the applied step is recorded here and also I need to take this column to the left so I'm going to drag it I'm going to select from the header and drag it to the left of the query so it will be the first column the month name and then date also you can see here I have the close and adjusted close I need only one of these two so let me get rid of the close I'm going to select from the header right click and then remove and finally I want to take the volume to be the first column after the date because when I'm going to draw the chart the stock chart it requires a specific order the order should start with the volume and then the open high then low and close so I'm going to take the volume and drag it to put it just after the date so now I have the data in the correct order I think now everything is going good I can now close and load let me go to home and then close and load close and load to it will open the import data window I need it in the existing worksheet and also in a table let me put it in A15 and then okay my query will be loaded and a new table will be created here so the final step is to draw the chart so let me start to select my data I'll start with the date column till the end of the column and then I will select till the end of the table and I'm going to insert ribbon and from insert ribbon in the chart area I can select the waterfall then I'm going to select the arrow the small arrow and I'm going to select from the stock area here I want to select the last one and you can see here if you hover over it here you can see the the order that required by this chart is volume open high low and close and this is exactly what we did inside the power query so I'm going to select this chart by mouse and you can see automatically our chart is created but you can see we have a plenty of data we have a lot of data so the chart is almost not readable so I need something to help me to slice and dice or to divide the data into at least month by month so let me select the table here and from table design let me go to slicer and I need a slicer on the month name field then click OK it will create this small slicer I can select from slicer August you will see that the table filtered on August and also the chart is now much easier to read because it's focusing on the on August I can change my selection we have here December and everything is going very well I can clear the filter from here let me also change the title let me call it Google now I want to add another share so because this report is only for Google let's say that we want also to look at the shares for Facebook company but this time I don't want to repeat all the steps more and more so in this case I can use a very good feature in power query I can just select my query from the queries and connection pane and right click and let me select duplicate once I click on duplicate it will open again the power query editor and it will create a new copy of my query you can see here and instead of having only one query we have two queries with the same name let me rename this to Facebook share price and you can notice here all the steps that I did in the first query all of them copied to the second query so I'm going to the first step which is the source I'm going to select the source and you can see here a small wheel on the right-hand side once I click on this wheel it will open the small window that you can use to change the source of this query as you can see here here is the URL that we used at the beginning so I want to change this URL and just place it with the Facebook URL and in this case all the steps will be applied let's try this together control V to paste the new URL and then I'm going to click on OK the query will connect to the Facebook web page or the Facebook URL inside the finance.yahu web page and if I select the last step you can see all the steps applied to my new query now I'm ready to close and load I can just select close and load to the same window open up again import data existing worksheet let me just put it beside the original table and click on OK the new query will be loaded to a table inside the same worksheet now you have the updated numbers for Facebook and now if you want to update this data it's very easy you can see in both tables we have data up to 27 of December when the new data came let's say for 28 or 29 you can just use this refresh button for the first query and the same for the second query or you can just select the table and right click and click on refresh you can do the same for both tables or you can go to data and select refresh all in this case the new data will be updated automatically into these two tables you can use the same steps to add a new chart and a slicer for the Facebook table that was all for today welcome to a new video from the Excel Analysis Series PQB or Power Query Basics this video PQB07 split columns to rows we are going to see how we can transform chaotic data this is the first video to talk about data transformation in the previous 7 videos we were discussing how we can extract data we saw how we can extract data from the same workbook how we can extract data from another workbook from CSV or text files how we can extract data from folders and also from access database and finally from the internet in this video we are going to solve an issue the issue is that we have all invoice data came in one column meaning that the invoice number the amount and month name all in one cell not only this more than one month in the same column as well and the number of invoices per customer is variable so some customer they will have one invoice some customer we don't know it's variable and is unknown for us and the requirement is to split column with consistent data types and finally report customer invoicing using a pivot table in this video we are going to see how we can separate labels from the values we are going to use split column by the limiter to rows we are going to use split column by digit to non digit and split column by non digit to digit and finally how we can split column by number of characters and at the end we are going to see how we can add more invoices or more data and the update will be with one click in our practical example today you can see here we have two columns of data the first one containing the customer ID and the other one containing invoice details and if we have a closed look inside the invoice details you can notice that we have more than invoice in the same row not only this invoice information contains the invoice number like what we have in the first row here inv-721 all of this is the invoice number then we have a semicolon and the amount 9212 then dash and the name of the month January then we have a space and we start another invoice till March we have here space and then another invoice and space so we have in this row four invoices not only this the number of the invoices in the same row or in the same cell is not consistent so we have four invoices in the first record the second row we have six invoices we have here like five invoices here we have only three and all that you can see here is a very chaotic and unorganized data the requirement is to put this in a nice format when we have the invoice number in a separate column the amount in a separate column and finally the name of the month in a separate column so we can report on this data so we need to use power query to ease and automate this complicated job so while selecting any cell inside the data range I'm going to use the shortcut control T to convert this into a table format it will open this small dialog box asking you or confirming the data range and asking you if you have headers in this range and yes I have headers I'm going to check this and click on okay Excel will convert this directly into a table format from table design ribbon I'm going to the left side and give to this table let me call it input and enter and I want to use power query power query in the data ribbon and on the left hand side I'll find the get and transform section which is the power query I'm going to use this icon from table or range I'm going to use this icon to push my data into the power query editor once I click on this icon the power query editor will launch inside the power query editor I can see my two columns in the center area and on the right hand side the query settings it starts with the name of the query which inherited from the name of the table I'm going to change this to output enter and then I have the applied steps I have only two applied steps first one is the source when Excel connected to my table input and then Excel try to automatically detect the data type but I don't need this step for the moment so I'm going to use the red X and delete it now I have only one step which is the source looking into the data I have only two columns as we saw in the Excel sheet I have the customer ID which is no problem all the problems that I have inside the second column which is invoice details as you can see here I have multiple sets of data related to each invoice each set contains invoice number then semicolon and then the amount and then dash and then the name of the month at end of the name of the month you have a space and then another set of data related to the next invoice so you can see here I have four invoices in the first row what I want to do I want to have every invoice at least in one row so I want to take the second one and put it below the first one and then have the third one and below them and so on and so forth and of course I need also the customer number say for the first one which is ending by 500 I want this to be repeated four times each time I have invoice from this line I have the same customer number in front of each of these four invoices in order to do this let me start by selecting the header of this column and from the ربن I'm going to transform section and then the split column button I'm going to click it will open the menu of the split column let me select this time by the limiter and because I want to separate each invoice so I'm going to use the limiter between each invoice in this case it will be the space each set of invoice data separated by a space here so I want to use the limiter space I can just select it from the drop down menu here I'm going to select space and then I want to split at each occurrence of the limiter so if I have four spaces actually here's three spaces it will separate this column into three and also if you look at the advanced option here there's a very good option that we are going to use let me click this arrow it will open the advanced option I can select between splitting the column into columns or rows yes we can split the column into rows let me select this one and I'm going to click on okay and let's see what will happen yes this is exactly what we want we have the first customer ID ending by 500 repeated four times one two three four and I have the four invoices separated here as you can see here and the table expanded into 48 rows instead of the 10 rows that we started with at the beginning now we can look at the steps we now have three steps source split column by the limiter the one that we just did and the last one again a change type it's already generated by Excel however I will get rid of this for the moment because I'm going to change all the types at the end of the transformation process so now as you can see here I have each invoice in a separate line but all the information still together I have the invoice number and then semicolon and then the amount and then dash and then the name of the month I want to separate each one of them in a separate column I'm going to select the invoice details column and again I'm going to use a split column again by the limiter this time my limiter will be semicolon so I'm going to select semicolon from the drop down again each occurrence of the limiter and then click on okay you will see here the invoice number separated and again I have another column called invoice details.2 but because Excel tried to detect again the type of this column so it's tried to be clever and converted into a date type so I'm going to again get rid of this step so now I have the amount and dash and then the name of the month in one column again let me try to separate this I'm going to select the invoice details.2 this time I can use digit to none digit what this means if you look at this column here I have four digits and then dash if I use digit to none digit at the end of the nine the fourth digit the separation will happen and then the dash and jan together will be in a separate column let me do it digit to none digits now I have the amount in a separate column and then dash and the name of the month together let's try to take out the dash I'm going to select invoice details.2.2 this time I can use by number of characters I need to separate only one character from the left so I'm going to select one in this dialog box and once as far left as possible and click on okay the dash now in a separate column I can just select it right click and delete or remove now I have all the information in separate columns let me double check the data types this one ABC which is fine this one one two three whole number let me convert it to decimal number invoice ABC which is fine and finally customer ID ABC one two three meaning any so let me change it to text and now the final step is to change the headers so let me call the first one invoice number and enter second one will be the amount double click on the header amount and enter last one will be the name of the month so I'm going to type month and enter now you are ready you can just load I'm going to go to home close and load the power query will be closed and the import data window will pop up let me select table and existing worksheet I'm going to select this sheet output on the top left and I'll click on okay the query and connection pane will open and the 48 rows loaded let me go to output sheet I have my data here clean and nice let's prepare a quick pivot table and I'm going to use the keyboard shortcut Alt and V I need the pivot table to be in the existing worksheet let me put it beside the output table and click on okay I have a pivot table created and it gives an automatic name pivot table one let me change it from pivot table analyze on the left I'm going to name it invoice report and enter let me create a quick report I'm going to take the amount in the values right click number format currency zero decimal places and okay month in the column customer ID in the rows let me also separate the invoices below the customer number so I have the customer number and all the invoices related to this customer very nice report coming out of a very chaotic data as you can see here not only this I have some more data for the rest of the year so I can see here the month of December September so this is the second half of the year I can just select Ctrl C to copy going back to customer data tab the first empty row below the table Ctrl V to paste the table range will be expanded automatically let me go to output sheet let me check the filter I have only six months here right click and refresh check the filter again you have the 12 months go to the pivot table right click and refresh or you can use Alt F5 the pivot table refresh and you have all the data inside the new pivot table fantastic job very quick and easy all the data updated why because all the steps are saved inside the power query when I added new data just couple of refresh and all the information updated into report that's all for today welcome to a new video from Excel Data Analysis Series PQB or Power Query Basics this video PQB08 we are continue talking about data transformation in this video you are going to see how we can append data from three different tables in this video we have an example of three year sales in three separate tables number of columns in each table is not the same one of these tables does not have the sales value what we require is to calculate the sales value in one of the tables and then unify the number of columns in all tables so we can append all data together in one single table in the first table you can see the sales for 2017 we have six columns first one is the product ID then the size and then the date then we have the quantity the price and finally the revenue for 2018 we have only four columns why because we don't have a column to calculate the sales revenue for 2019 we have only five columns because we don't have the column for the size however we have the revenue calculated inside this sheet so what we want to do is to get all this data together using the power query and put all of them inside this sheet called all sales so we are going to start with the first one it's already in a table form and you can check from the table design tab that we have a proper name for this data set called sales 2017 so I'm going to go to data ribbon from the left hand side I'm going to use the famous icon from table or range once I click on this icon it will trigger the power query editor on the right hand side I can see the properties started with the name of the query and then I have two steps in the applied steps first one in the source then excel try to detect the data type on the left hand side the first column I have the product ID the type is 1 2 3 meaning whole number which is fine then I have ABC meaning it is text for the size then date and time for the date I can change this I'm going to select date and click power query will ask you if you want to replace the current step or not for me I'm going to replace and then let me check the quantity 1 2 3 whole number is fine price and revenue 1 2 meaning it is decimal which is okay for me as well I think I can go to close and load close and load 2 it will close the power query and trigger the import data window for this query I'm not going to load anywhere because I'm going to use it later in the append so I'm going to select only create a connection in this case this data will be saved in the back end of the excel workbook however it will not be loaded anywhere so I'm going to select only create connection and click on okay as you can see on the right hand side queries and connection pane will appear and you can see one query created called sales 2017 let's do the same for the second table 2018 I'm going to select any cell inside this table I'm going to use the keyboard shortcut alt a p t it will do the same it will open the power query editor the name inherited from the name of the table which is okay and then change type automatically let me change it and type to date only same as the previous one replace now I think I'm ready I can close close and load 2 it will open the import data dialogue box only create a connection and okay on the right hand side you can see you have now two queries the same for the third table 2019 alt a p t it will trigger as you can see the power query editor on the right hand side the name is okay 2019 let's again change the date and time to date only replace close and load then close and load 2 and again I'm going to select only create a connection and okay I have now three queries ready and all created as connection only now we are ready to start the append the final step to append all these three queries into one query in order to do this I'm going to select again data and from get and transform I'm going to get data and I'm going to select combine queries and from combine queries I'm going to select append once I click on append a dialogue box will pop in and will ask you how you want to append your tables or your queries two major options here first one is two tables or three or more tables for me I need more than two tables so I'm going to click on three or more tables on the left hand side you can see your three queries 2017 2018 and 2019 I want to select them all so I'm going to select the first one 2017 then click on shift and select the last one and now the three of them is selected I'm going to click on add you will find the three transformed to the right hand side click on OK the power query editor will launch again on the left hand side if you open this you will see that we have now four queries the three original query and you created one which is append one here in the query settings so I want to change this to have a proper name let me call it all sales and enter and now you can see here is all the data coming from the other three tables and you can see the data here coming in six columns and if we check one of this field like the size if you open the filter button on the top of this column and for sure you'll find that the list is not complete because there is more data than that we have in this preview so in order to check all the data I need to click on load more if you click on load more you will find that power query will try to load the rest of the data and you can see that there is nulls and why we have nulls because we have only the size data for 2017 I'm going to cancel this and let me check something like the revenue if we click on the revenue also I need to first load more once I did the load more I'll find also some nulls as you can see here in this columns and why because 2018 we don't have a column to calculate the revenue so let me get rid of this step I'm going to use the red X to delete the filter now let's try to load this query although there is some issues in this query however we are going to load it so I'm going to close and load close and load too the dialog box will open again import data this time I'm going to select a table existing worksheet let me select all sales sheet on the top left and then I'm going to click on okay the query will be loaded to a table in all sales let me check as you can see a new table created and the 208 rows now loaded we agreed that there is some issues in this data so the revenue column is not complete and also the size column is serving only 2017 so I want to start to edit my data and do some changes so I'm going to double click on the first 2017 from the queries and connection pane once I do this the power query editor will relaunch again in 2017 I need just to get rid of the size column so I'm going to select it from the header and right click and I'm going to select remove column now I have this column removed I need to do some changes in 2018 I can use this arrow on the right hand side to expand and then select 2018 the problem in 2018 that I don't have a column with the revenue but I have the price and the quantity I can do a calculation based on these two columns usually I use add column and then add custom column and from custom column I can add a formula to multiply these two columns together however in this case I'm going to use a different option I'm going to select the two columns from the header I'm going to select the first one and then click on control and select the header of the second one now I have both of the column selected I'm going to add columns from standard I'm going to select multiply once I click on this a new column will be created called multiplication and I have the results of multiplication of the two columns calculated in this field no further changes required in 2018 let's check 2019 I think 2019 also is okay no changes required as well I think now I am ready I can just close the power query editor from the top once I click on this x it will ask you if you want to keep your changes or you want this card for sure I want to keep my changes I'm going to click on keep all the changes will be saved I have all the three queries appended I want to reflect the changes in the appended query so I'm going to select this icon and click on refresh the query will be reloaded as you can see here the 208000 rows will be reloaded and also the table will be updated to reflect the changes happened in the three other queries now the table is updated as you can see here I don't have the size column however I have the revenue on two columns and this is not what I want exactly I think the issue because I don't have the identical name of the headers meaning that if you want to append you need to make sure of two things at least that the headers of the three tables the three appended tables are identical meaning that if you have the header called revenue and the R is capital it needs to be revenue with R capital why because also power query editor is case sensitive and also we need to make sure that the data type is identical otherwise errors will happen or as what happened with us here column will be created and the data will not be appended correctly so now we want to edit the 2018 again I'm going to select and double click the power query editor will launch again the only change I want to make here is just to rename this column double click on the header type revenue and enter now I'm ready to close and load and you can see here all the changes saved I just need to refresh the append query I can just do it from the table this time right click and click on refresh the table will be updated again as you can see the query is loading again the 208 rows and now you can see the changes will happen to the table on the left hand side okay the multiplication column disappeared and all the revenue coming in the same column and if you check the filter there is no anywhere in this column now our data is ready we managed to append the three tables in one big table and all the data is identical and all comes through together with the same number of the columns and also we managed to calculate in the power query we managed to calculate the revenue in one of this table the next step is to create a pivot table however if you look at the left hand side the first column is only the codes of the product if I want to do a proper reporting I need to bring in the names of these products and also maybe the categories but this data will not be in the same worksheet it will be coming from other table and this dimensional or lookup table need to be merged with this query in order to get the correct names in the report and this will be the subject of next video pqb 0 9 we are going to talk about transform data again this time we are going to see how we can merge queries how we can get data from one table to the other table and how we can replace the normal vero cup in the excel inside the power query welcome to a new video from excel series pqb or power query basics this video pqb 0 9 we are still talking about data transformation today we are going to see how we can use merge queries to merge data from two different tables we are going to continue the same example that we started in the previous video pqb 0 8 so let me remind you with the previous video in the previous video we saw how we can use append to bring the sales data from three different tables into one table in order to have accurate or proper append we need to unify the number of columns the header names and the data type in this video we are going to see how we can use merge queries to bring product data into the sales table meaning that we are going to use something similar to the vero cup function and not only this we are going to use a join kind called left outer meaning if we want to use the same functionality of the vero cup inside power query we need to do merge queries with a join kind left outer there is more than one join kind in the merge queries but in this video we are going to focus on the left outer in video pqb 0 8 we started with the three years sales sales for 2017 2018 and 2019 and we managed to get all of them using append in one table as you can see here and if you look at the right hand side we have three queries three different queries for every year 2017 2018 19 and finally we have the 208 rows appended in one query called all years and also loaded into a table as you can see here in the all years tab if you remember that we had 2017 6 columns however 2018 was in only 4 columns and 2019 was in 5 columns and if you check here we didn't have the revenue calculated inside the original data however we managed to calculate the revenue inside the query and we unified the number of columns and the names of the columns in order to get the all years right and correct and all data in one table and we agreed that we need to create a yearly report for this sales however on the first column on the left hand side you can see we have the product ID but we don't have the product name or the product category which is required if you want to create a meaningful report we are going to use merge queries in order to bring the name of the product and the category inside this table so we can create a proper report but this time the data not inside this excel workbook it's saved in a csv file somewhere else so we need power query to go and connect to this csv file and in order to do this we need to go to the data ribbon on the left hand side we can see the get and transform and we can use this icon if you hover over it you will see text slash csv so we can just click on this or you can go to get data from file and you have the second option from text or csv I'm going to click on this it will open the explorer asking you where exactly your file here is my file I'm just going to select it and click on import it will trigger the power query navigator in the navigator window you can see a preview of the data it's coming in three columns product and category and here is exactly what we want to do we want to use the column product and join it with the product ID inside our sales table so we can bring the name of the product and the category inside this table then we can do a quick and accurate pivot table report so we are going to use transform as usual this will trigger the power query editor on the right you can see the name of the query product lookup inherited from the name of the csv file which is fine for me we have three applied steps the source when we point to the data then promoted header you can see here we have column 1 column 2 column 3 and then the first row was the headers of this table if you click on the second step you can see that the headers promoted and finally excel try to detect the data types it's fine because you can see 1 2 3 for product ID which is number and ABC ABC for product and category meaning is text so for me this is okay I am ready to close and load close and load 2 no need for me to load this anywhere so I'm going to select only create connection and click on okay you will see here on the right hand side we have now five queries and here is the new query that we just created now I want to use these two queries to merge them together to bring the data inside one table I can do this again from the data ribbon from get data I can click on the arrow down here and go down to combine queries I have two options append that we used last time this time we can use merge however if I selected merge from here it will create a new query and join the two tables together in one new query but this is not what I want to do I want to bring the data from the product lookup inside this table so I can do this from inside the power query itself so I'm going to select all years and double click it will open again the power query editor and while selecting the all years query I have here all my sales data for three years I can go to and from home the combine section I can use merge queries and click the merge direct box will open and it is divided into two section the upper section includes the first table which is our all years table or query and you can see here a small preview of the data of this table I have here the five columns and like six or seven rows as a preview for the table in the below I can select the second query or the second table that I want to join with the all years table in my case I'm going to select from the drop down menu I'm going to select the product lookup again I can see a preview or a small preview here in the lower section of the window and also in this window you can select the join kind you can see here the join kind by default it's left outer and we said that left outer is doing exactly what we look up in the normal excel formulas and if you select the drop down menu you can see plenty of other join kinds however today we are going to focus on this next time we are going to talk about the rest of the join kinds if you look at these two tables you can find that there is a common column between them which is the product ID so it's making sense that the merge will happen by joining these two columns together so I'm going to tell the power query that I want to join these two tables using the product ID in order to do this let me select from the header product ID in the first table then I'm going to select the product ID also from the header in the second table if you look down you will find something loading and something saying that estimating matches based on data previews now the estimation is done and you can see that 208 rows match it with 208 rows meaning that every product ID that we have in all years table we found a match for it in the product lookup table which is perfect we are ready and we can click on ok once we click on ok a new column will be created and the header of the column will be the name of the other table or the second table and you can see here below the header you can see table and table and table and table and there is no data just the word table but if you select from the white space here from the white area from the empty area here on the right hand side you can see below this is exactly the data inside this small table so inside each cell we have a table and if you want to preview the data of this table you can just select from the empty space and look down you will see the content of this table and doing that for 2023 after doing the match or the join you can see that a small table created containing one record that 2023 in the other table corresponded to the wheels as a product name and category as a component name but it's not exactly what we want we want to bring the data itself here so we need to create two columns instead of this column containing say for 2023 we need to see here in two columns product category the box should be wheels and the category need to be component in order to do this it's very easy if you look at the header of this column you will see two arrows this means that you can expand this column once you select these two arrows it will open a small dialog box and asking you what exactly you want to expand for us we don't need the product ID because it's already here in the first table so I'm going to uncheck the product ID so I need only the product and category and also I'm going to uncheck use original column name as a prefix because it will prolong the header of the column once I click on ok two columns will be created one for the product and one for the category and now I have all the data complete in order to do my report some small transformation also maybe required I want to just get rid of the prefix ID now because it will not be used in the report I'm going to select from the header and remove column also I think the order need to be little bit different so let me select the category from the header press on control and select product from the header and using the mouse I'm going to drag them to the left so we have the category then product date quantity price and revenue I think we are ready we can close and load so I'm going to use this button close and load or I can just use the red X from the top right and just close the window but I have to select keep in order to keep my changes the power query will close and also the all years query will be reloaded and you will see now the 208 rows is loading and also this table will be changed just after the completion of the loading of the query now the 208 thousand rows loaded and the table also updated to reflect our changes you can see on the left hand side the category product date quantity price and revenue I think now we are ready to create our pivot table report if you want to see the example from the beginning to see also the append you will see now a link on top of this video you can just use it to go directly to video PQB08 so let's start to quickly build pivot table report while selecting any cell inside this table I'm going to use shortcut alt and v it will open the create pivot table window it depends on table all years I'm going to select existing worksheet let me put it in h4 and click on ok a pivot table will be created let's quickly build our report I'm going to select date and drop it in the rows it will group automatically and this is exactly what I want so let me take out the date itself I'm going to take out also quarters and I'm going to put the years in the columns then let's take the revenue and drop it in the value let me quickly do some number formatting right click number format currency and zero decimal places and ok let me bring the category in the rows and then product under category and here you go you have a very nice yearly sales report by category and product name the data was in different sources three of them was in three separate tables as you can see here we managed to use append to bring all together and also the product information was in a different CSV file we just connect on this bring all data together and created a very nice and quick report in next video PQB10 we are going to continue with merge however this time we are going to see the full outer the inner lift