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.
Solved! Go to Solution.
@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...
@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.
@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.
Thank you very much, here is a dummy sheet:
I will take a look to the aswers later, I'm having a meeting right now.
@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 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. 🙂
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
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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
We're excited to announce our first cross-community 'Can You Solve These?' challenge!
Visit our Community Blog for articles, guides, and information created by fellow community members.
Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.
We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.