 Hey guys, welcome to SS Unity Accessile site and today we are going to see about the power query option inside the SEO data factory. So what is the power query? So power query is available inside the Excel, inside the Power BI inside the SEO data factory as well. We can also call this as the wrangling data flow. So this is the special type of the data flow by which we can do certain transformation and we can modify our data as per our requirement. So go to on the browser and we'll try to understand the requirement first. So here we have this table which is the sales table. So what is the requirement? So here we can see the sales data. So this sales data is having the data like sales order ID, date, customer ID, then quantity, value and country. So what we want to get? For example, if your manager or your senior manager have requested you to get the country by sales, then how you can do that? Or you can directly write the query here and you can get the data or you can do the same thing inside the SEO data factory if that data is going to required on the daily basis, then you can create the SEO data factory for the same. So in the output, we will be going to have only two columns first is the country and second is the sum of this value. Let me go in the browser. And here before going to so here we can see the option for the power query. So before going to implement the power query, let me go inside the YouTube. So inside the YouTube, you can search for SS unity. So once you will search SS unity, then you will get my YouTube channel. So you can open this one. And after this, you can go in the playlist. So in this playlist, you can scroll down side and here you will see the playlist for the power BI tutorial for beginners. So let me open this one. So this playlist you can open and here you can directly jump on the video number five. So here we have started implementing about the power query editor. So this power query editor is nothing but the power query which is inside the SEO data factory as well. So here we have explained each and everything which is available inside the power query. So you can see the edit rows in the next video, then append queries, then edit columns, then column data. So all these videos I have already recorded here. So you can watch and you can implement as per your requirement inside the SEO data factory. So let me go here and quickly try to implement the scenario which we have discussed. So let me try to add the new power query here. And once we have selected this power query, let me call this power query as customer sale by country. Now, once we have done this, then here we can observe we have two tabs. First is the setting and second is the parameter. If we are going to have any parameter inside the dataset, then those will be appear here. Otherwise, we will not see anything. So let me go in the setting. Here we can add once dataset or more than one. So here we can see this dataset. So as we have already created the dataset for the same, so let me try to use that one. So that is the dataset sales as your SQL. So this dataset once we have selected, it will be going to open in the power query editor option. So that is going to load soon. So we have to wait. So here we can see the currently not all power query M function are supported for data wrangling despite being available for authoring. So it means we cannot use all the M functions that we have seen inside the Power BI tutorial video inside the SEO data factory. But most of them we can use. Here we have added this dataset. So if you want to add another one, you can click on this plus symbol and you can add another dataset as well. So for example, if you want to select this says dot CSV, then that will be going to load as well. So here it is loading as we can see. So here we can see under this adf resources, we will see two options. First, we can see dataset sales and second for this one. So I'm not going to use the second one. So let me quickly delete that. So now here we can see this data. Let me try to make this as maximize mode here. Let me scroll this downside. So this is the data that we have. So what we want to do with this data, we want to do the group by on the country and some of this value. But here we can see the data type of all these which is the string as we can see ABC. So either we can convert directly from here with the 123 like this, or we can do one thing, we can select all these columns. And after selecting, you can go inside the transform. Here we can see the detect data type option. So let me try to click that one. So it will be automatically detect whatever the data type is available on that column. So as here we can see this is converted to 123, which is the integer. This is the string then 123123. And this is the numeric value. So that's why 1.2. And this is ABC for the country. So here inside this query setting, we can see the applied steps. So steps are going to add one by one over here. So if you want to remove any step, then we can directly go and click on this cancel button. So it will delete that step that we have added. So in this step, we have used the detect data type. So everything got changed. What next we want to do? We want to do the group by on this. So under this transform, we can see the option for the group by. So let me click on this group by here what it is saying, it is saying the group by on the SOID all these columns. But as per the requirement, we want to do the group by only on the country column. So as we can see everything is selected here. So we don't want group by on the SOID. So let me click on these three dots and delete this. Similarly for the date, customer ID, quantity, value, we want only group by on the country. So we have selected that one. After that here we can see the new column name. So this column name we want to keep like total sales and operation like what we want to perform. So we want to do the sum of the value. So here we have the sum and on which column we want to do that, that should be on the value column. So what it will be doing? It will be going to get the country. And after that, the second column is the total sales, where is the sum of this value? Let me click on OK. So once we click on OK, this operation is going to perform immediately and we can see the data. So this feature is very good inside the Azure data factory. Instead of using the data flow, if your requirement is going to fulfill by using this power query option, then you can also use this. So here as we can see we have all this data and we can quickly verify this as well. Similarly, here we can see one step is also added and this step is saying group by. So this we have done. So after making all these changes, this is your final data. And here we can see this user query. So if you want to rename this, so here we can rename and call this like sales by country like this. So this will be going to updated with the sales by country. So here we get a warning and warning is saying power query is invalid. Could you find user query in matchup and user query mandatory in Power BI? So after this, here we have certain limitations. What is the limitation? Like if you want to update this, which is the user query name, then we cannot do that. Here we have the option to update that we can see here we can edit an update, but it will not allow over here. But inside the power BI, we can edit that. Now let me try to publish this. So this is in progress. Publish is completed. So now here we can go and now let me try to add a new pipeline to execute this power query that we have created. So new pipeline is here and inside this, if you can go in this power query, let me try to drag and drop this power query activity. So this power query activity is used. Let me try to on this debug option. So it will take a little bit time. So this power query will be going to execute the power query that we have created sales by country. So the purpose is very simple. We want to load that data into the output folder of this blog storage and by which anyone can access this. So this process we want to do on the daily basis so we can create the pipeline as we are going to create and schedule that. So here if we can go on the setting then we can see the power query. So we have to select the power query that we have created. So power query we have created the sales by country after selecting this like this is the source we have seen, but whatever the sync by which we want to keep the file. So we can directly go in the here in the sync and here either we can select the existing data set or let me try to create a new one. So new data set it is asking like on which location we want to keep the file. So I want to keep the file on the as your blog storage. Let me click on continue, delimited text click on continue. Let me call this as sales by country link service we have already created. So let me select that one. First row as header we can browse the path the path that should be output folder. Let me click on okay. So after making all these we can click on okay. So what will be the file name that we need to select now. So let me minimize this. So here we can see the other options first is the edit once we click on edit then it will open this in this format and here we can modify this data set. Next we can see this property window. So once we click on this property window here we have the option to select the file name. So let me try to use the single file. Once we are going to load that into single file then partition that should be single. So we can set that the file name that is the sales by country dot csv. So after doing that we can go in the option we can see all these options inside the mapping. Now here let me try to use the auto mapping or we can disable it and here we can specify the output column names that we want. So I am okay with the country and total sales. So I am going to use the auto mapping. Now let me try to click on okay. So we have done with this option. If you want to delete this query then we can delete and add a new query here. Here we can see the plus symbol. So if we want to use the multiple queries here then we can use this plus symbol so it will add another one and this query as of now we have only one so that we can see only one and here if you want to load that into multiple places we can do that. All these options are only one here. Let me delete this. So we have done with this. Let me try to publish this. So now let me try to debug it. So once it will be executed your output folder will have another file that is the sales by country and where we will see the data. So it is in progress. Let me refresh it. So here it got executed successfully. Let me go into the output folder and let me try to refresh this folder. So we should be going to see another file which is the sales by country. Let me try to open this. So this file should have two columns. One is the country and second is the total sale that we can see. So here we can see the India the sale amount then the US the sale amount and the UK the sale amount. So here we have all this data. Let me go here. So this is all about the power query activity and this is the power query editor. So here let me go through a few things. If you can go inside the home then we can see the enter data option where you can insert the data. Here we can see the properties. So if you want to see the property of this query which we have selected we can see here if you want to make any change for the advanced editor options. So we will see everything as in JSON format like this. Let me cancel this. Here we can see the choose columns. So it will select only that column and other columns will be removed. Here the remove column. So it will remove that column which we have selected. Here keep rows everything is available. Group by we have already discussed. If you want to split any column by two then we can do that. Here we have the option by delimiter by number of characters by position. So all these options are here. So by which we can split any column. Here is the option to mark the first row as header. If you want to here we can see the data type of this. So if you want to make the change then we can do that. If we have multiple queries and you want to do the merge on that query we can do here. If you want to append the query then we can append from here. Go to the transform side and under this transform group by the same option. Use the first row as header the same option. Here is the option for the transpose. If you want to do the transpose we can do from here. Here we can see the reverse row. Similarly we can see the count row. Here we can replace. If you want to replace any value by any other value then we can replace it from here. Next we can see again the same thing. Here is the reject data type we have already seen that. Here we have all these like pivot and pivot. Here we can see like convert to a list split column is again the same. Similarly if we can see inside the add column. So here it is going to add all columns or from a selected. So that is available. Custom column if you want to generate index column we want to generate any conditional column we want to generate we can do all these. If you want to create the duplicate of this column that option is also here. Extract if you want to extract by length or the first character so everything is available we can do all these. Similarly like mathematical operations we want to do so that is also available. So these all the options we can use and I have already discussed about all these in details of this power wave playlist. So here you can see like delete or reference or you can see the data modeling then the merge query that I was talking about then the index column duplicate column if else condition inside the M query I have discussed mathematical operation then everything you can see. So I have already explained over here. So you can watch all these videos for your reference. So that will make more easier about the power query option inside the Azure data factory. So thank you so much for watching this video. If you like this video please subscribe our channel to get many more videos. See you in the next video. Thank you so much.