Power BI-Alex the Analyst Project Hr Analayst

First of all need to undertand, working preference of people…Its about hR, who need analytics by understanding how many people are attending the office , how much of them are leaving in the mid of day? we want to plan team lunch but we need to understand majority people presence on those days

Choose Import from excel, then choose that excel file and click on that file and click transform. this is the data engineering side of power BI

We can do chnaging in power query editor. Here you can see column names are not correct

We need to duplicate this attendance sheet because we want date written seperately and we want to clean the data and we need to have only date column in the template not the other columns. double click and choose “REmove other columns”

Lets say we are selecting the data for one particular sheet, april 2022 sheet nad we can expand the sheet from here ..choose the removed columns sheet from right hand side and then click this sheet data

Select the table and choose use first row as headers then choose remove the top rows,

Now i ma going to transform a data, transpose means chnaing the rows to columns, and its called unpivot…Under unpivot we have three options: unpivot columns, unpivot other columns and unpivot only selected columns

so i will select names and employee code and click on unpivot other columns. Use CTRL+Click

But in attribute column there are dates and other values as well but we dont need those ..we have just selected dates from that column…

Now think of parameter now, parameter is a way of filtering particular date and it can be change. How to get parameters? Go to home, then manage parameters.

We have done transformation step and we need to apply this across all the sheets now we need to apply this by formula.how?By the help of function, you can apply the chnages to other sheets as well

Now we have the data and we need to make some metrics on this file

DAX mesaure to calculate total working days

TotalWorkingdays =

var totaldays = COUNT(‘Final Data’[Value])

Var nonworkdays = CALCULATE(COUNT(‘Final Data’[Value], ‘Final Data’[Value] in {“WO”, “HO”}))

RETURN totaldays-nonworkdays

Now i need to count the work from home count..i am using dax formula by creating new measure

Workfromhome = SWITCH(TRUE)

‘Final Data’[Value] = “WFH”,1,

‘Final Data’[Value]=”HWFH”, 0.5,

0)

now i am adding another mesaure here;

Present days =

Var PresentDays = CALCULATE(COUNT(‘Final Data’[Value]=”p”)

RETURN

PresentDays + WFH[Count]