EXCEL- Power query

step by step

Excel Formulas

Column1 Column2 Column3 Column4 Category & Product Column7 Column8 Column10 Order ID Order Date Customer Name Salesperson Category Product Name Unit Price Quantity Shipping Fee 1001 41666 Company AA Mariya Sergienko Beverages Coffee 14 49 66.542 1002 41666 Company AA Mariya Sergienko Dried Fruit & Nuts Dried Plums 3.5 47 16.6145 1003 41643 Company D Andrew Cencini Dried Fruit & Nuts Dried Pears 30 69 198.72 1004 41643 Company D Andrew Cencini Dried Fruit & Nuts Dried Apples 53 89 448.115 1005 41643 Company D Andrew Cencini Dried Fruit & Nuts Dried Plums 3.5 11 3.7345 1006 41651 Company L Mariya Sergienko Beverages Chai 18 81 141.426 1007 41651 Company L Mariya Sergienko 46 44 198.352 1008 41647 Company H Nancy Freehafer 9.2 38 36.0088 1009 41643 Company D Andrew Cencini Baked Goods & Mixes Chocolate Biscuits Mix 9.2 88 79.3408

Split the Category & Product into two columns Rename the columns to Category and Product Remove the rows that have empty category values Accomplish changing the salesperson column to last name, comma, first name format - Change the Order Date to a date data type . Add a column named Revenue which multiplies Unit Price field by the Quantity field Format the Revenue column as currency Add an Order Total column that adds the shipping value to the Rev- enue value