Excel Power Query Fundamentals: A Step-by-Step Guide

1. Introduction of Power Query Course: Welcome to Ultimate Power Query course, your Gateway to Mastering Data Transformation and automation in Microsoft Excel. Power Query is more than just a tool. It is a secret weapon to clean, shape and analyze data like a P. Whether you are dealing with messy datasets, merging tables or building custom transformations, Power Query make it effortless. Hi. My name is Mahaviir. I have more than 20 years of experience across various industries. I've been working Microsoft query since it has been included in the Microsoft Excel and created a lot of MISs for my organization and professional work. I am very much passionate about teaching and helping for your success. In this course, you will learn automate repetitive task, combine and transform data, seamlessly, unlock powerful insights with ease. Is this course right for you? It is perfect for beginners and Excel enthusiasts. Our step by step lessons will make you a power query expert into no time. So are you ready to level up your excel skills? Let's get started. 2. What We can Do with Power Query: Hello, everyone. Welcome to Power Query course. In this video, we will learn what we can do with Power Query. So here we have example of sales data in which we have three different type of data. In Fo sheet, I have customer sales data in which we have customer ID, product ID, and the quantity. And we have separate sheet in which we have product ID, subcategory item, Sndterpris and its name. We have a separate sheet in which we have customer ID, name, city and state. Now, my task is to consolidate all three sheets and create state wise sales data in which the sales must be greater than 1,000 only. So by using Power Query, we can do this task in few clicks. And whenever there is a change in the data, that report will be updated automatically. It means it's completely dynamic. Let us begin with our example. First, we need to fetch the subcategory and the item name in this customer wise sales data. For this, right click on it and click on Get data from table and range, so you will get Power Query additive. On the very left hand side, we have query table. On the right hand side, we have query settings in which we have properties like table name, and whatever the steps we are following in our example or our task will be saved over here. I will explain this later on in details p. This is a query setting only, and this is the area where we can see all the data. Basically, what power query do, it will record the coding in the background. So we are not doing any coding, but we are recording our task similar to the macros. And whenever we refresh our data, the all code works and it all the task which we recorded over here. So right now I have one table called customer sales data, and I will rename it so that we can easily identify. Sales data. And now we need to bring another data that is item master. For this, first of all, we have to close this and click on Keep. You may see a new table, but don't worry, leave it as it is. Now, click on Itemmster, right click over here and click on Get data from table and Rnge. Again, we can see the Power Query additive, and here we data, and we have to rename this as well for easy identification item master data, presenter. Now, Guru Transform. But before that, select the sales data as we want to use sales data as a main table, and we need to retrieve data from item master data. Then we have to select on sales data and then come to the home tab and click on merge query. So right now what we are going to using the merge, we are connecting both the tables. So by default, the sales data will appear over here, and from this option, we have to select item master data. And we need to select Product ID and product ID from the second table. And make sure you have selected left out or from first matching from the second. I will explain all these details later incoming videos, and after selecting, click on OK button, and then you will see item master data. Then click over here and click Okay. Now we get all the data which is coming automatically. It is working like Lou or lookup. Means whatever the product ID over here has been written, the product name, this tenterprise, and their category is coming automatically. So now we need to adjust the columns. So we need to move the category from here, and I want to move the name as well, so you can increase or decrease the width of the column for adjustments. So this is my product ID. This is the master file, and this is the name, and here is a quantity, and we can remove this because I do not want two times the product ID. So I have subcategory so I will rename this to category. I will rename this to item name, and here is a quantity, and this is the standard price, so I will rename this as well. Now my next task is to get the total amount, which can come after multiplying the quantity and the price. So what I need to do I need to select quantity, press control and select the price as well. Now, coming to add column and come to the standard and select multiplied this time. We go. We got the multiplication. It means we got our sales amount. So you can see all actions which we are doing has been recorded over here, which we can delete or maintain as per our requirement. So we will also discuss about it later on in this video or the course. Now we need to rename this to sales amount. Center. And after doing all this, we have to save press control and come to the home, close. And what we get is the sales data with let me reduce the Zoom size, customer ID, product category, name, quantity, price, and amount. The next task is to bring our customer sales well. Now, select the customer master, right click and then get data from the table. And this time, we got our master data in the Power Query. Now rename it to customer table data. Now again, select the sales data. Come to the home tab, click on Merge Query. This time, we need to fetch the customer data. So we have to select customer table data and select the customer ID and the customer ID from the table below and make sure you have selected join kind as left oututer. After that, click on Okay button and move it a little bit. Click over here, click on Okay, and this time also, we got customer ID, so there is no need to rename to customer name, city and state. Select all three, and you can move it to the left hand side just after the customer. So now we have customer ID and the customer name, city and the state would be fetched directly from the customer master, and that would be linked directly. So whenever there is any change in the customer master table, the data will be changed automatically. First of all, now we can delete the customer ID and click on close and sales and come to your table sales data. So what we have, I'm going to close this one. We have customer ID, name, city, state, product ID, category, item name, quantity, price, and the sales amount as well. So initially what we have in the sales data, we have only customer ID, product ID, and the quantity. So we retrieve customer product data and the price, and we calculate the sales. Now my next task is to calculate the sales amount statewise and category wise. Also, the sales must be greater than 1,000. So now I'm going to click over here and click on G data from the table, and you can rename it and you can rename it to sales data, consolidated. And after that, come to the transform and click on group Y. And from here, we need to select state as first, we want to calculate the total of sales state wise and give the column name state wise, total sales. And here, the operation which we are going to select is sum and select the column sales amount, and click Okay, and click on this button, select number filter, select greater than, and here enter amount 1,000. Click Okay, and come to the click on Close unload, we got our report. We can close this one. So what we have, we have sales data state wise, and all sales data is greater than 1,000. We can do the shorting, descending or ascending as per our choice. And now on the right hand side, we have query section in which we can just duplicate it and we can give some other name like sales data, rename, we can rename it. Item group. And this time, I'm going to select group row and click on this gear button. And from here, we need to change state to item category. Rest of the things would be same, but we have to change it category, total sales, some operation sum, column sales amount, and then click on O and make sure we have to delete that filter option. And then if you want to do the filteration over here, you can select the number filter. I'm not going to select and I'm going to click on close load. So we got our report, and this report is completely dynamic. As I already explained, whenever there is any change in the data, we have to just click on refresh button from the Data tab and click on refresh A, that would be changed automatically. So this is a very basic use of Power Query. In this course, we are going to explore each and every topic which you can use in your daily routine work, and you can automate your all task. So you can save your time and money as well. So let's begin our course and let's get started Power 3. Excel vs Power Query: Hello, and welcome to Power Query course. Before we dive into further videos, we need to understand there is a difference working in Axl and power query. First one, in Excel, we work directly into the cell level. For example, if we want to calculate any multiplication or we want to insert or delete any row, we can directly work in the axels row and column level, but in Power Query, we cannot. Even if we have any blank cell and we do any calculation. So we are getting desired results. It means it will considered automatically a number using their inbuilt functionality. For example, if you want to calculate the sales amount, so I just type on header and then by using equal sine, we can highlight the cell then multiply by multiply with another number. And when we double click or we can copy paste all these function. We will get the results. Let's say in two cells, I have blank data. We do not have any data in these two cells, but I'm getting perfect results, zero. But if the same thing we do in Power Query, we will get some different results. Let's begin. I have deleted my multiplication column. Now I'm going to use Power Query, and this is the Power Query add here you can see null is coming instead of blank. So whenever there is a blank cell in the source drata, we will get null in the cells. So now, how we can get rid of that that I'll show you. But before that, I want to do the calculation multiplication. For this, we need to highlight both the columns by using control key, keep hold Control key and select both the columns and now go to add columns and select standard operation and select multiplication. So we will get our data. We can rename it to sales. But here we are not getting the results which we want. We are getting null values because Null is coming over here. So how we can get rid of that? So select the data. Or the columns in which you want to change the value. For example, here, I want to replace null with zero, so I'm going to highlight and then coming to the transform and I'm selecting replace values. So here we have option to find the values which I want to change. So in this case, it is null, and I want to replace it with zero, and I'm going to click on Okay but I'm not getting sales data zero because as in the first video, I told, whatever the action we do will be saved on the right hand side, this applied steps. So these are the steps. And first, we did the multiplication, and then we did the replacement. So what we need to do we need to move this step to the upper side of the inserted multiplication. And then we got our results. So this is the way you can use the things in the Power Query. Now, I need to put these values to my actual seat just below my source data. So I'm going to select this time close and load to option. So this time, I have choice whether I want to put my data in new worksheet or existing. So I'm going to select existing. I'm going to put my data over here for comparison purpose only and then click on. Okay, so I'm going to zoom out a little bit. So here, I'm getting my perfect result. And the second thing, whenever I need to insert any data, we can insert directly in the cell in the excel, but not over here. Let's say I want to change this data to ten or maybe five or I want to replace this to 20. So you can see it is not updating automatically that we need to update manually coming to the data tab in the Excel and click on Replace All button. So data has been similarly, if I insert or delete any row from here from the sourced era, then we have to click on refresh button to get our desired result. So these are the things you need to take care before warping in Power Query. The next thing, if you change any header, then you will get error in the Power query. For example, here, the quantity is coming, which is in the short form. If I type in the full form quantity and press control has to save this one. And now if you click on refresh all, you will get the error. Why? Because that header has been linked to our Power it is considered the source column data, and we have to change the same in our power query as well. If I click on replace A, we are getting data. Selecting this one, come to the Power Query, click on Addit we are getting error, click on go to error, it will move to the error on the first step where we are getting error, and then come to the view tab and click on Advance Editor. Here is the code which was generated when we were recording our steps. Here we need to change all these values where there is a change. So let's see on the top of side, if you go to the right hand side, we have some data. So on the first row, we have quantity. So we can type and copy, and now we need to replace it and make sure once you replaced all the quantity or the changed data, you have to click on Don button, and we are getting our data back in the same shape which was we got earlier. Now, click on Clouse and Load button, and data is perfectly fine. So basically, you need to consider three things before working Power Query. That you cannot add or do the changes at cell level in the Power Query. You cannot insert any row in the power query. And the third one is, if there is any change in the header at the source stata, we need to change same in the Power Query editor. Thanks for watching. We'll see you in the next video. 4. Data Types in Power Query: Hello, and welcome once again. In this video, we will explore about data types used in Excel and Power Query. Basically, if you have ever worked in Excel, you have entered different type of data in the Excel. Let's say here I have some data. In the customer ID, I have some numbers, which is in the number format, actually. So whenever you select any cell, you can see the data types in the home tab and journals section. If you click on this button, so you will find different type of data types used in the Excel. So basically, it is journal that can be a number or text. We can use currency, accounting, short date, then long date, then time, percentage, fraction, et cetera. So why we need different type of data types? Because we need to do some calculations in the excel. So Excel used their inbuilt functionality to do the calculation based on the data types. So let's say I want to do some multiplication with the price and with the date. If I presenter, so I get some data which is in the date format. But actually, every date in the Excel treated as a number, you see a date in the date format, but basically, it is a number in the background. So if you change this datatype to general, so you can see that number. So that is why Excel is not showing any error, and it is displaying a number instead of error. So I'm going to delete that one. So we can do the addition substraction using the same datatypes. And if you do any multiplication using the text, so you will get an error in the value. So this is error type because a text cannot be multiplied with a number. So the same thing will be applicable in the power query as well. So if you see in the quantity, I have two, five, two, and one is written in the text. Excel is determining this as a text. In the power query also it will determine the text. So opening I'm opening this data in the power query by right click, go to text data and click on Okay. So it will open that Power Query editor. Now I'm on the Power Query editor. And now, if you see here we have numbers written over here. So that is called number type, and here some texts are coming here, also some text, here, some text. Here, also some text, it means this data retrieved from the data source is considered as a text. But if I go to the right hand side, here it is treated as 1.2, it means decimal places. So here you can see it will be considered as date format, and here text, and this is also date with the time. So now if we do any calculation using the different data types, we will get error. So we can do the changes in Power Query. So let's say customer ID is never used to do any calculation. It must be in text format. Click on this number, select text and click on replace current data type with the text one. So it will be converted into the text. Now I can see the ABC over here, and this would be treated as text. This is also text, text, and this is quantity. And this quantity is treated as text, but I need to convert this into the numbers. Then only I can do the multiplication. So let us try something. So if I select this quantity and hold Control key to highlight the price and then come to the ad column and if you see the standard option is not visible because of this different data types. So we can add a custom column, click on this custom column, and we can do any heading if you want, and then you can select, let's say I want to multiply the quantity, double click on it, Asterix sign, and the price and click Okay. So on the very left hand side, we can see the error. We can rename it from here as well. Let's say sales amount, press Enter. So I'm getting error over here. So now I need to convert this data into the numbers for getting the proper result. So just click on this ABC and select decimal places all the whole numbers. So the quantity will be in the whole numbers only, it will be converted. But still, I'm getting error because this change is used after the multiplication. So I need to move to the upper side. So I'm getting some results. Now we can see some results are coming over here if we select this step. But here I'm getting some errors, so we can also get rid of that. So for doing that, you have to just right click on this quantity or the header of this column. And select replace errors. Click on Insert and Inter zero because I want to replace all the errors by the zero only and click Okay. A has been converted or replaced with zero, and also I need to move this to the top of this step, and I'm selecting at customs. So now I'm getting perfect answer as per M. Requirement. Now moving to the order date, and this time I cannot see any timestamp. I need only dates. So just click on this calendar sign and select date only and click on Insert, that would be replaced automatically. And now moving to the date with time. Here also we have timestamps, but this time, I want to see the time as well, so that would be okay. But if you want to do the split this column. So you can do it very easily. Now, select this one, right click, select this one, come to the transform and come to the split column. And I have some numbers like delimiters number, position, lowercase, uppercase. Just click on it, select by delimiter, click on Insert, and this time, I need to select space. So after the space, I want to split this column because I have date, then the space, and after that, I have time. After that, I need to click on Okay. So I have data in separate columns, date and the time. So you can change the header if you want. I'm getting perfect results which I want actually. After doing that, come to the home tab, click on Close and Load, and it will be loaded in this new sheet with the desired result. So I have customer name, product name, quantity, and order date, and I'm also getting sales amount. And this order date it has been replaced with separate columns, date and time as well. So this is how you can use the data types in the Power Query. We will explore some more options incoming video. 5. Import Data in Power Query: Hello, and welcome once again. So in this video, we will import the data in the power query from different sources. So we have many ways to import. For this, first of all, we need to select a particular cell in the table in the Excel. You can highlight all the data or you can select any cell but make sure you do not have a blank row in between. So let's say I have a blank row over here, if I select any cell and then if I click and go to the G data from tableslas range, so you can see only a part of range above the blank row has been selected, which I don't want. I want to select a complete data. So for this, it is a good practice. You need to select or highlight manually all the data or you can convert this official data table in the Axl. I'm going to highlight this one, and I'm going to right click and I will select get data from the range, and my whole data has been selected, I'm going to click Okay, and this is the power query. We have done this task many times. But now I want to arrange professionally this data. So I have this sales data from my source, and it is North reason, let's say, example, and I'm going to presenter. And when I click on Close and Load, it will be moved to the new worksheet in the same worksheet, and we have to save this one. Now, coming back to my data worksheet, I'm going to close this one. And this time, I want to select from my second reason, and this time, I'm going to convert this into the tables. I'm going to select this one and going to insert, then table and okay and then I'm going to change the format as per my choice. And after selecting the cell, I am going to convert the name from table three to South reason. Presenter, save it. And this time, right click and then go to the Get data from the table and ranges. And this time automatically you will get the query name as South reason here and here as well. And now I'm going to close this one. The data would come to the new W sheet, right click over here, Guru it, and you can do any changes if you want, then close it. And there are many other ways which I'm going to show over here, Guru data data. And from here, we can import or connect the data from Excel, text, XML, J Shon, other database like MSXL, online services, and we can have the options from merge and append query from here as well. So we can import from other sources as well, even we can import the data from the PDFs. So this is the way we can import. We will use few options from them incoming videos. So just do practice using this while how to import the data in the Power query. 6. Connection Query: Hello, everyone. In this video, we will learn about the connection query. Before few videos, we discussed how we import the data and do the connection from other Excel file into a particular file in which we want to do some manipulation with the data in which we will load that data into the Excel file and it will be displayed. Right now, I do not want to load that query in my Excel file. Without loading, I want to do some manipulation with the data. Let's take one example. I have sales data, reason is east, west and south. Now my task is to create a file and I want to do the consolidation of reason wise sales in a separate file. So I'm going to close this one and creating a new file, and I'm going to save that file. Give the name and save it. And now come to the Data tab, go to get data from SL from Excel workbook, select the Sales reason data file. Click on Import. On the right hand side, you will see the sheet names. When you select any sheet name, the data in that file will be displayed on the right hand side. So we can see the preview of data in each worksheet. I'm going to select East file and click on Transform right now. So data will be imputed or connected with Power Query editor. Now, click on Close and Load button arrow, select Close and Load two, select only create connection, and click Okay. So this time, we have connection only query. If we over the mouse, we can review the data. Now, save it. Again, I'm going to do the same task, but this time, I'm going to select a different worksheet. This time South transform data. Load, close and load two, only connection. Okay. Again, from file, from Excel workbook, sales data reason, Import, West, transform data, close and load two, only create connection. Okay, and save. Now we have query, but we cannot see the data in the worksheet. Now we need to append or do the consolidated report using these three queries. Go to get data, go down site, combine queries and select append queries, select three or more tables. Select the first one, click Add, select the second one. Add, select the third one, click Add, and then Okay. Now the reason name means the worksheet name is coming automatically. Order ID, order date, customer, customer name, quantity, all are appended automatically, but make sure the heading of the all worksheet would be same. Then only you will get the append or the consolidated file, but here there is a one problem. As you know that if there is a blank sale in the resource data, we will get null values. Now I want to replace this with the East. I cannot use the replace option, as I do not have a single value. So now this time, we can use the fill option. Select that column, come to the transform, come to the fill, and select down and boom, you got your answer. So what we got east in all blank data automatically filled by the power query. This is the message of Power Query. Now come to the home tab, come to the close and load, and this is the data we got it, and it is completely dynamic. Whenever there is any change in the resource data, you have to just refresh and you will get your data. This is the way you can use the connection only query. 7. Conditional Column: Hello, everyone. Now we are moving to our next subject, which is conditional column. Let's take one example. We have one sales data product wise, sales amount and the North region. Now, my task is to get the performance, whether it's high, medium or low, which is based on the sales amount. If the sales amount is more than 1,000, it's excellent 500-9 is medium and below 500 is low. In Excel, we use IL statement, but in power query, we will use conditional column to achieve this kind of situation. First we will learn this option by a simple example, then we will move to the Advanced one. So first of all, right click in the cell, select G data from table and ranges. Make sure you unable my table as header and then click Okay. It will open Power Query editor, and then go to Add Column. Select conditional column. Add conditional column. Option will pop up. First, give the column name. Performance, and now we will give the I statement conditions. If the column name is sales amount is greater than 1,000, the output would be excellent or high. We can add multiple conditions. Add clause sales amount is greater than or equal to 500. The output would be medium, and if it is less than 500, performance would be low. After that, click on. Okay, we got our result, go to home, close and load to existing worksheet, select the cell, click Okay. Close this option, and here we have the desired results. If you have large data, this option is quite good. In the next video, we will discuss about the advance option. So stay tuned to the next video. By for now. 8. Conditional Column Advance: Hello, everyone. Welcome once again. In the previous video, we'll discuss about the conditional column where we have a simple column with a simple condition, so we can add a column. But when we have multiple conditions, we have to use custom column. Let's say, now, I want to check if our sales employee met his sales target and his experience is more than five years and the amount is greater than 1,000. He is eligible for high bonus, otherwise, low bolus. I want to put a custom column over here. For this, we will use query, which is a part of Power Query. Right click Go text and table ranges. Okay. And this time, instead of conditional column, we will use custom column. We will give some name such as bonus. Click here, then if target mate equals to yes, all text must be in the quotation mark input N, so we are giving the next condition that experience must be greater than or equals to three years, and this is the number. That is why I'm giving a direct three character without quotation mark. Then again, sales amount is greater than or equals to 1,500 then quotation mark high bonus, Enter, low bonus, close it. Here we can see no syntax error have been detected. It means there is no problem in this code right now. Click Okay. I think there is a problem with our condition. We have to check it once again. Click on gearbox. Target mat equals two. Yes, experience must be more than three and sales amount must be Okay, I'm going to reduce this one to 1,200, then high bonus, low bonus, click Okay. Yeah. This time we got the perfect answer. So here we can see the target is yes. Experience is also more than three years and the amount is also more than 1,000. This condition has not met over here as the experience is less than three years. But here it is true, that is why we are getting high bonus. So this is the way you can enter the conditions based on the multiple criteria. So in the next video, we will explore some more options of power query. Stay tuned to the next video. 9. Column by Example: Hello, everyone, and welcome back. In this video, we will discuss about column by example. The column by example feature in Power Query allow you to create a new column based on transformation or patron you specify without writing any formula. It is incredibly useful for quickly extracting formatting or manipulating the data. Let us take one example. I have name, email, and the joining date. Now I want to extract the first name from it and the domain from the email IDs, and I have to extract the month with the year in the new column. Let us begin with the example. Right click on cell, click on Get data from the table. Unable my table as header, and then Okay. It will open the Power Query editor. First of all, if you get some different kind of formatting in any column you have to convert. We have already discussed about it, so we can change it from here. We can replace it. Now, we have to extract the first name by using the column from examples. Click on Add Column, click on column from example. And we will get one custom column on the right hand side, click on the first cell and start typing the first name and press Enter. That's all you have to do. If you see on the top in the formula bar, the power query has written the function using the artificial intelligence. If you think you got the result as per your requirement. Click Okay. Rename. First name. Now we have to extract the domain from the email given for the person. Again, come to the ad column, click on Column from examples, and start typing the domain name only. Presenter. That's all you have to do. Click Okay. And you can rename it domain. And the next is to extract the month with the year. Go to Add Column, click on Column from examples, and start typing May then 2022, presenter. If you did not get the desired result, don't worry. Start typing again. Next is July 2018 presenter. This time you got the result and click Okay. So this is the simple example. In the next video, we will take some advanced example for better understanding of column by example. Stay tuned to the next video. But before going before leaving this video, we can load this data to our sheet. And it is completely dynamic. Whenever you add any data here, it will be Updated in this table automatically. Once you click on the Refresh button. 10. Column by Example Advance: Hello, everyone. Welcome, once again. In this video, we will discuss about the advanced uses of the column example. Here I have employee table in which I have full name, department, employee ID, and the joining date. Now my task is to create a employee code based on their department. I need to take first three characters from the department. For example, in case of John, the department is Ss and first three character is SAL. I need to take first three letters, then dash and then we need to take last three characters from employee ID. So in this case, it would be 001, and then I need to extract and join the year of join in this code, so 2022. So this is the combination of the characters from different fields. I need to extract using the power query. So for this, I need to right click, go to get data from table and ranges. Click Okay. And then first change the format of the date, replace the current now come to the ad column, click on column from examples, and then start typing SAL 001 2020, press Enter. Now system has not recognized the formation. Again, you have to type rd00 2-2018, press Enter. So now Power Query has determined the formation of the custom using the example we have entered in the first two cells. Now I'm pressing Okay, and I got my result a AL 001 that we have entered manually, but it has taken IT. As you know that we have two arrectors in the IT and the HR, so it is taking two characters only, but from the finance, it is taking three characters. So remaining in the middle part, it is taking last three digits from the employee ID and the joining here in this. Now you can rename and then click on Close and Load, it will be imported in this sheet. So this is the advance option, so you can explore more examples and do your practice. 11. Merge Column: Hello, everyone. In this video, we will discuss about the merge column option in Power Query that allow you to combine the content of two or more columns into one with optional delimeters, for example, space, column, comma, hyphen between the merged values. It is particularly useful for creating composite keys, full names, addresses or any concatenated. Here I'm taking one example. I have address in which we have house number street, city, state and the zip code. My task is to create a combined field with the comma using all these fields using the Power query. It is very simple, right click Go get data from the table and data. Click Okay and select all the field by highlighting it. So I have highlighted all the columns. Now, there are two options whether you want these columns and want to add a merged column, or you want to remove these columns and want only merged column. That's totally on you. I'll explain both the options. So first, we want to use merge column only, so I'm going to transform and then click on merge columns, and merge column dialogue box will appear. Select the separator you want. So in this case, I want to use Coma, and I want to give the name of merged column over here. You can give any heading if you and click Okay. I will remove the source columns, and you will get the merged column only. Now I'm going to delete that one. Again, I'm going to highlight by press and hold the Control key from the keyboard and then go to add column and then go to merge column. And this time, I'm going to select coma or you can go with the custom one, press Coma and give some space. You can give some name and click Okay. So this time, I will get another column, and now go to home, close and load, save it. Now I'm going to explain one advance option. So here I have list of employees with their first name, last name, department, employee ID, and the joining. I want to join all the fields and the data in that format. I have to combine first name, last name. Department must come in the brackets, then cool employee ID and the date joining. For this, right click on the data. Go get data from table and ranges. Okay. And first of all, we have to change the format to the date, replace, and then add column by example. Now type John. We want last name as well, Smith, then Coma sales, close the bracket, column. Dash presenter. Okay, we got the results. Now you have to move it like this and now select this one and select select by highlighting it, and now go to add column and then click on March column, and you can give any separator if you want or select Custom, give Coma space, and I was, I think, hyphen instead of coma and ok. And this is the way you can merge the data. Now go to home, close and load, and it will be loaded here. So this is the way you can use the merge option of Power Query. Thanks for watching Follow for mode and stay tuned to the next video. 12. Filter and Sorting: Hello, and welcome once again. In this video, we will discuss about sorting and filtering option in Power Query. If you have ever worked in Excel, we have option for shorting the data of columns, simply select ACL and go to data. And then from here, we can select shorting option A to Z, lowest to highest or Z to A. Highest to lowest. It's very simple. We will get the desired result. There are many types of a types in Excel, date, number, text. We have some option for filtering the data if we need a particular range of data. For this, we go to data, then go to filter option, and from here, we can select. Let's say, in this case, first we have years if the data types is date. We can select particular here, if we click on plus sign, then we have months if we again expand this one, so we have all the dates. And this is how we normally select or filter or short the data in Excel. But we are learning about power queries. So I'm going to unfilter this one, and we will move to the Power Query. Right click Guru, get data from table and ranges and Guru and click on Okay, and we will get all the data is available in this Power query. Now I'm going to expand to the right hand side all the data. So we have profit discount. Many columns are there. Now we need to extract some particular data. For this, we can use these options. By default, this filter option has been unibed, so you have to click on this little arrow button, so we will get the kind of data. The options are available over here will depend on the data types in the column. In this case, it is date and time. That is why we can see date and time filteration. If you convert this to date only and replace the values, if again, you click, only date filter will be available. So we have equal before after between even years, quarter, month, week, hours, so many options are available, so you can just explore. You can deselect all the data by disabling this you can just type any number from here. We can remove empty if any cell in this column, so we can select that option. So I'm going to select Okay and going to date filter, and this time, I'm going to select here, so this here only. So nothing is selected, clear filter. So this is how you can work with the date. So you can go to Date filter and go to customFilter, and here, click on Advance. From here, you can enter any date. A range or so many options will be available. You can add more filter options if you want add clauses, order date, operator equals greater than everything will be worked. I'm going to cancel this one. Now we are going to discuss shorting. Shorting means, let's say we want to shot by row only, select that one. And here we need to just come to home and click on short options A to Z or Z to A. Click over here. That would be recorded automatically. But what happens when you want shorting by raw data, then by date, then by the ship mode. So nothing to do. First, select the column you want to short, then do the shorting. And this time, I'm going to shot by date, so I'm selecting that one. And I'm going to click on A two Z. It won't change because it is A, two, one, two, three, I mean, the row shorting has been unable, so I'm going to just removing that option. I'm going to select ship mode first. So we have first class, and now I'm going to short by date. So there's some changes. But the shorting ship mode will be enabled only. Now, click on customer ID. Let's see whether we are not getting any or shorting, because it is first shot by ship method, then the order date, and then by the order ID. If we want to do the shorting, once again, so we have to remove and we have to start the complete procedure once again. So once you're done with your shorting and the filtering option, you have to let's say I want the data for first January 2014 to 31, December 2014. So I'm going to select that one, go to C filter, then custom filter and then is after or equals to, then select the first January and is before or equals to, 31st, December 24, click Okay. And now click on Close and Load. So data would be loaded for 2014 only. So this is the way you can use shorting and filtering option in Power Query. 13. Add Days Function: Hello, everyone. Welcome once again. In this video, we will discuss how we use the inbuilt functions in Power Query. Here, I'm explaining this using one practical example. Let's say we have different task and the start date, and we have a number of days to complete. My task is to calculate the completion date we can easily do this in by using some function or addition. Simply, I can start by equal sine, selecting the date, plus sine and selecting the days and presenter. If I drag to the downside, we will get our completion date. But in Power Query, we cannot do that because as I already explained, we have different data types and we can do the mathematics with the same type of data in power query. But for doing these type of task, we have inbuilt functions. I'm going to use ad dot days to calculate the future or the past date. The AD dot days function in Power Query allows to add or subtract a specified number of days to a given date. This is useful for calculating future or past dates, managing project deadlines, setting reminders, or creating scheds. Let us begin. Right click. Go to tag get data from table. Okay, and make sure you change the data types. It is numbers, and it must be dates only, and this must be text, but I'm not going to use this column. So now we have to add a custom column, go to Ed Column, click on custom column and rename this to completion date. And then come over here and type the inbuilt function date dot, add days starting. We need star date, coma. We need number of days, which is over here, close the bracket and click Okay, and we get our answer. Make sure you use complete name of the function, which you can find in the help file. If you click over here, you will get the list of all inbuilt functions used in the power query and you can learn about them. So once you are done with it, click Okay, go to home, close and load, and you got your answer over here, you have to change the format of the date like this. So this is a very simple task, but we can do more complex task by using the inbuilt functions of Power Query. 14. Pivot and Append Query: Hello, everyone. Welcome once again. In this section, we are going to start Pivot functionality in Power Query. The Pivot functionality in Power Query is used to transform data from long format to wide format, means from the rows to columns. It is especially useful when you want to summarize or aggregate data by categories. Like creating a report where each category product or maybe reason has its own columns for varicius matrix. So for practical example, I'm taking this example. We have sales report January. Here we have month, here years, then the reason and the sales. We can As create the Pivot report reason wise in the Excel. Why would we need power query? You will understand at the end of this video. Here I have sales data of two years. My task is to create a combined joint report for both the years. It must be dynamic means if I add new reasons data, that must consolidate automatically in that report. How we achieve that? Using the power query only, let's begin. First select your table and then go to data, then this time, I'm going to click from table and ranges from here. And now we need to create a Pivot using the reason only. It is in the column, I want this on the header part. And sales would be the data, so I will get the summary of the sales. So first, I need to move this here to here and then select the reasons or the column you want to pivot. Then go to transform and then select Pivot column. And from here, we have to select value column as sales. Then click Okay, and then save it. And then we can close this one, but we need to click on keep changes. If you want, then you can delete that one. Now, come over here on 2023, select any cell, go to data from table and ranges. We'll move the e, selecting the reason, then go to transform, pivot column, select the sales, then okay. And then you have to rename this to 2023. And we have another table related to the 2024. Now, save it and select any table you want to append or consolidate. After that, come to at column, come to home and select append query and click on this small icon button over here and select append query as new and select two tables right now because we have two tables only. If we have more than two tables, we have to select that option. The first table would be 23, and the second table would be 24 and then click Okay. So here we got our result. We have appended query, consolidated query we can say, in which we have here, then we have M then we have Ada reason in the column, which is in the Pivot, and we have total data. So by that way, you can use the Pivot option, append query in the Power Query. Then you have to click on Close and Load button, and this would be your final report. This is the benefit of using Power Query pivot options. So in the coming videos, we will also explore some more good options of Power Query related to the Pivot. 15. Pivot And Dont Aggregate: Hello, and welcome once again. In this video, we will discuss about the Pivot, but we don't aggregate any data. In Power Query, we can pivot the data without performing any aggregation, which is useful when you simply want to recognize your data. Converting unique values from one column into headers without applying any mathematical operations. When we don't want to aggregate data, we can use the don't aggregate option in the pivot process. This option ensures that the original data values remain intact in the pivoted table. Let's take one example. We have sales data with the salespersons, sales amount here. So we want to pivot the data so that the salespersons become the column header for each reason without aggregating the data. That will keep the sales value intact. How we can achieve that in Power Query in few clicks. Don't worry. Me start. Select any sale. Go to Da, then click on from T. Click on O and select the salespersons. Come to the transform pivot column. But this time before that, we have to change the sales, but this time, we have to click on this Advanced Options. And for aggregating, we have to select don't aggregate this time. After that, click Okay, and we got our data. This time, the sales data is not getting any mathematical calculations. We can see exact sales numbers in the report. Now come to the home, close, and load, increase the size. So before it was like this, now it is like this. 16. Unpivot: Hello, everyone. In this video, we will discuss about the unpivoting in Power Query. Un pivoting in Power Query is used to transform data from a wide format, which is multiple columns to a long format, single column. This is very useful when you need to restructure your data for analysis, making it easier to work with pivotables chart, and other reporting needs. Let us take one example. Here I have some sales data in which I have segment reason, ship mode, January, February, March. This data is actually in the pivot. Now I want to know what would be the total sales month wise of each reason for particular shipment mode. From here, I cannot understand. This data is not useful for me. What I'm getting is only total of January, February, and March. I can create some Pivot tables by using that one, but I need the data in months on Rost. How we can achieve that we can achieve this using Power Query. Select any cell, come to data from table and ranges, click OK, and we have to select the columns which we want to unpivot. Press and hold Control key, highlight segment, reason, and the ship mode. Then come to transform and click on this little arrow button, right hand side of unpivot columns, select unpivot other columns. Now we have data in unpivot format. Now go to home, close and load, save it, select any cell. Click on Insert, click on Pivot table, and then okay. Now we have to move attributes which are months. Now put your values over here, and now we can move our ship mode and we can put reasons over here, and we can put the segment over here. Now this data is related to the all segments. So now let's say I want month wise detail for home segment, I will select this one. The data would be changed. Now I can see in January manth I can see the total of data related to the ship mode and reason wise. So what we got, we had this data, which I converted using the unpivot option into this format. This is the magic of Power Query. 17. Grouping: Hello, everyone. In this video, we will discuss about group by functionality in Power query. It allows you to summarize or aggregate data by grouping row based on one or more columns. It is especially useful when you need to calculate totals, averages, counts, or other aggregations within a specific category. Take the example. I have order data. It's a huge data, 10,000 rows of data approximately. Now I need to know what is the total sales of particular reason. I also want to know the total state wise, and I want to know the counting of sales as well. How we can achieve by using Power Query, let us begin. Go to data from table and ranges. Click Okay. It WopalPower query editor. Now we have to select particular column for which we want to do the grouping. First, we will do a single column grouping. In this case, I want to know the sales of reasons. I'm going to select reason column from here and come to Home tab and click on group B. Then first, we will discuss about the basic, then we will move to the advanced one. Here, we have to select which column we want to use for group by sum. So in this case, by default, as we have already highlighted the reason it has been selected. Now, change the column name, let's say, sum of sales and what kind of operation we want. We want to do the sum. Then we have to select the column. In this case, it is sales and click Okay. That's all we need to do. And then change the number from decimal to whole numbers. This is the first thing which we can do. It's a simple one. Now you can duplicate this query, and now I want to delete that grouping and the changing as well. Now I want to do some advance grouping. So in this case, I'm going to select reason first, then group, and this time, I will select advance. So now I would like to add one more grouping, and this time it would be state wise, and then column is sum of sales. We want to do sum of sales, add one more aggregation, and this would be the count of sales, and this time count rows and then click Okay, now we have to change this to the whole number and select a reason short. And close and load. So now I can see reason wise total of state. This is the way we can use Power Query for getting aggregation of sales and count. 18. Appending Query Multiple Table: Hello, everyone. In this video, we will discuss about appending the data. We already discussed before in previous videos in which we took the example, very simple example. But here, I will explain advanced uses of the appending the data, in which we will use multiple data sources. First, we will begin with a simple one. I have two tables of inventory. One is warehouse A, warehouse B. My task is to do the item wise total of all the quantities. This data can be in the same sheet or differentiates. It doesn't matter. So let us begin the practical example, select any cell, go to data, select get data, then. And our first task is to create a common header so that the system, the power query can identify which type of data is stored in this particular so it is item, it is category, so I will type complete name of category. I will rename it. And here it is quantity, and here I will also change warehouse, A, and then I will select close and Low two and select only create connection. In the similar way, I will select any cell, right click, get data from table and Ranges, then Okay, I will rename to warehouse, B, Enter and then product. So instead of product, it must be item, it must be quantity, and category is f. Go to close and load two, select only create connection, and then o. I have to save this one. Now, I will go to data, get data and combine the queries and the appart. And two tables where we have to select the first table. So in this case, I'm going to select warehouse A. The second table would be Warehouse B. Then click Okay. Data would be connected perfectly in the same manner in which we want. Then we can close and load tu to a new table. This is we have already discussed. Now we are combining our three tables. So our warehouse data has been stored in Sheet two. In this table, it can be a different sheet that doesn't matter. So now we will create a connection only as well. So now I will go to that cell, right click, get data from Tubulen ranges, then click OK and then make sure you have to change the header, then quantity, and then go to transform. Add Column from home, go to Append Queries. And this time, we have to select three or more tables. So right now, I'm going to select table A then B and the current one. You can rename it if you want. Then this one we have already selected, it is coming over here. I will delete from here, and I will move this and then click OK, and you can see the data has been appended in the table four and you can rename it inventory. Report, presenter, close and save too. So whenever we add any data, let's say, here I added some data, and if I go inventory report, if I right click Refresh, it will be added like this. So this is the way we can use appending the data from multiple tables in the Power query. 19. Import from Folder: Llo everyone. Welcome, once again. In this video, we will discuss how we can import the data from a single folder. Let's take one example. I have one folder. This one in which I have sales data relative to 2014, 15 and 16. But all the columns and headers must be same in this case. Now my task is to consolidate the three years data into a single file, and this also must be a dynamic means whenever I put any other year of sales file into this folder, that must be consolidate automatically in that particular file. I have to calculate the E wise sales data in Pivot format. On the top of that, I need reason. Let us begin the example. First of all, I need to close all the files for importing the multiple files data, Guru data, Guru G data from file, select the folder and select the folder in which you have saved your all files. Click on Open and click on this little arrow button, select Combine and transform data. Select the sheet one, and we can see the preview and click Okay, and we will get all the data. If you go down, you will see the source file name over here and relate it all data would be automatically combined in this file. So if I go down, so that is 2014 only, so I'm going down. Now we can see the data related to the 2015. So we can find if we go down 2016 and 17 as well, if we have added. Now we want to do the sum year wise. So for this, first of all, we have to take out the from the order date. So I will go to add columns, columns from example. And now we can start typing. Let's say I want to extract the data from here, 2014, presenter. So data has been selected from the order date and click Okay, and now select that one and go to home, go to Group basic here and here, sum of sales. Let the operation is sum, and this would be sales and then o and you have to change this. This is the one way. And if you don't want this one, then you can duplicate it and remove the grouping, and then click on Close and Load. You can close this option, select any cell, come to insert, select Pivot Table and O and then select here from here and select the reason from here and put the sales data like this and you have to select this data. Or left click value field settings, some number format, then select the number and reduce the decimal places. Select if you want the separator. Click Okay. Okay, and adjust the size and then go to design. Sorry, analyzer options out of it option, remove that one. So that the format of the cell, sorry, format of the Pivot table would not change. I'm going to save. Sorry, I could not see at that time. So the file would be 0701 import from folder. And now I will show you the magic. I'm going to that folder, working file. I have data from 17. I'm just going to paste this file in this folder, and now I'm going here, saving this file, right click and then refresh. Sorry, first of all, I need to refresh this data. Now come over here and refresh this one. So in two clicks, I got my consolidated data of each year separately. So this is the magic of Power Query you can use. This is the must use function of Power Query for consolidated every type of data. 20. Join in Power Query: Hello, everyone. Welcome back. In this video, we will discuss about the joins in Power Query. Joins in Power Query are powerful tool that allow you to combine data from two or more tables based on a common column. Let us take the example. We have customer table over here and we have order table over here. Now we want to extract data based on different type of joins. So they enable you to create meaningful relationship between the datasets, making it easier to analyze or draw insights. Whether we are trying to match customers with their orders, identifying missing data or merge information from multiple sources. Our query provides several joint types, including inner joins, left outer joins, right outer join, full outer join, and interjoins. Each types serves specific purpose, offering flexibility for different data analysis scenarios. So we will discuss each type of joins with the examples. Here we have table customer, where we have customer ID, and this is the table of orders where we have customer ID. Basically the common field in both the tables is customer ID. It may possible some IDs are available in the orders, some are not. So we need to create different type of scenarios. So first one is inner join. So you need to consider this is a left table, and this is the right table, and we are going to learn about inner joins. So what would be the inner joins? Inner joins means in both tables, the data related to both combinations must be available. In this example, only customers who have placed the orders, so here we have four customers, but two customers ID is available in this order table. So only two records will be joined. Here would be the result. Customer ID with the order ID will combine, and we can see in the results. So this is the inner join, where the joint field must be available in the both tables. So now moving to the left outer join, left outer join means all the data from the left table, and whatever the field available in the right hand side tables will bring over here. So all customers matching orders were available. So here we can see all customers whether the join is available on the right hand side or not. We can see all the data. It is coming over here, and it will bring the data which is available in the order table. So these are the two fields which is available in the right hand side, but we can see all the data from the outer table. So this is the left outer join. And now moving to the right outer join, it is completely reverse of the left outer join. It means all the data from the right hand side table be available in the result. But we need whether it is matching to the left hand side table or not. So we can find all the data from the order, so 101, 102, 103. So all data is coming from this table from the right hand side table. But here we can see only this data is coming from left hand side. So this is the right outer join. Now moving to the full outer join. So whether data is matching or not, all data will be in the result table. All customers and orders with matches were possible. So here we can see that all customers are coming. This is the all customers data, and this is all orders data. And whether it's matching or not, we are getting null results. Now moving to the antijoin left, antijoin means customer who have not placed the orders means it is completely inverse scenario of left outer join. It means in the left outer joins, we were getting the orders which were matched, but in the antjoin which is not matched. So here we have orders, sorry, customers. And here, customers who have not placed orders, so who have not placed orders means these two customers have not placed any orders because these IDs are not available in the order. Anti join right means orders with no matching customers. So this customer ID is not available in our database. That is why only we are getting one order ID is coming over here. So these are all joins available in the Power Query. So in the next video, we will discuss a practical example and I'll show you all the scenarios in Power query. So let's move and join with me in the next video. 21. Join in Power Query Practical: Hello, and welcome once again. In this video, we will do the practical examples related to the joints we discussed in the previous video. So first of all, we have to create queries of these tables, select the data, right click, get data from the table ranges, click Okay, and give the name, customer table, press enter, go to close and load to only create connections, then Okay. We will do the same task with the order ID. Okay. Then rename the table name to order, press Enter, go to close and load to only create connections, and then okay. Now finally, we will go to the data tab, click on G data, select combined queries, and click on merge. So here, we will get all our queries available in this file. But in our example, we discussed left and the right. But here, it is not left right, it is top, and this is bottom. First or second, we can say, but you can consider the top one is left and the bottom one is the right. Now click over here, this is our left table and order is our right table. And we have join field customer ID over here. So once we select both tables, we will have join kind over here. So first one was inner join, so we will select this one. We will select Inner Join only, and we will click Okay. So this was the result we were expecting. So we have join table. If you want data from the order table, you have to click on this arrow button and remove this one because we don't want to prefix the name of the IDs in the data. Now click Okay, we got our data. So now we can remove this one because we don't want we want customers order ID and the product ID from the order table. Go over here and you can rename this to inner, Inter, close and load. It will be available as a new sheet in this workbook. Again, we will go to data, give data, combined queries, march queries. So now this time we will select customer. Here we will select orders, and this time, we will select left outer join. So it is coming automatically. It means all from the first matching from the second. So, select the combination common field. We will get the green tick, and we can see selection message two or four rows from the first table. Now, click Okay, we got the data. Click over here, remove this one. And then if you don't want customer ID, then you can deselect this one. Click Okay. We got our desired results. Now rename this two left outer join. Inter close and load. Save this one. Again, we will go to combine queries, select this one, merge, and this time we will select customer and then left. Sorry, orders, select the common, then out, sorry, right out. Click Okay. Click over here, remove these two fields and save this one, right, join, press Enter, close and load, and save this one. Again, we will go to combine queries. Then the march queries, we will select customer, and then the orders. Then left nt, select the common field. Okay, click over here, rename these two left nt, and close and Load. Save this one. Now we will go to combine and merge once again. We will select customer order, selecting the common, and then write t, click Okay. All processes are same. And this would be write t, press intersive and load. That's all we got. So we will discuss some advanced uses of the join incoming videos. 22. Join In Power Query Advance: Hello, and welcome once again. In this video, we will take one practical example related to the human and resources department in which we have two type of data. One table is employ table in which we have employer related data, employee ID, their name, department, and the location. In the next table, I have data related to the training program. In each training program, I have employee ID, which is the common field. Now my task is to retrieve these six different reports. All reports must be dynamic means whenever there is any update or change in this report, it will update automatically. The first one is find employees who have participated in at least one training program. So let us begin and how to find out this type of reports using the Power Query join option. Let us begin. So first of all, I will open a new file. I will save it now I need to bring the data. So for this, first of all, go to data, get data from file, go to Excel and select that file in which you have data, import it and select the sheet in which you have data. So I have employee related data in the employee table and click on Load. So data will be loaded. Now go to query, go where it. Now we need the training related data, so we need to do the same task once again. Get data from Excel, selecting the sheet, select the worksheet in which we have data. Now we have two queries, training related data and the employee related data. Now again, go to data from here, go to combine queries, merge, and select employ and in the bottom select training. Select employ and employ This is the common field between the two tables. And this time, I need to before that, I need to check what kind of data we required. So we find employee who have participated in at least one training program. So we need to get inner data because in both tables, the employee ID will be there and select inner, click Okay. And click over here, remove Employee ID and this one and click Okay, and then click, sorry, here we need to give report one. And then close and load. So these are the employees who have taken or participated in at least one training program. And it is completely dynamic, it means, let's see, I will go to my main sheet here. This is the training program in which some employee have been attended. So I'm just going to copy this training, and I'm increasing. Sorry, here, I do not have let's say six. Okay, this would be six, and this is the training. And if you open that sheet and if you refresh it, so that will be updated automatically, so you get the complete status. So now let me go over here. The next one is list all employees with their training details available. So for this, we will use left Outer because we want to get the status of training of all employees whether they have attended or not. For this, we will come over here, and this time, instead of doing Mergeta once again, so we can duplicate it, and this would be report. Two. And this time, I'm going to losing this one and then click on Merge Queries. So from here itself, we can get the data. So instead of this one, select employ, go to Merge query, and this time, select training. And from here, left outer, select the employee related data and click Okay, and click over here, remove this one, Employee ID, click Okay, and rename this. So once again, we will follow all the steps. From here, we will go to the combined queries, then the merge query, and we will select employ and then training, select this one and the left outer because this time we want all employees. So all employees are coming and their training related data is coming here. So I need to expand this. I do not want employee ID. I don't want to prefix it o and rename it too. Report, two, and then close and load. Here we go. So these two have not attended any training remaining all the status is coming over here. Now the next requirement, list all training records, including those without matching employees. So we need to use write join this time. So again, I will go to data, get data, combine query, merge query, and then employee training, select these two and write, Okay, I will expand this one. Move, remove, Okay, rename to report. Three, enter, and this time, we can move training ID, training program status, and then close this one. So all training is coming over here. And in this training, there is no employee record. Now, fourth, combine all employee and the training records, ensuring no data has been missed. So this time we will use full join, go to TsetGth data, get data, combine, merge, employ, then training, and then pull out, select common field, click Okay, click over here, and Okay. If you don't want employee ID, then select and remove from here as well, and then you have to rename this to report for. Close and load, and the next one is find employees who have not participated in any training. For this, we have to use ti join left. Come on data, get data, combine queries, merge, select employee, then training, select tileft. Before that, we have to select the common field and click Okay. So this is the report. Five, click over here, save it. Close and Load. These are the employees who have not attended any training. Now go to Data, get data, combine, merge, employ training. Okay. Here, we need to change write t, then Okay, click over here, remove employee ID, remove this one, then Okay, then we have to save this. Before that, we have to rename if you want. So these are the reports and all are dynamics. So whenever there is any change in the source file, that data will be automatically update in a single click. So this is the practical example you can practice for your learning. 23. Outer Join vs Xlookup and Vlookup: Hello, and welcome once again. You might be thinking, instead of using joins, outer joins or any other kind of joints, why we don't use X Lou or VLA? That is also used to bring up the value from other tables. So let me give you one example. So in the previous video, we discussed about the HR department in which we have employee related data and we want to bring training related data of any employee. So this is the table in which we have employee IDs and their name and other related data. And in this table, I have training related data. So if I use Xu or VCA, so if any employee attended two trainings, for example, 101 has attended two trainings, and if we use X lookup or the VLab, both rows data cannot bring over here. It will bring the nearest. It means the first training data will come over here. But if we use outer join, we will get the data of both trainings. So let's begin with the example. So here in employee table, I want to bring first, I want to bring here later on because I will use this table for outer join. So I will use Lookup and lookup value would be employee ID then the training data. And here I will select this complete row and then coma then two, then coma then zero, and I will close the bracket and press Enter. So I can bring only one training data. This is the problem with V Lou or the X lookup. But if we use, let's say, I will add this to my query data, and then I will give the name to no, sorry, employ and I will use this table as create connection only. I'm going to save this, and then I will do the same for training table as well. So I will give training, close and load to save it. Now, if I go to data, get data, combine, merge, and here I will select employee in the training if I select and if I select left join, and then click Okay. Here if I click over here, OID and this one, and then okay. So if you see here that 101, Alice has attended two trainings that is coming over here. But if I go over here, here, that was not possible. So this is the main difference using the X lookup V Lou or using the join option in the power query. 24. Join with Multiple Fields: Hello, and welcome back. In this video, we will discuss about multiple field matching. Here is an example. I have employ table here employee data, employee code, name, department, and the location, and I have different data in which I have employees location wise, their attendance data. So now I need to match employee code location with their attendance status of all dates. How we can achieve that. So for this, we will use Power Query join options. So go to data first, click on Queries and Connections. I have already connected both the tables using the connection only feature, which we have already discussed many times, so I'm not going to use that one again. So this time I'm going to data, get data, and I will go to combine queries and the merge queries. First I will use employ and below I will use attendance, and this time, I will use employ and then employee. Then we have to press and hold control key then location. And here also, we have to select a location. So now we can see one and then two, one and two. So we have two wheels matching for proper record. Here we have very less data. Just think about if you have thousands of data. Now select left outer join if you want all data from the left hand side and the matching data from the right hand side, click Okay, and we got this data. Now, click over here, deselect the employee ID and deselect this one and click Okay, and you can change the format to the date only and save it. So this is the data we got using the multiple connections. And if you want to know all data, then we have to merge employ and attendance, one, one, press and hold Control key location and the location, and then select full Outer data and then click Okay. Click over here, Deselect Employee ID and deselect this one and click Okay. Change the data type. To Tate and then close and load. So this time, we have missed one attendance of this location for which we do not have any data in the employe table. That is why it is coming plan. So this is how you can use the joint tables with multiple fields. If you update the data here. Let's say I want to add more data over here for three, and this is employee ID three, and this is New York. That was present and go to here and refresh, we got the data. This is how you will use this join option with multiple fields.