cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Attendance sheet to Power BI

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.

 

table0.jpg

 

Any idea...

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User IV
Super User IV

Re: Attendance sheet to Power BI

@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...

 

image.png

 

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!

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





View solution in original post

14 REPLIES 14
Highlighted
Community Champion
Community Champion

Re: Attendance sheet to Power BI

@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. 

 

 

 


______________


Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos.


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


https://sites.google.com/site/allisonkennedycv

Highlighted
Super User IV
Super User IV

Re: Attendance sheet to Power BI

@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.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Helper II
Helper II

Re: Attendance sheet to Power BI

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.

Highlighted
Super User IV
Super User IV

Re: Attendance sheet to Power BI

@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.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Helper II
Helper II

Re: Attendance sheet to Power BI

Yeah Parry2k,

 

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

Highlighted
Super User IV
Super User IV

Re: Attendance sheet to Power BI

@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. 🙂






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Helper II
Helper II

Re: Attendance sheet to Power BI

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...

Highlighted
Microsoft
Microsoft

Re: Attendance sheet to Power BI

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.

Highlighted
Helper II
Helper II

Re: Attendance sheet to Power BI

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

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors