A.E MODULE IV STUDY METARIAL.pdf

NETT ZONE COMPUTER EDUCATION CENTRE “PREPARE – PRACTICE - PERFORM” A.E MODULE-IV AN AUTONOMOUS BODY REGISTERED UNDER GOVT.OF W.B. BASED ON TR ACT 1882 INSPIRED BY NATI...

NETT ZONE COMPUTER EDUCATION CENTRE “PREPARE – PRACTICE - PERFORM” A.E MODULE-IV AN AUTONOMOUS BODY REGISTERED UNDER GOVT.OF W.B. BASED ON TR ACT 1882 INSPIRED BY NATIONAL TASK FORCE IT & SD. Registration Number: IV – 1903 – 04384 - 2017 REGD UNDER MINISTRY OF MSME, GOVT OF INDIA Registration Number: UDYAM-WB-10-0043486 Quality Management System ISO 9001:2015 Certificate No: QMS/230620/1271 STUDENT NAME: ____________________ REG. NUMBER_______________________ BATCH NUMBER_____________________ COURSE NAME______________________ ISSUED DATE_______________________ GIVER SIGNATURE__________________  Contents 01.Power Query - Text Functions 02. Split Column by Delimiter 03. Left Right and Mid Functions 04. Text with Delimiter in Excel Power Query 05.How to Add Prefix & Suffix 06. Date Function 07. How to Extract Start & End of the month, Quarter & Year 08.HOW TO CALCULATE AGE USING OF BIRTH 09.DIFFERENCE BETWEEN TWO DATES 10. PERCENTAGE, PERCENT OF, MODULO 11.APPEND QUERY MULTIPLE WORKSHEETS IN TO ONE SHETT 12. POWER QUERY REPORT BY PIVOT TABLE 13. EXTRACT DATA FROM THE SAME WORKBOOK 01. Text Functions in Power Query  We have a text data, let's assume that the text spaces of that data are random. We have previously done the Trim function in Advanced Excel, there are many text functions like that Trim function. which we will learn here.  From Text data by selecting any cell > Data menu > get & Transform Group > From Table Option > Create Table Dilog Box Open > Click on Ok  We will see here the Power Query Editor window will open  Here in Transform menu > Text Column/Number Column/Date & Time Column.  If you need to remove unwanted spaces from the data then Text Column > Format > Trim  If you want to transfer your data to uppercase or lowercase then select three columns in a row then > Text Column Group > Format Option > Uppercase or Lowercase  The biggest benefit of Power Query Editor is that everything we do is recorded here. We can notice - Query Seeting on the left side of the Editor, there is a group called Applied Step, in which our recent applications are stored in the group.  Remember that Ctrl + Z function does not work in Power Query Editor, if we have any wrong step then we can delete from within Query Settings.  According to the data, here we want Prefix, first name, Last name to come together, so the way I want to merge the data, we have to select the columns one after the other. then  Transform Menu > Text Column Group >Merge Column > Merge Column Dialog Box Appear > Under the Separator Group >Select Space > Put Proper Column Name > Press OK Now we have to convert the data to excel file after this data becomes a text function  In Power Query Editor Home Menu > Close & Load Option > Dropdown Menu > two options will be available like 01. Close & Load 02. Close & Load to > The difference between the two is that if you click on the first option, the data will be transferred to the new sheet and if you click on the second option, it will give you the option that you want to take the data in the new sheet or in the existing sheet. 02. SPLIT COLUMN BY DELIMITER  Delimiter means SPACE, COMMA, semicolon etc.  First place the cursor on the data > Then click on the Data Menu > Under the Get & Transform Data Click on From Table Option. > The Create Table Dialog Box Appear. > Then Click Ok. > After That Our Data Will Be Transferred to Power Query Editor.  Then Click on the Transform Tab > Under the Text Column > Click on Split Column > Drop Down Menu Appear > Click on By Delimiter > Split Column By Delimiter Dialog Box Appear. > Under The Select or enter delimiter Comma already Selected  Then click on OK 03. Left Right and Mid Functions  First place the cursor on the data > Then click on the Data Menu > Under the Get & Transform Data Click on From Table Option. > The Create Table Dialog Box Appear. > Then Click Ok. > After That Our Data Will Be Transferred to Power Query Editor.  Then click on the Add Column Tab > Under the from text > Click on the Extract > Drop -Down List Appear > Then Click on The First Characters > Insert First Characters Dialog Box Appear > Under the Count Box put the number 3  Then click on the Add Column Tab > Under the from text > Click on the Extract > Drop -Down List Appear > Then Click on The Last Characters > Insert Last Characters Dialog Box Appear > Under the Count Box put the number 8 04. TEXT WITH DELIMITER IN EXCEL POWER QUERY  First place the cursor on the data > Then click on the Data Menu > Under the Get & Transform Data Click on From Table Option. > The Create Table Dialog Box Appear. > Then Click Ok. > After That Our Data Will Be Transferred to Power Query Editor.  Then click on the Add Column Tab > Under the from text > Click on the Extract > Drop Down List Appear > Then click on Text Before Delimiter > The Text Before Delimiter Dialog Box Appear > Then put Hyphen - That is, how many words are there before the hyphen, all will be separated. 05. HOW TO ADD PREFIX & SUFFIX  First place the cursor on the data > Then click on the Data Menu > Under the Get & Transform Data Click on From Table Option. > The Create Table Dialog Box Appear. > Then Click Ok. > After That Our Data Will Be Transferred to Power Query Editor.  Then click on the Transform > Under the text column > click on the Format > Drop down list appear > Click on Add Prefix Option > Prefix Dialog Box Appear on the screen > Then Enter the Prefix Value > Then Press Enter  As we can see in the case of this data that there is age number - now add year after age i.e., add suffix ----  Then click on the Transform > Under the text column > click on the Format > Drop down list appear > Click on Add Suffix Option > Suffix Dialog Box Appear on the screen > Then Enter the Suffix Value > Then Press Enter 06. DATE FUNCTION  First place the cursor on the data > Then click on the Data Menu > Under the Get & Transform Data Click on From Table Option. > The Create Table Dialog Box Appear. > Then Click Ok. > After That Our Data Will Be Transferred to Power Query Editor.  When Transferring Normal Data to Power Query, Normal Date Will Be Converted to Date & Time.  If You Need to Convert to Normal Date Then You Will See an Icon Next to Order Date Heading Click on It  Drop Down List Appear > Then Click on Date Option.  Change Column Type Dialog Box Appear > Then Click on Replace Current Option.  Now I will separate the year from the original data for that –  Click on the Add Column Tab > Under from Date & Time Option click on Date > Drop – Down List Appear > Click on Year Option. Drop – Down List Appear > Click on month Option. 07. Extracts Start & End of The Month, Quarter & Year  First place the cursor on the data > Then click on the Data Menu > Under the Get & Transform Data Click on From Table Option. > The Create Table Dialog Box Appear. > Then Click Ok. > After That Our Data Will Be Transferred to Power Query Editor.  When Transferring Normal Data to Power Query, Normal Date Will Be Converted to Date & Time.  If You Need to Convert to Normal Date Then You Will See an Icon Next to Order Date Heading Click on It  Drop Down List Appear > Then Click on Date Option  Change Column Type Dialog Box Appear > Then Click on Add new step option  We will find out start quarter here > Then Click on the Add Column Option > Under the From Date and Time Group > Click on Date Option > Then Click on Quarter > Then Start of Quarter.  According to the data as here - 18th June 2025 - we want to know which day of the quarter this date is?  To get it subtract start of quarter from order date for – select order date then press ctrl then select start of quarter > Under the From Date & Time > Click on Date > Drop Down List Appear > Then Click on Subtract Day.  According to the data as here - 18th June 2025 - we want to know which day of the Year this date is?  Then we have to subtract the start of the year from the order date to get the start of the year earlier ---  Select the Order Date column > Under the from date & time > Click on date > Year > Start of Year.  To get it subtract start of quarter from order date for – select order date then press ctrl then select start of Year > Under the From Date & Time > Click on Date > Drop Down List Appear > Then Click on Subtract Day.  Then select the Order Date > Under the From Date & Time Option > Click on Date > Drop Down List Appear > Then Click on Month > Day in Month  Then we can remove Start of Quarter and Start of Year 08. HOW TO CALCULATE AGE USING OF BIRTH  First place the cursor on the data > Then click on the Data Menu > Under the Get & Transform Data Click on From Table Option. > The Create Table Dialog Box Appear. > Then Click Ok. > After That Our Data Will Be Transferred to Power Query Editor.  When Transferring Normal Data to Power Query, Normal Date Will Be Converted to Date & Time.  If You Need to Convert to Normal Date Then You Will See an Icon Next to Order Date Heading Click on It  Drop Down List Appear > Then Click on Date Option.  Change Column Type Dialog Box Appear > Then Click on Replace Current Option.  Click on the Add Column Option > Under the From Date & Time Click on Date Option > Drop – Down List Appear on the Screen. > Then Click on the Age option. > We will see the age converted to number of days. > Then select Age column > Under the From Date & Time > Then Click on Duration.> Then click on Total Year Option.  Then click on 1.2 option under the Total Year column. > Then click on Whole Number. 09. DIFFERENCE BETWEEN TWO DATES  First place the cursor on the data > Then click on the Data Menu > Under the Get & Transform Data Click on From Table Option. > The Create Table Dialog Box Appear. > Then Click Ok. > After That Our Data Will Be Transferred to Power Query Editor.  When Transferring Normal Data to Power Query, Normal Date Will Be Converted to Date & Time.  When there are many columns, we will apply a shortcut to quickly transfer to date format which is ---  Select the column and right click on it > dropdown menu > change type > date  Select Delivery Date Then Select Order Date > Then click on Add column > Under From Date & Time Option > Subtract Date. 10. PERCENTAGE, PERCENT OF, MODULO First place the cursor on the data > Then click on the Data Menu > Under the Get & Transform Data Click on From Table Option. > The Create Table Dialog Box Appear. > Then Click Ok. > After That Our Data Will Be Transferred to Power Query Editor.  Click on the Add Column > Under the From Number > Click on Standard > Drop- Down List Appear > Click on the Percentage > Percentage Dialog Box Appear >  If we need to know 100% of 1500 then > Add Column Option > From Number Group > Click on Standard > Drop Down List Appear > Percent Of option > Percent of Dialog Box Appear > Then Enter Value to Find Result.  Think we're dividing the sales number by 3 here – What is Left that is Remainder.  Click on the Add Column > From Number Option > Standard > Modulo.  11. APPEND QUERY MULTIPLE WORKSHEETS IN TO ONE SHEET Corporate has different data every month, and it is on separate sheets. At the end of the year, a master database is created by organizing that All data together. When the data is arranged vertically it is called Append Query and when the data is arranged horizontally it is called Marge Query.  First, we need to open a new worksheet  Data Menu > Get & Transform Group > New Query > From File > From Excel Worksheet > Import Data Dialog Box Appear > Select the Location > Select the File > Then Click on Import > Navigator Dialog Box Appear > Select Multiple Items > Then Click on Transform Data > Power Query Editor Open > Under the Home Menu > Combine Group > Append queries > Append queries as new > Append Dialog box Appear > Click on the Three or more table > Select table from Available Table > and add to table to append > click on ok > Then click on Close and Load > Close and Load to > Load to dialog box appear > click on only create connection > Then click on Load. 12. POWER QUERY REPORT BY PIVOT TABLE  To transfer Excel data to Power Query, select any cell of the Excel data and then press CTRL + T to transfer it to the table.  When our data is transferred to the table we will see that the Design menu is activated in the menu bar.  When we click on design menu ribbon > find a group named properties inside design menu ribbon > there we can give proper table name according to the data  Then we have to click on Data Menu > Data Menu Ribbon > Get & Transform Data > From Table / Range option > Then we will see the Power Query Editor will open. o In power query editor we get total four menu > ie 01 Home, 02 Transform, 03 Add Column, 04 View  At the extreme left side corner there is an option called queries, if we click on it then the queries section will expand and we can see our table name there.  In this way all the tables we will create will be shown in the form of a list in the queries section  Then we will get a section called query setting on the right-hand side where we will get a sub section called properties inside which we will show our table name. If we want, we can change our table name from  here > As per requirement we will change our table name and give table name Sales & SalesRep  Immediately we can see on the left-hand side that the table name has changed in our queries section.  On the right-hand side there is another important section called Applied Steps where we get the Source and Change Type options.  If we click on the source option then we can see the source of the Excel workbook o Then Home Menu > Transform Group > Split Column > Dropdown List Appear > Then  Click on By Delimiter > Insert Step Dialog Box Appear > Then Click on Insert Option >  Split Column By Delimiter Dialog Box Appear > Under The Select or Enter Delimiter Section Click on the Dropdown > Then Click on the Custom > A Box Will Open Below the Custom Section > Write here space ; space > then click on “Each Occurrence of the delimiter  NOW WE WILL LOAD THE DATA IN EXCEL > HOME MENU > CLOSE GROUP > CLOSE & LOAD TO > IMPORT DATA DIALOG BOX APPEAR > THEN CLICK ON THE EXISTING WORKSHEET > SELECT CELL > THEN CLICK ON OK  JUST CLICK ON THE NEW TABLE >DESIGN MENU >UNDER THE TOOLS GROUP > SUMMARIZE WITH PIVOT TABLE > CREATE PIVOT TABLE DIALOG BOX APPEAR >CLICK ON EXISTING WORKSHEET > THEN CLICK ON OK. 13. EXTRACT DATA FROM THE SAME WORKBOOK  First of all we will select BIKESQ1 data table range > THEN CLICK ON THE DATA MENU > UNDER THE GET & TRANSFORM DATA > FROM TABLE/RANGE  POWER QUERY EDITOR will be open in front of us > we can see BIKESQ1 table range in properties name > in applied steps we can see two options named 'Promoted Header' and 'Changed Type' will be default, this is when we transfer to Power Query from Excel It came automatically when I did  Now we will delete them from the applied steps  Then Click on the Home Menu > Under The Transform Group > Click on Use First Row as Headers > We will see the above column 1 column 2 etc. will move automatically.  Then Click on the Home Menu > Under The Transform Group > Click on Use First Row as Headers > We will see the above column 1 Column 2 etc. will move automatically > Then Right Click on Product Name > DropDown List Appear > Then Click Remove Other Column > Then Home Menu > Under The Reduce Row > Click On Remove Rows > Remove Duplicate.  Then Click On The Home Menu > Under The Close Group > Click On 'Close & Load' Option > Then Click on "Close & Load To" > Importing Data Dialog Box Appear > Then Select  Existing Worksheet Redio Buton > Then Select the Cell Of Summary By Product Column > Then Click on Ok.  We can see that a column of Product Name has been created in the Summary by Product column > If we write sales in the column next to it, it will automatically take the table format > Then Press Enter > In the cell just below that we can do a quick formula > =sumifs(sales value F4 to Lock,Product Name F4 to Lock,cargo bike from summary by product name) then press enter > Then copy the formula till end. 'Promoted Header' and 'Changed Type' will be default, this is when we transfer to Power Query from Excel It came automatically when I did  Now we will delete them from the applied steps  Then Click on the Home Menu > Under The Transform Group > Click on Use First Row as Headers > We will see the above column 1 column 2 etc. will move automatically.  Then Click on the Home Menu > Under The Transform Group > Click on Use First Row as Headers > We will see the above column 1 Column 2 etc. will move automatically > Then Right Click on Product Name > DropDown List Appear > Then Click Remove Other Column > Then Home Menu > Under The Reduce Row > Click On Remove Rows > Remove Duplicate.  Then Click On The Home Menu > Under The Close Group > Click On 'Close & Load' Option > Then Click on "Close & Load To" > Importing Data Dialog Box Appear > Then Select Existing Worksheet Redio Buton > Then Select the Cell Of Summary By Product Column > Then Click on Ok >  We can see that a column of Product Name has been created in the Summary by Product column > If we write sales in the column next to it, it will automatically take the table format > Then Press Enter > In the cell just below that we can do a quick formula > =sumifs(sales value F4 to Lock,Product Name F4 to Lock,cargo bike from summary by product name) then press enter > Then copy the formula till end..