Computer Lab - Practical Question Bank

FACULTY OF COMMERCE,

OSMANIA UNIVERSITY

------------------------------------------------------------------------------------------

B.Com (Business Analytics) III Semester

Data Analytics Modeling

Time: 60 Minutes                                                                    Record : 10

                                                                                               Skill Test : 15

                                                                                               Viva - Voce : 10

                                                                                               Total Marks : 35

                                            MS Excel or Power query

                                                Table for 1-10 Questions

I.             Format the following Table as per Questions 1 to 10.

1.    Split the column Deptname-EmpID into two columns.

2.    Format the Employee Name column.

3.    Format the Gender column.

4.    Format the date to YYYY/MM/DD”

5.    Find the Number of years of service of service for an employee in the                               organisation as on date.

6.    Insert the currency “$” symbol in the salary column.

7.    Remove the decimal place in the salary column.

8.    If an employee has to retire at the age of 60, when will they retire.

9.    Employees whose experience is greater than 10 years will get a bonus of 10%.  Calculate the bonus paid.

10.    How many employees are there in each department and what is their total salary department wise.

II. Format the Table as per Questions 11 to 15.

              11. Remove blank rows.

12. Highlight blank cells and fill with “No Data”.

13. Concatenate First Name and Last Name columns.

14. Format the names of the concatenated column.

15. List out all the employees whose salary is greater than $3,00,000 and less than $4,00,000 give allowances of 5% and above $4,00,000 give allowances of 10%.  Find the total allowances paid by the company.

III. Format the Table as per Questions 16 to 20.

16. Using Excel, concatenate the columns Street name and city.

17. Insert "C-" in the left side of the customer Id in customer name column of the above data.

18. Using Excel Remove Extra spaces and format the names of customer name column.

19. From the above table remove the duplicate rows.

20. From the above table remove the customer whose customer ID is not a    4 digit number.

IV. Format the table as per questions 21 to 25.

21. From the above data in Excel, Concatenate the Headers.

22. From the above data in Excel, convert the numerals in Year column to text format.

23. From the above table in Excel, highlight the blank spaces and fill it with    "Not Available".

24. From the above table in Excel, select only the first name from the Employee name and paste in another column.

25. Create Email Addresses using lower case and underscore in between the names and end with “@gmail.com”.  (For example priya_dixit@gmail.com)

V. Format the Table as per Questions 26 to 30.

26.From the above table, remove the invalid phone number which are not in the proper format of having 10 digits, no characters and the phone numbers which are starting with 9/8/7/6

27. From the above table, put the phone numbers in US format.

28.From the above table, split the Address column into Address, City, State and ZIP  code.

29. From the above table, select only the middle name from customer name.

30.  From the above table, calculate the number of days required for shipment.

            Power Pivot and Power Query Questions:

 

            VI. Power Query link(Order data set 1)

                 Power Query link ( Order data set 2)

            Use Power Query Editor to answer the following questions:

        31. Remove the rows which has improper data.

        32.Split the column Customer ID into Customer ID and Customer Name.

        33.Remove the currency Symbol and the decimals (Give reasons for                     removing them) from the column Cookies shipped.

        34.Calculate the profits for each of the customer.

        35.How many days does it take for the delivery of orders.

        36.Load the second data set to the existing data set1.

        37.Create Pivot table for the cookies shipped year wise and quarter wise.

        38.Create pivot table and graph for the cookies shipped customer wise.

        39.Create pivot table to know the top 5 customers.

        40.Crate pivot table to know the relationship between the sales and the                 delivery of the orders.

        VII.          Power Query and Power Pivot Link (Sales_2018)

           Power Query and Power Pivot Link (Sales_ 2019)

 

        41.In Power Query Editor concatenate the first two rows.

        42.In Power Query Editor Split the column ship mode and container.

        43.What is percentage of total shipping amount for each order priority.

        44.Find the bottom 3 Shipment Mode and Container by total Unit Sell Price.

        45.Sort Customer ID by Unit Sell Price in ascending order.

        46.Calculate the number days for shipment and establish a relationship                 between the number days for shipment and shipment amount.

        47.Find the total selling price customer wise?

        48.Has Order priority has any influence on the shipping amount.

        49.Which shipment mode has more customers?

        50.Is order Id determined by discount percent?