Attendance sheet to Power BI

[KelvinMorel]

Hi,

I'm having hard time thinking how to connect Power BI to following attendance sheet.

I have and Employees table in PBI and I would like to extract from following table the days they're present at work, I don't know how to do it.

Any idea...

[AllisonKennedy]

@KelvinMorel  A few questions:

Is each month in a separate Excel table?

Is it possible to update the 01, 02, 03 column headers in Excel with the full date (you can still format so it only shows 01, 02, etc)?

Is there any other data in this Work table that you are using? 

Do you have a DimDate table? https://allisonkennedycv.blogspot.com/2020/04/dimdate-what-why-and-how.html

Ultimately, the way to go about this will be to Unpivot the Dates using Power Query. If you have no other data coming from that table, then simply select the Employee column header in Power Query and Unpivot other columns. 

If you haven't updated the column headers in Excel, then you will now need to do a series of M code or adding and merging columns in Power Query to get the full date. 

I would also recommend combining all months using an Append. 

[parry2k]

@KelvinMorel this can be achieved by transforming the data in power query, we don't need to change the sheet. Share a couple of months data in an excel sheet, share through one drive/google drive, and remove sensitive information before sharing. Once I have the data file I will send you the solution.

[KelvinMorel]

Hi Parry2k,

Thank you very much, here is a dummy sheet:

https://drive.google.com/file/d/1MYlJJ_0UvHWwMhfpBXZFT28OqgxsouHr/view?usp=sharing

I will take a look to the aswers later, I'm having a meeting right now.

[parry2k]

@KelvinMorel you have created a table for each month, do you want me to use tables or the full attendance sheet, the solution is going to be different depending on what you want me to use. if you will always have tables then I would recommend I use that.

[KelvinMorel]

Yeah Parry2k,

I was also thinking that working with tables could be better, thoses tables will be filled by someone else not me.

[parry2k]

@KelvinMorel that makes it super simple but one critical thing missing in the dataset is years? Since the table names are Jan/Feb/Mar and it doesn't tell if for which year, do you think there will be separate excel file for each year with 12 tables in it, one for each month. I like to put together a more scalable solution rather than patchwork, it is just me. 🙂

[KelvinMorel]

We're for a solution and if this is the one we're looking for than it could be easier having years...

Thank very much for the effort...

[V-lianl-msft]

Hi @KelvinMorel ,

I think it's very difficult to extract the information you want from this table. You can consider using SharePoint to record attendance, and then use power bi to connect to the data source and show the visual

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[KelvinMorel]

Yeah,

It's crasy that it's so difficult to get the info out of this table. Honestly my head was about to explode just thinking about anyway to do so.

I was thinking maybe I could "transpose" the table to get columns like: [Date] [Emp01] [Emp02] [Emp03] and in the rows level "W" or empty.

I will have a look to the Sharepoint option as well

[parry2k]

@KelvinMorel sorry it took a bit longer, busy day, the solution is attached. You can tweak it as per your need. Sorry, I couldn't spend enough time to document everything but I guess you will get it from here. if you have questions, please feel free to reach out.  

There is always a solution, a matter of taking time out and understanding the problem and boom...

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[KelvinMorel]

Wow @parry2k 

I don't know what to say, I still need to understand how you did it but it's just awsome! Maybe I will come back with question but thank you so much to help me out.

[parry2k]

@KelvinMorel glad to help, it is pretty straight forward, I think I can still tweak few things but it is pretty good for now. 🙂

[KelvinMorel]

Hi again,

I'm trying to understand the "fnAttendanceData" function to see if I could modify it so the date could fully be read but as expected it doesn't work that way.

Edited

Maybe explane why I would like to have the full date, I'll add an extra column "Worked Hours", something like if [Worked] = "W" then 8 else if [Worked] = "H" then 4 else [Worked], being "W" fullday and "H" halve day, I would like that end-users could drillthrough by Year, Month, Week and day.

Your fnAttendanceData

(AttendanceTable as table, Month as text, Year as number) =>
let
 //   Year = 2020,
//    Month = Text.Proper("Apr"),
    Months = { "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"},
    MonthNumber = List.PositionOf(Months, Month)+1,
    Source = AttendanceTable,
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"EMPLOYEE NAME"}, "Day", "Worked"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Work Date", each #date(Year,MonthNumber, 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Day"})
in
    #"Removed Columns"

Mine

(AttendanceTable as table, Day as text, Month as text, Year as number) =>
let
 //   Year = 2020,
//    Month = Text.Proper("Apr"),
    Months = { "Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"},
    MonthNumber = List.PositionOf(Months, Month)+1,
    DayNumber = List.PositionOf(Days, Day)+1,
    Days = {"1","2","3","4","5","6","7","8","9","10","11","12","13","14","15","16","17","18","19","20","21","22","23","24","25","26","27","28","29","30","31"},
    Source = AttendanceTable,
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"EMPLOYEE NAME"}, "Day", "Worked"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "Work Date", each #date(Year,MonthNumber,DayNumber, 1)),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Day"})
in
    #"Removed Columns"

[KelvinMorel]

Hi Parryk2,

Now when dates are getting through Power BI we getting 01/mm/yyyy I was wondering if we could get the full date like dd/mm/yyyy.

Could this be possible?

Grtz