How to automatically generate employee daily attendance report using Power BI by getting data from HR.MY app.

Posted by Monirul on

Steps to generate the daily attendance report for our company Quality Assurance team members in Power BI and we will collect the data in excel format from HR.MY app. We can do it by any source of excel data. 

Required Tools:

  • Power BI
  • Data Files
  • SharePoint / OneDrive login credentials
  • ChatGPT / Google Search

Open Power BI Desktop App:


Open a blank report:

Click Get Data and Click More


 Select Folder and Select Connect:

 

Browse the folder location where you have saved your data:

I have created One Folder inside at my computer Downloads folder named: Attendance

And inside the Attendance folder I have create another three sub folders, Daily Attendance, Employee, Leave


The reason I have created those files to keep the data files separate.


 

First, I will load the Employee data:

Click Ok and again OK


I will use the template data which I have downloaded from HR.MY app.

Immediately you will see the below window, and you need to click Combine & Transform Data

From the next po up window, in the left select Data and click OK.

Finally, you will have this window:

You need to modify, clean, and transform the data on the red marked box named: Transform Sample File


This step is very important, otherwise the data will not sync when new data files will be kept on the specific folders.

Now I am going to clean and transform the data at Transform Sample File:

At home tab Reduce Rows Pan > Select - Remove Rows > Click – Remove Top Rows – Enter: 7 and click okay



Now promote the headers:

On the Transform Pan – Click “Use First Row as Headers”


It will automatically use the top row of the table as headers.

Now look and check if there any columns in the table which type of the data is different then it’s type: Suppose you have date column with data, but your column type is different. In that case you need to change the type as date.



Now I am going to remove unnecessary column from the table to make the table clean. Here in this table Middle Name value is null. Means no need middle name column. So, select the column and click remove column.


In this stage I want to merge two columns into one; I need to merge First Name and Last Name together into one as Full Name:

Select both First Name and Last Name column and go to Add Column Tab and select Merge Columns


Select both columns

 


Select Merge Columns


And from Separator list select Space


And give a name for the new column and click ok. And remove the old two columns.

Here is your Employee Table is ready to use.

Select Employee List from the Other Queries to see the final employee list.

I followed all the required steps mentioned earlier to clean the data, and here I am sharing the extra and important steps and procedures for Daily Attendance:

Here we are going to add new data source from the Daily Attendance sub folder inside the main Attendance folder as I kept the daily attendance files separately.

Go to transform from Power BI


If you are in the Power query than leave this steps.

Make sure your cursor is on the Other Queries:


Click New Source and Click Blank Query


Like the below: Query1




Click New Source and Click More


On the Get Data windows select Folder and click connect



Browse the location where you saved your data



Click Daily Attendance and Click Ok




Remember to follow all the below steps inside the "Transform Sample File (2)" under "Transform File from Daily Attendance"



I have data into this table as column name Geolocation combined latitude and longitude together, I need the latitude and longitude coordinate values in separate columns this will help me later to generate google maps link automatically. For that I need to split the Geolocation column.  



Go to transform pan, select Split Column by Delimiter


Select of enter delimiter: Space and select "Each occurrence of the delimiter"



Remove unnecessary columns, re-name the column name where it required:



Now we will keep only the first entry of the employee, as you may aware that on the HR.MY app we have Field Check In / Check In / Check Out and for our Quality Assurance team we only accept Filed Check In as the QA team works in different factories. So, we will keep only the first Check In entry by sorting.

To do that, select the time / check in / check out column 


From Home Tab, select Sort Ascending order


And then from Home Tab Reduce Rows Pan select Remove Rows and then Remove Duplicates, this will remove the duplicates rows which we sorted by Sort Ascending.



Now go to  Other Queries and Daily Attendance:



Select and remove the source.Name column




Now we need to select, clean, transform the Leave data for QA team same as we did for Daily Attendance to import the data and remove unnecessary columns and rows. I assumed you did follow all the other steps, now we will work on the "Transform Sample File (3)" under "Transform File from Leave [2]" for you it might be different.



After cleaning and removing unnecessary rows and column you will have:




Here we need to expand this two column into one, why? Because we want to separate the the date into one row there are a chance that a QA team member spend leave for more than one days in a row which will help us to calculate or to show the report in a convenient way. Have a look in the below From to Two column.


Go to Add Column Tab, Click Custom Column and Paste the below code and Click Ok.



Add the Custom Column Code inside column formula after the equal sign and click ok:


   List.Dates(Date.From([From]), Duration.Days(Date.From([To]) - Date.From([From])) + 1, #duration(1, 0, 0, 0))
Now click the two arrows and click Expand to New Rows




Rename the custom column to Date and remove the other from and to column and your final Leave Table is ready like:




Now go to Leave Table under Other Queries [3]



Remove Source.Name column


Here is your final Leave table looks like




Now go to



Add a custom column



Custom column code: 

   Table.AddColumn(#"Changed Type", "Custom", each 
    if Text.Trim([Remark]) = "" and Time.From([Time]) > #time(9, 31, 0) then "Late"
    else "On Time"
)
















Comments

Post a Comment