Example 4.

1: You are provided with the following HR data and Pay details of theemployeesw
in your cormpany:
HR Data:
Employee Name Date of Birth Designation
Emp_D
A001 Ankita Sharma 23-03-1993 Team Lead

A002 Amit Singh 18-09-1986 Supervisor

A003 Asha Verma 02-03-1970 Senior Manager
DO01 Deepak Rai 11-10-1979 Manager
GOO1 Girish Jha 20-06-1969 Senior Manager
 SUMMARIZATION, AND ANALYTICS IN SPREADSHEET 4.9

D A T A

Gauri Sharma 27-07-1987 Assistant Manager

G O 0 2 Madhay Sen 06-11-1992 Team Lead

M O O I
Mahesh Kumar 24-12-1985 Assistant Manager
M O 0 2

Mohan Singh 29-01-1972 Senior Manager

aye MO03
Meena Ojha
16-02-1990 Team Lead

Pari Jain 07-08-1988 Supervisor

queny
MO04

05-07-1990 Assistant Manager

POO1
Priyanka Singh
PO02 Rohan Kumar
17-06-1989 Assistant Manager
09-12-1977 Assistant Manager
ROOI Shiv Pratap Singh
23-06-1990
Team Lead
SO01 Vidya Rathi
16-04-1971 Senior Manager
VOo! Vijay Kumar
VO02
cal is PayDetails:

HRA (%) TDS Rate PE Contribution

Advance Salary Paid DA(%)
Basic Salary 10% 16%
Emp
D 30% 15%
20500
45500
15% 10% 22%
MO01 30%
orizonta 46950
30% 16% 10% 17%
ute difi MO04
48000
10000
22%
A001 30% 17% 10%
15000
49250 15% 10%
ch as adk VO01 40% 17%
58450 15% 11%
a, mant A002 40% 17%
he butin 61250 15% 11%
PO01 40% 22%
sheet. 82470
22% 20% 18%
RO01 40%
ming 85235
22% 20% 10%
PO02
40%
87350 20% 19%
MO02
40% 24%
in the d 15%
S001
88400
24% 25%
Amns, In 40%
89350 25% 17%
GO02 40% 25%
22000 21%
OwerQ D001 92325 27% 30%
50% 17%
ons toziu
M003 105000 27% 30%
50% 15%
V002 110850 27% 30%
eeswon
50% 17%
A003 115880 27% 30%
50%
GO01 121200
transformations.
you are required to: data
Using the Power Query tool, consolidated data by
suitably applying

(a) Merge both data Sources and prepare

payable to all employees.
0) Compute the Net Salary categories of
employees.
to their
Provident
(c) Compute the average net salary of all amount
ofcontribution

andthelowest
(0) Identify theemployees withthe highest
Fund (PF).
 4.10

Solution 1:
(a) To merge both data sources
and transform
and prepare data for
may perfom the following steps: Payroll
Open anew Excelsheet and click on the Data tah
Under the 'Get & Transform Data' group, click on
computaiA,
Workbook to launch the Import Data dialog box. Get Data >From
Select the first data source, namely the workbook
Import. In the Navigator pane, tick the 'Select ffile the HR containing File>Tra
containing the HR data. Click on Load. multiple items' option,
then
Repeat the previous step to import data from
fle containing the Pay details of the second data
employees. source, namely
Click on Get Data >Launch (the we
Power Query Editor to
access
Click on Home > Merge
Queries > Merge Queries as New the imported data.
Select HR Data as the first table to launch the
and Pay Details as the Me
key column in both the tables, second table. Since
of the pane should confirm select this column in both sections. At
a match of all rows.
Click OK. this poinEmp
t, the
IDy
ctI HR Data Powe Query
Tnnstorm Merge
Select tables and maching coumes to
reate amerged table.
L
HR Data

Enslom Na Desetoe
AO01 Ankita [harma 2B1993 Team Lead
AO02 Ami Sinch 18091906 Suoevisoe
As Vema
Deepak Re
02031970 Senior Manae ery Setings
t101979 Mang
2006-1969 Senior Manger EES

operies

MOON $$00 LEDSHES

20500
MOON Source
000 10000 Naigion
49280 Promoted des
15000
S4S0 04 Chnged hype
12 Join Känd
13
ut Oue ( rom frst, maching rom second)
14
15 V The selecion mtches 16 ol 16 rows rom the first table.
1 SCOUMNS, KROWS
RNEWDOMOOSA

Navigate to the Query Settings Pane to rename this query as Payroll, then press
Enter.
Using the Current View slider, scroll to the Pay Details Column and click
on the button
the column header to expand the query. Click on Expand, then OK to
expand the merged
query.
 SUMMARIZATION AND ANALY TICSIN SPREADSHEET
DATA 4.11
Query Editor
Paynol Power
Transfomn
Add Column View
Home

Properties
Data Type te
L AdvancedEdtor
hoose Rernove eep Remove Ue Fira RowmWedes
Refresh
Manage Columns Columns" Ros Rows sple Group
Raplaa Velues
LAgpens Queks
y
Ce Prview
Query
Manage Columns Reduce Rows Sorn
Gortne Fles Mansed
Trnsdorm Pararneta
Contine Paramet
X Table.Nested)oin(uHA Data", ("np _10), Pay Detalls", (tnp _10),
"y
HR Data Employee Neme Dteof Birth Me Deslgnatlon
1 Ankita Sharrma yDatals
Pay Detoils 23-03-1993 T
Emp. Amit Singh 18-09-1986 S
Poyrol!
Asha Verma 02-03-1970 O bpand O Aggregate
AD02 | Deepak Rai 11-10-1979M selecd Al Columns)
AO03 iGirish Jha 20-06-1969 Emp D
DOO1 6 Gauri Sharrma 27-07-1987 Basic Salary
GOOL
7 Madhav Sen 06-11-1992T Advance Salary Paid
GO02 Mahesh Kumar DA S)
24-12-1985Ad

9 MO02
Mohan Singh 29-01-1972 HRA %)
10 Meena Otha 16-02-1999oT TDS Rate
t oMO03

MO04 11 Pari sain 07-08-1988S

ZPF Contribution
2 POO1 12 Priyanka Singh 05-07-1990 A VUse oDginalcolumn name as prefix
PO02 Roban Kumar 1706-1989 A
RO01 Shiy Pratap Sirgh 09-12-1977A Cancel
14
h6vOOL
15
17v02
Remove unneceSsary columns. This may be achieved by executing aright click on acolumn
header andI selecting Remove Column. For example, the analyst may consider removing the
deemed
Date of Birth column, the Pay Details.Emp_ID column and any other extra column
computation.
unnecessary for the purpose of payroll
headers.
Bename columns suitably; this may be achieved by double clicking column
Output sheet in the Excel wokbook.
Click on Home > Close & Load to view the
Queries & Connec.
8ask SYlary Advance Salary Pald DA- HRA-TDS RatePE Contribution
Desgnation 20000030.16.01o17 OuciesCornecdoes
Name
Emp 1D Employee Team Lead
48000
a16
Anlita Sharma 45S00 20500 0.3 .15
Madhav Sen
Team tead 040.170.15 0.1
MO01 Supervisor 58AS0
Amit Sing 0.3 HROata
AO02 Team lead 46950 0.3 0.15
Meena h a 62 16 rovs Soaded
MOOA Senior Manager 15880 0.25 0.25 a17
Asha Verma 22000 0.4
AO03
Deepak Rai Manager 1S000 0.3 0.17 0. 0.22 Pay Detals
DOO1 Team Lead
49250 16 rows koaded
idya Rathl 121200
Seniar Manager 04 0.24 0.25 0.15
Girish sha 89350
GOO1 Assistant Manager 0.17 015
Gauri Sharma
10 GO02 Supervisor 61250 0.22 0.15 0.11
Pari lain 82470
Rohan Kumar Assistant Manager 0.4 0.22
87350
MO02 Mahesh Kumar Assístant Manager
Assistant M a n a g e r 8 5 2 3 5
04 0.22 02
0,23
0.3
FO02 Priyanka Singh 0.5 0.27 0.15
Mohan Singh Senior Manager 105000 a4 o,24
MO03 8S400 0.3 a.17,
s001 Shiv PratapSingh Assistant Manager 05 0.27
Senior Manager 110850
Vijay Kumar

Payrol!Sheet

the following steps may be performed:

Salary payable to all employees,
(0) T0 compute the Net clicking on Query tab, then on Edit.
Editor by
Launch the Power Query Column.
Column tab, then click on Custom appropriate column from
Navigate to Add selecting the
Salary'. By Custom
the New Column Name as Gross enter the following formula in the
Enter clicking on insert, Salary])+([HRAJ*Basic Salary]).
column pane and
ne Available Salary]+([DAJ Basic double clicking on the
section: = [Basic formula by
column formula inserted in the
specific column may be
Alternatively, the Available column pane.
coBumn name appearing in the
 BUMMAIZATION. AND ANALYTICS IN
SPREADSHEET
4.12
generatethe
new column Gross Salary'. 4.13
Click OKto Column. Enter the New Column 4A
CUhenpone
Custom
Column>
column formula scction: Nanme
Rename

Click on Add Custom

Baas'sic ouc
formula inthe
A o nh o s

following
enterthe Group By
Salang
R a t c + [ P FContribution).

'Deductiong Soecty the columns lo group by nd one oft more otouts,

the new column
Click OK to generate the'
Custom Column, Enter Ncw Column
Cick on Add Column> inthe Custom Name:
formula column formula
and enter the
(Deductions].
following
secion: =(Gros Degnstion

Add groaping
Salary'
Click OK to generate the new column 'Net Neow colmn name Operstion
click on
Select this column, navigate to the Transform tab, Data type and Average Salary Average
Currency. 10 AT AM Rows
Advancee Salary
Since the computation does notrequirethe column, right click 11
Add aggregation

header, then click onRemove column. on the 13

Click on Home> Close &Loadto generate update the Output sheet inthe ) 14

Select the Basic Salary, Gross Salary, Deductions andINet Salary columns, wot Excel 15

the Home tab and specify the number format as Currency. Similarly, select then naviga the query containing the: average net salary of
TDS Rate and PF Contribution columns and specify the format as the generate

tothe Home tab, then click on Close & Load to generate thedesignations. all
the final output. t Percentage DAR Click
OKto
Navigate
wworkbook.
Average Salary

sheet
in the Excel
E F Output Average Salary column, navigate to the Home tab in the Excel
Designaton
TesnLead
ClBasie Sslary DA- HRA ToS Rate -PF Contributlon Gross Salary - Deductlons - Net Salary -
R48,000.00 30% 16% 10% 17% 70,080.00 12960.00 57,120.00 Oueries &Conniectione Selectthe
format to Currency to produce the final output. workbook and
Team Lead (45500.00 30% 15 16% 65,975.00 11830.00 54,145.00 Ouerfes Connectons the
number
Supervisor 58AS0.00 40% 17X 15% 10% 91,766.50 14,612,s0 77,154.00 4queries
change

G
Team leed 46950.00 30% 15 10% 229% T68,077.50 15,024.00 53.053.50 B
J6 Senior Manager R115,880.00 S0%
92,325.00 40%
27%
252%
30%
25%
15% 2,0S, 107.60 "52,146.00 R152,961.60
17% 152,336.25 38,776.50 1,13,559.75
HR Data A
Average Salary Queries &Connections
Manager 16(ows loaded. S5,239.0o
Team Lead R49250.00 30% 17% 10% 22% $72,397.50 15,760.00 56,637.50 Deslgnaton
Queries Conecions
Senior Manager 121200.00 50% 27% 30X 17% 2,14524.00 56,964.00 R1,57,560.00 Pay Detals 2T e a mLead
K78,695.75

o AssstantManager 89350.00 40% 24% 25X 15% 1A6,534.00 16 (ows loaded K146,731.65 4queres
R35,740.00 K1,10,794.00, Supervisor

1Supervisor (6L250.00 40% 17% 15% 11% 96,162.50 15,925.00 80,237.50 R4,13,559.75
Payrol
Senior
Manager

Assistant Manager 82,470.00 40% 22% 15% 11% K133,601.40 HR Data

21442.20 1,12,159.20 16rowsloaded K1,10,889,32
3Assista Manager t87,350.00 40% 22% 20% 10% 141,507.00 26,20S.00 1,15,302.00 Manager
TAssistantM a n a g e r 161ows loaded.
Assistant Manaper85,235.00 40% 22% 20% 18% 138,080.70 Average Salary
"32,389.30 1,05,691.40
Senlor Manager L05.000.00 SO% 27% 30% 21% 185,850.00 53,550.00 1,32300.00 5rows loaded. DPay Dtals
6Assstant Manager 88,400.00 40% 24% 20% 19% 1A4976.00 34476.00R110500.00 16 rows loaded
7Senior Manager 110850.00 So% 27% 30% 17% 196,204.50 S2,099.50 1A4,105.00
Paol
16 rovs loaded.
(c) To compute the average net salary of all categories of employees, the
analyst may perform he B Average S3lay
following steps: 510Ws losded
13
Right click on the 'Payroll' query from the Query Pane, then select
Duplicate. Rename tit
new query as Average Salary. employees with the highest and the lowest amounts of PF contribution, the
identify the
Navigate to the Transform tab. Click on Group By to launch the Group be performed:
By pane. following steps may
Select the grouping basis as Advanced. Launch the Power
Query Editor.
Select Designation from the drop-down section.
.
executing a right click on the query name and clicking on
. Duplicate the Payroll query by
In the first aggregation section, specify the New column name as
Average Salary. From the Duplicate.
details.
Operation dropdown, select Average. From the Column dropdown, select Net Salary. query as PF contribution
Click on Add aggregation to add a second aggregation section. Type " Rename this new column as PF contribution amount.
Al" as the New >Custom Column. Rename the
Click on Add column to generate the new column.
column name.From the Operation dropdown, select AllRows.
formula: [Basic Salary]*[PF Contribution]. Click OK
Enter the
 SUMMARIEATION AND ANALYTICS IN
SPREADSHEET
4,12

Click OKto
gencrate the new
>Custom
column
Column
Gross Salary'.
Enter the New Column
BUSINES p 4 A
Cpone
Dets lype h

ortrd Data Type

n.
Move
4.13

formula section: Name

Column column Renarne

Click on Add formulain the

Custom formet
enterthe following
Rate]+[PF Contribution).

Deductiong
asDeuet
=[Basic Group By
Speitytthe coumns o group by and one or more outout,
the new column
Click OK to generate m
O
Basic O Advand

Column >
Custom Column. Enter the New Column 1 A

Click on Add
.
and enter the
following formula in the Custom column formula Name as"Ded 2

Designstion

[Deductions].

Click OK togenerate the new column

'Net Salary' section:=(Uros |Add grouping

Now comn ame

Select this column, navigate to

the Transform tab, click on Data Operation

Currency.
type anu |Average Salary Average
NAsday
Since the computation does not requirethe Advance Salary column, right click Cags Al
d ggregution
| AlRows

header. then click on Remove column, 3

12

Click on Home> Close &Load to generate update the Output sheet in the Excel 14

15

Select the Basic Salary, Gross Salary, Deductions and I Net Salary
the Home tab and specifythe number format as Currency. Similarly, columns, then
naDA,vigaA
IoUNS16ROH

TDS Rate and PF Contribution columns and specify the select the Click OK to generatethe query containing the average net salary of all designations.
format as
the final output. Percentage to prod to the
Home tab, then click on Close & Load to
workbook. generate the Average Salary
in the Excel
Navigate

G sheet
K
-RAsle Salbry DA HRA
Designation TDSRate PF Contributlon GrossSalaryDeductlons Net 5alary -
Queries &Connecioe
Output
Average Salary column, navigate to the Home tab in the
Currency to produce the final output. Excel workbook and
2Team erd R48,000.00 30% 16% 10% 17% 70,080.00 12960.00 S7,120.00 Selectthe
itean lead 45500.00 30% 15% 10% 16% 65,975.00 11830.00 S4,145.00 Quertes Connectong number format
to
Supervisor 584S0.00 40% 15% 10% 91,766.50 14,612,50 7m,154.00 the
change
Team Lead 46,950.00 30% 15% 10% 4queries
K115880.00 S0X 27%
222 68,077.50 15,02A.00 53,053.50 D G
k Seníor Manager 15% 2,05,107.60 RS2,146.00 K152,961.60
|7 Manager t92.325.00 40% 25% 25% 17% 152,336.25 38,776.50 1,13,559.75
HR Data
16rows loded.
A -Average Salary Queries &Connections
TeamLead 49,250.00 30% 17% 10% 22% K55,239.00
72,397.50 15,760.00 56,637.50 Deslgnatlon
Senior Manager "121,200.00 50% 27% 30N 17% 2,14,524.00 R56,964.00
1,57,560.00 u Pay Detals 2TeamLead 78,695.75 Queses Cornecions
o Assistant Manager (89,350.00 40% 24% 25% 15% 1A6,534.00 R35,740.00 16 rows loaded.
Supervisor (61,250.00 40% 17% 15% 11% 96,162.50
1,10,794.00, Supervlsor K146,731.65 4queses
ll2Assistant Manager t82470.00 40% 22% 15%
15,925.00 80,237.50 DPayoh SenlorM a n a g e r 1,13,559.75
11% K133,601.40
K21,442.20
l3Assistant Manager 87,350.00 40% 229% 20% 10% 141,507.00 26,205.00 1,12,159.20 16 1oWs loaded 1,10,889.32 R
Data
I4 Asslstant Manaer &5235.00 40% 229% 20% 1,15,302.00 sMansger
18% 138,080.70 32,389.30
K1,05,691.40. WAverage Salary
6 Ass/stantManager
16ros loaded
siSenior Manager L,05,000.00 S0% 27 30%
6Assistant Manager R88,400.00 40% 249 21% 1,85,850.00 53,550.00 1,32,300.00 S rows loaded
7Senior Manager
20% 19% 14,976.00 34476.00 1,10 500.00 Pay Detais
110850.00 S0% 27% 30% 17% 196,204.50 R52,099.50R1,44,105.00 16 rows loaded

Payrcl
(c) To compute the average net salary of all
following steps:
Right click on the 'Payroll' query from the Query
new query as Average Salary.
Navigate to the Transform tab. Click on Group By
categories of employees, the analyst may perforn te
Pane, then select Duplicate. Rename ti: ale
15

(d) To
identify the employees
withthe
16 rows loded

DAvercge Sslary
51owsloded

highest and the lowest amounts of PF contribution, the

to launch the Group By pane. following steps may be performed:
Select the grouping basis as Advanced.
Editor.
Select Designation from the drop-down section. . Launch the Power Query
Duplicate the Payroll query by executing a right click on the query
name and clicking on
In the first aggregation section, specify
the
Operation dropdown, select Average. FromNew column name as Average Salary. From
the Column dropdown, select Net Salar. the
Duplicate.
contribution details.
Click on Add aggregation to add a second " Rename this new query as PF
column name. From the Operation dropdown,aggregation section. Type All' as the New
select All Rows. Click on Add column > Custom Columnn.
Rename the column as PF contribution amount.
new column.
Contribution]. Click OK to generate the
Enter the formula: (Basic Salary*PF
 suMMARZATION AWND
ANALYTICS IN SPREADSHEET

lhc
T r a n s f o r

Navigate to the Number

tok
n
BUSINESS Ah cleaning
and Itransforming
r e l i a b l e ,
Power Query cnsurevdata,
lcading to more accurate insights that the data used for analys1s is
4.14 column,
clhCkon Thic will gencrate the maximum
yaluc wilhin accurate
By
and
can be integrated with reporting tools to automate the
on business performance. generation of reperts.
M a x i m u m ,

this
Select > Query
updates
he
Statistics

timely
clickon
2205S0).

Applicd Steps to return to the providing.

P o w e r

is Security

(inthis
case,it
step
under Goernance and
tlhe
previous
can
be used to create centralized data pipelincs, ensuring consistency
Click on the organization. and
. Navigatetothe PF Contribution Amount column. Click on the header dropdown ande Dgta
standardization
Q u e r y

across

Quer integrateswith data security measures to protect sensitive

Click on Insert to launch the Filter
P o w e r

Rows data and control

Number
on
Fillers > Equals.
Specify the
frst
condition.
diaoflogbox,
equals,
filtercase, 22050)in tthe corresponding value section
condition as
PowerQuery

data
sources.

tool for anyone and everyone who

maximunn asthe
value (in this the coNd intends to work
essential

to
SelectBasic is an with data
access

Tts powerful data connectivity,

BI.
now reflect the name
and other details of the nbrief
Power spreadshetor
Ouery
PoOwer
transformati¡n
an indispensable toolfor data cleaning, capabilities, and
Click OK. The
query
maximumn P F contribution.
shall
employee wilh Sutomation
f e a t u r e s
r e n d e r
itag
Query tool,
the analyst
integration,
can prepare data efiiciently from
and analysis.
multiple sources
reports and. analysis are based on
E x c e l

Power
Byleveraging
a the

that their accurate, up-to-date information.

ensure

anstom
A d C o l

Data ly Any D Herge Queries therebr

Uge First Sow as Hesders T Append Queries Hew Soute.
enables users to create sophisticated data models for
1 2 3 P o r e r P i v o t

Aotes
X Manage Data so
ilConnbine Files and
KAO ßemove
Parameter.Souroe

Excel add-in that

settings
perforning
AdaondKAY

Rows Bony
Transform Combine Parameters Data Sources
Ates
cots" (hm
Redce Rows
Sort
NewOun an
With Power
Pivot, an analyst can import large volumes of data from
Mane CoAumns
Custom3", cach [PF
Contrlbutlon Amount] 2)0s0) isanalysis various
between tables, and perform complex
calculations, all within an Excel
Table.selectRONs(e"Added
. Pivot

MDeslg-13B 1 2 DA. Power

Pivot enhances Excel's capabilities, thereby allowing for

data relationships
tme J0 Employ 185950 53550 132.30000 i
Mohsn Singh
'Senior Menager i 105000 0.27 powertul
create
Power
software.
advanced data
SOurces, Further, require specialized
CAeye Salary
workbook. w o u l

l otherwise
d

Cotition ana that

Pivot
a n a l y s i s ofPoower

facilitatesimport of data from various sources, whích include

Benefits of Power Query Power pivot
F e a t u r e s

3. Distinct Main. Import:

data providerS. and online
(a) Comprehensive Data Integration Data
s p r e a d s h e e t s ,

integrate data from various s . facilitatesthe creation of relationships among different tables, just as
Power Query allows the analyst to casily connect to and Creation: It
d a t a b a s e s ,

sources,
including Excel workbooks, CSV:fles, databases (SQL Server, Oracle, etc.), and lonline data Model
relational database.
Data in a
lists, 2. : Power Pivot uses DAX formulas for advanced calculations
sources such as web APls and SharePoint (DAX):1
a c h i e v e d

itis Expressions

Regardless of the data's original format or structure, Power Query can transform and resh
Analysis

Data
3.
a n d
d a t a m a n i p u l a t i o n .

it into a consistent format, making it easier to analyze and visualize. Compression:

It can handle large datasets efñciently by asystem ofbuilt-in memory
(b) Independent Data Workflows 4
Memory

models can be seamlessly integrated with Power BI

compression.

Power Query empowers users to independently access, clean, and transform data withet with Power BI: Power pivot
interrupting their workflow. I n t e g r a t i o n

visualization andIreporting.
5. enhanced

It also allows users to create and share reusable data pipelines, collaborate with others on for
data projects, and automate routine data tasks. P o w e rP i v o t E n v i r o n m e n t

Pivot tab is created in the Excel workbook.

(c) Data Discovery and Exploration has been enabled, the Power
thePower r Pivot Add-in Pivot tab have been explained below:
The intuitive interface allows for quick experimentation with different data transfomations Once
of the Power
The key
components

Model group, the Manage option launches

the Power Pivot window
and analyses, thereby enabling the analyst to quickly discover insights and patterns within Under the Data
functions and tools for managing
important
the data. 1. Manage:
provides the user with access to a variety of window include:
that Pivot
" It provides tools to characterise data, identify anomalies, and assess data quality, thereby options available in the Power
the data model. The tabs and working with the data model, such as:
ensuring data accuracy and reliability before further analysis. several essential tools for
(d) Enhanced Business Monitoring and Analysis a. Home Tab: It provides within this group, the user may import
Using the options provided or
" Power Query can be used to integrate real-time data feeds from various sources, allowing () Get External Data: sources such as Databases (SQL, Access, Analysis Services
for timely, real-time monitoring of key metrics and trends. data from a number of
 No 5.7 bxNo. No. S8 fNo. 59

SUMMARIZATNON
418

Click on
Jaunchanthe
Chart
Styles to

lnsertPivotChart,
selccttthe
navigateto
Style and Color
the
of the
PivoIChart,
PlvotChart Analyze
tab. sti
Sliccr dialog box. Check the boxes against the requircd
BUSINES AND ANALYTICS IN

Tables
270
SPREADSHEET
6575 4.19
active MA
Vih 455
April
Tables
1500
350
Apil East
Tables
1740
OK,
Power Pivot Weat
Elective Use of
Tables
475
4590 Pebruary South
Guidelines for Pivot ae 1275 Match
potential of Power tool for data Chairs
10300 Norh
To efictively leverage
the
outlined below: modeling and Chairs
800
1700 January
may follow the
(a) Import
guidelines

and Clean
Data: To ensure data integrity, it is essential to
systematically before importing it. removs
Dnahysis, Chairs

Chairs
740
1300
9200
8500
June
March North
South
data and organize itt March
the
and linkimported data
b) Optimize Data Model: ldentify eliminate redundant. tables using 100nssiC provided with the profit margin sheet and the
e excel
workbooks, product name and West
category mapping
establish optimized relationships, data and enhanceprimary 1
separate
alsO

gre in
model isloanation
Vou Product Name
Hierarchies: To. streamline data analysis, create and implement Profit Margin (%of
(c) Use
model (e.g. Department > Team >Employee) to be able to drill down orhierarchies periort P r o d u cD
t
Dishwasher Sales)
intuitive deeperup throughwithin di gb Cooking Stove 12,.25%
levels of data granularity, and thereby facilitate a more
A001

and Mobile Devices 11.75%

exploratio
A 0 0 2

data,
BO01

Laptops 9.15%
(d) Under the Basics
of DAX: Develop a sound grasp of DAX (Data
Analvsis BO02
Tables 14.15%
fully leverage its analytical capabilities, such as create dynamic measures, EApres Chairs 15.50%
and perform complex data transformations. calculated. CO01

CO02 8.25%
(e) Refresh data: Configure data connections to enable automatic refresh of data Product Name
intervals to ensure that the data remains current and accurate, sources at peig ProductID
Dishwasher
Product Category
Tet us understand how to work with Power Pivot with the help of an example. A001 Apliances
Cooking Stove
Example 4.2: You are provided with the consolidated sales data ofAlpha Ltd. in an A002

Mobile Devices
Appliances
excel workbo. B001
Electronics
Product ID Product Name Qty Sold Price per unit Month Laptops Electronics
A001 Dishwasher 250 14500
February
Reglon BO02
Tables
Funiture
A001 Dishwasher 540 12900
East CO0!
Chairs
February Fumiture
A001 Dishwasher 800
West CO02
to:
13500 required
June
South Pivot, you are
A001 Dishwasher Power.

Pivot table using the Product Dfield as the key column.

520 14750 March Using
A002 North a
Power

Cooking Stove 690 Create

Profit for all the sales data.
7250 May North la)
the Total Sales and Total
A002 Cooking Stove 1200 Compute

Average Sales and Average Profit of Appliances the

8050 June (6) ast and West regions.
A002
West the
Cooking Stove 770 9990 Compute

A002
March South ()
a Pivot Chart to displaythe Total Proft of the four regions.
Cooking Stove 850 6900 Prepare
Chart with Month and Region slicers tto display the Sales (in terms of quantities
May East ()
B001 Mobile Devices 490 45000 Prepare a Pivot
April West
B001 Mobile Devices 690 36500 sold) ofall products.
January South the profitability of all products.
B0O1 Mobile Devices 1270 21900 June East
A Create a KPIvisual to track
B001 Mobile Devices 1100 22900 March North Solution:
BO02
Laptops may perform the following steps:
la) To create a PowerPivot table, the analyst
220 33500 April West
B002
Laptops 370 28900 January North Open anew Excel workbook. Add the Power Pivot Add-in (ifnot already added) by following
B002
Laptops 460 36900 March East the steps outlined in the previous section.
B002 windoW. Pivot
Laptops 375 click on Manage to launch the Power
Navigate to the Power Pivot tab and
25700 May South
 euMMARIZATIONAND ANALYTICS IN SPREADSHEET

418

Click on
Chart
Styles to
sclcctthe, Style

navigate tothe
and Color of the
PivoLChart.
PivotChart Analyze tab,
BUSIHES Tables
270
6575 4.19
PivotChart,
Check the
With an active dialog box. boxes against the Cick
requircd. Tables
455
1500 April
Jaunch the
OK.
Insert Sliccr

Pivot
on\nser Tables

Tables
350
475
1740
4590
April
February
East
West

Effective Use
ofPower 1275 March Soth
Guidelines for Power Pivot as tool for Chairs
10300 North
the potential of data modeling and 800
1700 Janiary East
effèctively leverage outlined below:
Chairs

To C002
740 June
mayfollow the
guidelines

Clean Data: To ensure data integrity, it is essential to analyais, Chairs

1300
8500
9200
March
North
South
() Import and
organize it systematically
before importing it. remove C02
Chairs
March
the data and imported data tables
inconsist. provided with the profit margin sheet and the product name and West
Data Model: Identify and link category
workbooks.
Äata and using primary kev mapoing
excel
Optimize redundant also

(b) optimized relationships, eliminate

s e p a r a t e

establish enhance inormation,

sre in Product Name
Profit Margin (%of
model : perf
(c) Use Hierarchies: To streamline data analysis. create and implement hierarchies Vo
P r o d u cI
tD
Dishwasher Sales)
drill
model (e.g. Department >Team > Employee) to be able to down Cooking Stove 12,25%
facilitate a more intuitive and or up through
A 0 0 1

levels of data granularity, and thereby 1175%

deeper A002 Mobile Devices
data, exp\oration g BOO1
Laptops
9.15%
14.15%
() Under the Basics of DAX: Develop a sound grasp of DAX (Data Analysis BO02
Tables
fully leverage its analytical capabilities, such as create dynamic
and perform complex data transformations.
Expres ian
measures, calculatcd. N
CO01

COO2
Chairs
15.50%
8.25%
Product Name
(e) Refresh data: Configure data connections to enable automatic refresh of data s Product Category
intervals to ensure that the data remains current and accurate. Sources a peiok Product D
Dishwasher
Appliances
Let us understand how to work with Power Pivot with the help of an example.
A001

Cooking Stove
Appliances
Mobile Devices
Example 4.2: You are provided with the consolidated sales data ofAlpha Ltd. in an excel
A002

Electronics
Product ID Product Name Qty Sold Price per unit Month
wotkbos B001
Laptops Electronics
A001 Dishwasher 250 14500 Region BO02
Tables Fumiture
February East C001
Chairs
A001 Dishwasher 540 12900 Furniture
February West CO02
A001 Dishwasher 800 13500 June Pivot, you are required to:
South
table using the Product ID field as the key column.
Power.

A001 Dishwasher 520 14750 March Using

Power Pivot
North Create a
A002 Cooking Stove 690 and Total Profit for allthe sales data.
the Total Sales
7250 May (a)
North
Sales and Average Profit of Appliancesthe East and West regions.
Compute
A002 Cooking Stove 1200 8050 June West (6)
the, Average
Compute

Chart to display the Total Profit of the four regions.

A002 Cooking Stove 770 9990 March (c)
South a Pivot
wwitth Month and Region slicersto display the Sales (interms of quantities
Prepare
A002 Cooking Stove 850 6900 May East ()
Pivot Chart
B001 Mobile Devices 490 Prepare a
45000 April West (e) products.
sold) ofall
the profitability of all products.
B001 Mobile Devices 690 36500 January South
B001 Mobile Devices 1270 21900 June
(n Create a KPIvisual totrack
East
B001 Mobile Devices 1100 22900 March North Solution:
analyst may perform the following steps:
a) To create aPowerPivot table, the
BO02 Laptops 220 33500 April West
added) by following
BO02 the Power Pivot Add-in (ifnot already
Laptops 370 28900 January North Open anew Excel workbook. Add
B002 previous section.
Laptops 460 36900 March East the steps outlined in the Manage to launch the Power Pivot
window.
B002
Laptops 375 25700 May South Navigate to the Power Pivot
tab and click on
 420 Sources > Excel file> Next
Other.
SUMMARIEATION. AND ANALYTICS IN SPREADSHEET
tab>From
4.21
Chek on
Home
which the
data is to be
imported. Check he andTotal Profit columns Sales
optionunderthe Formatting group of the Homeby tabclicking
of the onPower
T o t a l .

the Excel
filefrom Click on Next. From the Preview box the the Apply
Pivot window.
Curreney Format
section,
o 4 T A

the Excel file, then check\abele

headers" Fornat

row as
column
therelevant
tables in
Total Sales
and Total Profit of all the
Soure Tableto
include product categories, the following steps
imported.dick on by
from which data he
Excel files is to be Topresent
t h e

procedurefòr all bepertomed: tab and click on may

window, namely SalesData,
Suitaby\nthtea
this Power Pivot Pivot
imported intothe Manage to PivofTablethe dialog
Power
the
Onthe Hometab. click on PivotTableetto launch the Create launch
productMin Power b¡x,
sheets e) to

ProfitMargin.
Navigate
PivotSelect New
window.
Iothe Home tab of the Power Pivot window. click on Diagram View click on OK. Rename this
. then
PivotTable output sheet as
SalesDataAnalysis.
Worksheet,
data model. Fields section, expand the SalesData
(including headers) in the
PivotTable

SalesData table. drag and

source
it. Select the Product_Name, Region, Total by clicking on the arrow
Cick onthe Product ID field ofthe to the Pr navigate In
the beefore
the ProductMapping source, Sales and Total Proft
of the ProductMapping table to create alink between the two , appearing
then select Product fhelds.
tables. Repeat t
expand

ID field of SalesDatatable with that of the Similarly,

the Product_Category field under

Filters area. Category field.
to link the Product tis ProfitMargin table.,proca and
place

ofthe PivotTable Fields, click

Power Prvot for Excel- Sales Report of Alpha Ltd.xlsx
Drag Values area on Sum of
Total Sales
for the>Sum
Settings. Select Average and click OK. Repeat the same procedure
Undert h e
Valueof Field
Total
Home Design Advanced
value
field to generate
the. Average Profit values.
Rste Append Data Type: Proft
click on the Product Category
Tick the checkbox next dropdown. Click on the + sign next to
PivotTable.

Paste Replace Format:

the items. to Multiple items. Deselect
From From Data From Other Eisting Refresh PivotTable SortZ to A Inthe
Electronics
expand
faste
a Copy Database Service Sources Connections
Alto then click OK.
Furniture,
Get External Data
dpboard Formating
Sort and Filter Fromthe Row Labels, flter the values for East and West to present the Average Sales and
and

Average
for Appliances in these two regions.
Profits

alesData
ProfitMargin E
PivotTable Felds
B
Product |D D Product ID
Product Category
Appliances
Acn A

Profit
Average of Total Sales Average of Total
Product Name Ooose ldds to add to °po.
Product Name R o wl a b e l s
47,45,000.00 5,66,600.00
Qty Sold A Profit Margin (% of 83,.13.000.00 9,94,192,50

Price per unit

Sales) East

West

Month
Reglon Drag flcds beton bo
ProductMapping
TRers
Product |D Produa Ctagory
1
Product Name Rws
Recioe
Product_Category
15
SolsDataAnalysis Sheet1

(b) To compute the Total Sales and Total Profit PivotChart displayingthe Total Profits of all four regions, theanalyst may perform
for Toprepare a
all the sales data, the analyst may perfom the (d) the
following steps: following steps:
Click on Data View. Navigate to the SalesData Navigate to the Home tab ofthe Power Pivot window, then click on PivotTable> PivotChart
sheet. Click on the Add Column heade,
then navigate to the function bar. By selecting the appropriate to launch the Create PivotChart dialog box. Select either New Worksheet or Existing
sheet, enter the function: -SalesData[Qty column headers in SalesData Worksheet as the output table destination, then click on OK.
Soldj*SalesData(Price
Rename this Calculated Column 1as Total Sales". per unit]. Press Enter. Expand the data sources by clicking on the arrows to the left of
thedata source table names
Click on the Add Column header, then navigate in the PivotChart Fields.
to the function bar. By selecting the under the Values area, and place
appropriate column headers from the SalesData and ProfitMargin
sheets, enter the function: " Drag and place the Total Profits field of the SalesData table
-SalesData[Total Sales]*ProfitMargin(Sum of Profit Margin (% of Sales)]. Press the Region field under the Axis (Categories) area.
Rename this Calculated Column as Total Enter.
Profit. Format the Pivot Chart using the Chart Elements and Chart Style options.
 4.23
Measures > New Measure. In the Measure dialog
K M
the following
formula in the formula section:bOx, input the
measure narne
PivotChanFlelds
and
on
TotalProfit
otick
=sumx(SalesData.sSales Data[Sum of Total 1Profit])
ear 2023
for thbe Chone validatethe formula.
Itel Profis as to Sclect the Category as
Check
Formula
Currency. then click
(E91 on
Product_Name field under Rows and the newly
the
created
TotalProfit field and chck TotalProfit
Click

place

Values. Click
on the dropdown of the
on Value
onOK.

and
Drag
u n d e r

onthe
Click "Show Values: as" tab, and select %of Grand Total. Click
measure
S e t t i n g s .
values as percentages of Total Cost (Overall).
TotalProfit
on
the
Field d i s p l a y

tab and click on KPÍs> New KPI. Select the TotalProfit

Power Pivot
Wes

As Categore) to the, ffeld. Select Absolute value as the measure

Relers
Ferion
OK
to target value. And enter the Target Profit
sliders
Navigate
base

Drag the to define the KPI thresholds. In this case, the lower
Sheet1 NDefer yot Updele Sndlon the
KPI
85,00,000)
whereas the upper bound is
oChar2
AeChart
SalesDalanalysis
as (say, at17,00,000, set at 39,00,000. Select an lcon
(e) To prepare a Pivot Chart with a slicer to display the Sales (in terms of quantities value
is
set OK
bound
click
on
following steps:
products, the analyst may perform the
then Indicator
(KP)
Style,
Pivot window. then. click on
Perfornnance

Key
tab of the Power
Navigateto the Home
to launch the Create PivotChart dialog box. Select either New PivotTable> Pivso KPI base
hold
aue)
TolaProft

Worksheet or Ei KPI Stattrs

Worksheet as the output table destination, then click on OK Define target value:

Expand the data sources by clicking on the arrows to the left of the data O M a z s u r e :

source table O
Absdle
value
8500000

in the PivotChart Fields. Defino status thresholds

Drag and place the Qty Sold field of the SalesDatatable under the Values
the Product Name field under the Axis (Categories) area. area, and npa 170000 3900000

Format the Pivot Chart using the Chart Elements and Chart Style options Taget

Click on the PivotChart, then navigate to the PivotChart Analyze tab. Click
on
Slicer. Select Month and Region, then click on OK to insert the slicers for these f Inse Select ioon style.

D EFG H KL M
N
PivotChart Fields
Month Actve AR
Total products sold in the year 2023
Choose felds to add to report
a Desaipbons

2510 February Search

anuary
fune salesData
click on the newly added TotalProfit KPI measure and select the
2110

1550 Product Narme Fields Pane.

1425
Ma
Inthe
PivotTable
output as depicted in the image
below.
Regon
Dra fiels betveen areas bela.
Coal and Status fields to display the
THllers PivotTable Fields
Stove
Dshwoser Lapigos yuile
Device:
Tabfes East Artve AN
Praducts Noth TotsiProfft Status
ToalProfft Gosl Choce l to add to eport
Ads Categornes) 1Valet -iTotIPróle

South RowLbo
K 30,97,256 R85,00000
W , Product Name Sum of Qty Sok Chalrs R33,15,827 85,O0,00
Cooking S t a v e
R35,59,973 85,
MvotChrt2 PvorChart SalesDataAnalysis Sheet1 sher
Dishwash
ODefer Lyout Update 63,21442
K97,73,205
REoo.000 Dsvelue (TotalProfk)
Mobile Devices K8s00,000 oal
() To create a KPI visual of profitability of all products, the analyst Tables
K15,61,935. 8S.O0000

steps:
may perform the following GrandTotal
R2,76,29,637
Drag fields betw ary belrw

Navigate to the Power Pivot tab and click on Manage to launch the Power Pivot
window.
" On the Home tab, click on PivotTable to launch the Create ROw

New PivotTable dialog box. Select P r o d s t Name

Worksheet, then click on OK. Rename this PivotTable output sheet as KPI
Since KPIs in Power Pivot only work with measures, navigate to
Tracker.
the Power Pivot tab and
 USNES
Conchson

is an
invalabie
toolfor tavanced data analysis and
Pvot its powertül
h
Power
briel,
outlinecdsteps
andlevernging
eatures, analysts
ollowigthe compler
data models, periom intricate
create
lage
dhtasets,
empowers
Ihis
USGrS to deeper insights, thereby ma
uncover

viuslizations.
inteligence anddata-divenn decision-making,
for busines
lool
Aental
View)
Bl(ormerlyPower
424 Power enables
data
technology
visualization that
Power Viewis a
mative dashboards and reports. Power View was Drevin