Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

14 REPLIES 14
AllisonKennedy
Super User
Super User

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

 

 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as 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 C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

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.

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

Yeah Parry2k,

 

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

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

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

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.

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

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

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

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.

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

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

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"

 

 

 

 

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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.