cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

Highlighted
Helper II
Helper II

Re: Attendance sheet to Power BI

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.

Highlighted
Super User IV
Super User IV

Re: Attendance sheet to Power BI

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

 

 






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

Highlighted
Helper II
Helper II

Re: Attendance sheet to Power BI

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"

 

 

 

 

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