Speaker 1: This macro is running to map these items with every element of this data. I need all these items for every single of these codes. This is scenario one. The next scenario is this is a pivot table data but the data is not in pivot table. It is pasted as values. So I cannot change the data in this format. I need to do it manually like this. Copy this item and paste. Using Power Query, both these tasks can be done very easily. No VBA coding is required and you don't need to monotonously copy paste the items to get the desired format. Let's learn how we can harvest the hidden powers in Power Query in Excel. Before Power Query was introduced by Microsoft, there was only one process to do this first job, VBA coding. But you will witness today in this video that VBA coding is not required for this task and it is faster than VBA. So we have two datasets in two different data ranges and we need to map all product code and product name from this data range to every customer code and customer name of this data range like this dataset. I have product id and product name in D1 sheet and customer id and customer name in D2 sheet. First job first. If you have watched my previous videos then you must know what is first job first. You must convert data range to table format to work in Power Query environment. Press CTRL plus D and quickly convert these data ranges into tables. Now from D1 sheet, go to data, select from table or range. You will be in Power Query editor. Click close and load and close and load to. Select only create connection. Table 1 is connected. Now using same process connect the table 2 in D2 sheet. Now we have two tables connected. Double click on any table, say table 1 and you are again in Power Query. Check these two tables. One is having product id and product name and the other is having customer id and customer name. Select table 1. Make a copy of this table by pressing CTRL C and CTRL V. Change the table name from here or you can rename this from here. Now the important thing. We need an additional column after this last column. Select this column and from the add column menu, click on custom column. Change the column name as you like. I will use ABC and here within custom column formula box, type table 2 after the equal sign and hit tab. This is because we already have product id and product name in this table. We need to map table 2 data which are in table 2 as customer id and customer name. Click OK. So we have a new column ABC and now if you click on this new cell table, you will see this column is holding the table 2 data, customer id and customer name. And this table is holding product id and product name. Click on this two-way arrow and you will get this tiny pop-up. Uncheck this and click OK. If you don't uncheck this, it will prefix the custom column name like this. Better to uncheck this. To revert any changes you made, just check this properties window and click on delete. It is like undo command in any application. Now you know my next move. From home menu, click on close and load and then close and load to. I need table. Select table. Check new worksheet and click OK. It's done. Filter any customer id and count the item number 98. Row count in T1 is 98. The best benefit is that in future if you update any of these tables or both and click on refresh on final table, the data will be mapped as per the new dataset arrangements. I will discuss the next scenario in the next video. You can ask questions in comment. Thank you.