Scribd
Upload
2 views8 pages

TOP ANALYST

The document outlines a step-by-step guide for analyzing project and analyst data using Excel, specifically focusing on calculating average project costs and identifying the highest-earning analysts. It includes detailed instructions on importing data, transforming it, and creating pivot tables to summarize the findings. Additionally, it provides specific questions and answers related to individual analyst earnings from various projects.

Uploaded by

kodularcoding
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
2 views8 pages

TOP ANALYST

The document outlines a step-by-step guide for analyzing project and analyst data using Excel, specifically focusing on calculating average project costs and identifying the highest-earning analysts. It includes detailed instructions on importing data, transforming it, and creating pivot tables to summarize the findings. Additionally, it provides specific questions and answers related to individual analyst earnings from various projects.

Uploaded by

kodularcoding
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 8

20-Oct-24

TOP ANALYST

Project and analyst layout

We have multiple analysts working on multiple projects, let’s find


the average cost and which analyst worked on the biggest project.

NOTE: Think of the project cost being the amount made by the
company (profit).
20-Oct-24

Project and analyst layout

You will need the Excel file:

POWER QUERY ANALYST - STUDENTS 2024.xlsx

Project and analyst layout


STEP 1) [IMPORT] Click in the table anywhere and press ctrl + A to highlight
all and goto data – choose (from table/range). Check for the headers
box (if they have)and click ok.

Lets move the TEAM ANALYST column to the end of the table so we have
space, just drag it to the far right.

STEP 2) [SEPARATE DATA] Highlight the Team Analyst column goto HOME -
Split column - by delimiter. Ensure comma is selected at the top, check
each occurrence is selected, then OK. [ORIGINAL DATA IS DELETED]

We now have 5 columns with all analysts separate from 1-5, the NULL
simply means no extra analyst was used there, some have 3, 4 and some 5.
20-Oct-24

Project and analyst layout


STEP 3) [CREATE LIST] We need to convert the table into rows by unpivoting
them. Highlight ALL Team analysts columns 1-5, right click and UNPIVOT
COLUMNS. [this transposes them and takes them out of the groups].

STEP 4) [CLEANING] There is a problem in the VALUE column as some of the


rows with the names have extra spaces.

i) Right click on the column header VALUE, goto transform in the menu
and select TRIM (removes spaces).

ii) Now let’s delete any hidden formats (tabs, returns etc). Right click on
the column header VALUE again, goto transform in the menu and select
CLEAN.

iii) Finally, double click on the column heading Value – (rename to


Analysts).

Project and analyst layout


STEP 5) [COPY TABLE] Now let’s copy the table as we will change it and
combine it shortly. On the LHS, goto Queries, right click on Table1 and
duplicate. Rename it Table COPY.

[Make new column to group analysts] Click on the new Table COPY and goto
the top menu TRANSFORM, then choose GROUP BY.

Check the radio button - ADVANCED

The first box - Choose PROJECT NAME from the menu.

New Column name - Number of analysts

Operation - count rows

Column - leave blank and WAIT!


20-Oct-24

Project and analyst layout


STEP 6) [FORMAT] Click on ADD AGGREGATION (so we can add the money
made from each analyst).

Set the following parameters:

i) New Column name - Project value.


ii) Operation - Average
iii) Column - Choose Project cost (US$) from the drop down menu.

Click ok.

NOTE: Table 1 – original table with all data.


Table1 COPY – all analysts are counted for each project and an
average value found.

Project and analyst layout


STEP 7) [MERGE AND CONNECT] Lets merge these queries (tables), based
on the primary key PROJECT NAME. Go to Table1, go to the menu HOME -
merge queries (NOT AS NEW).

i) The top window should have Table1, select PROJECT NAME column with
the mouse. (it’s the primary key).

ii) Underneath, choose table1 COPY from the drop down and again select
PROJECT NAME column - OK.

Leave as LEFT OUTER. Click OK.

NOTE: Check for the green arrow, you will see matching 34-34 rows at the
bottom.
20-Oct-24

Project and analyst layout


STEP 8) [CHOOSE DATA] You will see the whole table in the last column
Table1 COPY, we just need to click on the filter (top right of the column
header) and choose [number of analysts] AND [project value] – click OK.

Rename the 2 new columns to just [number of analyst] and [Project cost].

STEP 9) [FIND COST] Finally, let's add a final column to see which analyst
made the most. Goto ADD COLUMN, CUSTOM COLUMN. Change the
name to VALUE PER ANALYST and add the formula:

=[#"Project Cost (US$)"]/[Number of analysts]

SOS: do this by clicking on the RHS names. Click OK - ensure the green
arrow at the bottom, click ok.

CAREFUL - check the spelling is the same as the variables.

Project and analyst layout


STEP 10) [CHOOSE DATA] On the final column we just made VALUE PER
ANALYST, click on ABC123, change the units to currency.

Go to HOME, close and load.

Goto TABLE 1 and click on column H (value per analysts), go to home


menu and conditional formatting and choose data bars.

You can now see who are the top 2 managers are?
20-Oct-24

Project and analyst layout


STEP 11) [ADD INFORMATION FOR AUTOMATION] We can also add more
information and then click refresh and the data will be analysed.

Click on the ANALYSTS Excel tab and add the following new project.

Project 111 FMCG Akshay Saxena, Manoj Tiwari, Sanjana Kapoor 232,423

Go back to Table 1 Excel tab, then goto DATA - REFRESH ALL.

After the change, you can see top managers!

1 - Akshay Saxena (77474.33)


2 - Manoj Tiwari (77474.33)
3 - Sanjana Kapoor (77474.33)

Project and analyst layout

PIVOT
TABLES
20-Oct-24

Project and analyst layout


STEP 12) [PIVOTS] Close the query window, in excel.

i) Find the Excel tab Table1. Highlight all the data (ctrl + A)
and goto INSERT - PIVOT TABLE - FROM TABLE/RANGE. Leave
everything as is (ensure new worksheet), OK.

ii) Check the box on the RHS, ANALYSTS and VALUE PER
ANALYST. You will now see a list of all of them with their
values.

Project and analyst layout


STEP 13) [RESULTS] Click on the drop down in the analysts
menu (RHS) to select only specific analysts based on projects,
you will see:

Akshay Saxena 321457.0166


Anjali Parmar229775.7667
Manoj Tiwari 265501.4333
Rahul Kumar 140101.8333
Sachin Dev 233363.6
Sanjana Kapoor 417828.35

Grand Total 1,608,028


20-Oct-24

Project and analyst layout

QUESTIONS

Project and analyst layout

Question 1 - How much did Manoj Tiwari make in project 102


only?

32,945.75 (must click on project name and choose only manoj)

Question 2 - How much did Anjali Parmar make in all projects?

229,775.7667 (clear filter on projects and choose Anjali)

Question 3 - How much money did Sachin Dev make in all


projects in Banking?

68,530

You might also like