+ Reply to Thread
Results 1 to 20 of 20

Pivot table should show absent for the employee whose presence is not in source data

  1. #1
    Registered User
    Join Date
    06-02-2017
    Location
    Lahore
    MS-Off Ver
    2010
    Posts
    22

    Angry Pivot table should show absent for the employee whose presence is not in source data

    I need help for employees attendance report built by pivot table on check in and check out data.
    I have used power query and added some custom columns to calculate Present and Absent employees. Also created calendar and employees list tables in data model and created relationships between tables.
    Please see attachment. Pivot table should show absent for the employee whose presence is not in source data.
    Please help and guide me that what Am I missing?
    Please suggest some Custom Column / Calculated Column / Measure etc for desired result.
    Thanks and regards in advance.
    Attached Files Attached Files

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Pivot table should show absent for the employee whose presence is not in source data

    Hi, I'm no PQ expert but, you could create a cartesian of the Dates + Ees, to generate unique combinations thereof, and then merge that with your raw Table1 {left join}; finally, append resulting table with your Present / Absent calcs, and return to the Model.

    Refer attached for illustration of above [edit: I forgot to add the Absent/Present fields to the Pivot - but you can do this via Pivot window - Table2_2]

    As you infer, there may be a way of doing this using some DAX measure etc, but that's beyond my competency, I'm afraid.
    Attached Files Attached Files
    Last edited by XLent; 06-30-2020 at 09:47 AM.

  3. #3
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Pivot table should show absent for the employee whose presence is not in source data

    Excel cannot create what is not in your data. You will need to create some sort of record for those dates that indicates absence. What is not there is not there and cannot be formulated out of thin air. Your records will need to show the Employee for the date but with zeros for data for Excel to find them.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  4. #4
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Pivot table should show absent for the employee whose presence is not in source data

    Alan, the cartesian approach {per previously attached} will auto-generate the 'missing' records - so I think OP should be fine.

    Thanks,

  5. #5
    Registered User
    Join Date
    06-02-2017
    Location
    Lahore
    MS-Off Ver
    2010
    Posts
    22

    Cool Re: Pivot table should show absent for the employee whose presence is not in source data

    @alansidman

    Nothing debugged. Power query is tool in excel to use SQL data as source data for Pivot Tables.
    My source data is in SQL. Source data in attached file is just a sample table.
    Last edited by Ramzan-ul-Mubarak; 06-30-2020 at 11:07 AM.

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Pivot table should show absent for the employee whose presence is not in source data

    XLent

    XLent Point made. I had forgotten about a full cartesian. Like how you did that.

  7. #7
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Pivot table should show absent for the employee whose presence is not in source data

    @Ramzan-ul-Mubarak

    With PQ, you can GetData and select several SQL data bases as your source.

  8. #8
    Registered User
    Join Date
    06-02-2017
    Location
    Lahore
    MS-Off Ver
    2010
    Posts
    22

    Re: Pivot table should show absent for the employee whose presence is not in source data

    Yes. Thanks for guiding.
    Last edited by AliGW; 07-01-2020 at 02:55 AM. Reason: Please don't quote unnecessarily!

  9. #9
    Registered User
    Join Date
    06-02-2017
    Location
    Lahore
    MS-Off Ver
    2010
    Posts
    22

    Re: Pivot table should show absent for the employee whose presence is not in source data

    Quote Originally Posted by XLent View Post
    Alan, the cartesian approach {per previously attached} will auto-generate the 'missing' records - so I think OP should be fine.

    Thanks,
    Whats is OP?
    Please explain.

  10. #10
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Pivot table should show absent for the employee whose presence is not in source data

    OP - Original Poster / thread creator

    Please review the attachment provided back in Post #2 as, I believe, this does what you requested; you can review the steps in the PQ code and, hopefully, recreate on your real-life file.

  11. #11
    Registered User
    Join Date
    06-02-2017
    Location
    Lahore
    MS-Off Ver
    2010
    Posts
    22

    Re: Pivot table should show absent for the employee whose presence is not in source data

    Thank you so so so so much.
    I am currently out of work room. I will check it tomorrow INSHALLAH and I hope it will solve my problem.
    Last edited by AliGW; 07-01-2020 at 02:56 AM. Reason: Please don't quote unnecessarily!

  12. #12
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,811

    Re: Pivot table should show absent for the employee whose presence is not in source data

    Administrative Note:

    Welcome to the forum.

    We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

    Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

    (Note: this requirement is not optional. No help to be offered until the link is provided.)

  13. #13
    Registered User
    Join Date
    06-02-2017
    Location
    Lahore
    MS-Off Ver
    2010
    Posts
    22

    Unhappy Re: Pivot table should show absent for the employee whose presence is not in source data

    I am sorry. Actually we needed the solution urgently as some work is halted. I am very sorry for posting in more than one forum. Link of query on other forum is
    https://www.excelguru.ca/forums/show...rce-data-(SQL)
    I am very very sorry for this. I promise to follow the rules in future.
    Last edited by AliGW; 07-01-2020 at 02:54 AM. Reason: Please don't quote unnecessarily!

  14. #14
    Registered User
    Join Date
    06-02-2017
    Location
    Lahore
    MS-Off Ver
    2010
    Posts
    22

    Question Re: Pivot table should show absent for the employee whose presence is not in source data

    Quote Originally Posted by XLent View Post
    Hi, I'm no PQ expert but, ...
    Hey XLent
    When I apply this approach on actual data from SQL and I add custom column (Step 3) and then expand and remove duplicates, excel hangs and dont return to normal.
    It is i think due to large number of data rows. I think Adding custom column adds all dates rows to all employees separately and that increases the number of record lines by hundreds of times.
    One table has 2500 rows and other 25000. I think Adding custom column creates (2,500*25,000=6,25,00,000) rows. Which hangs excel and Power Query dont complte and save.
    What you say?
    Last edited by AliGW; 07-01-2020 at 02:55 AM. Reason: Please don't quote unnecessarily!

  15. #15
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,389

    Re: Pivot table should show absent for the employee whose presence is not in source data

    I see you have removed the nonsense about your 'assistant' ...

    Administrative Note:

    Please don't quote whole posts, especially when you are responding to the one immediately preceding your own - it's just clutter. It's OK to quote if you are responding to a post out of sequence, but limit quoted content to a few relevant lines that makes clear to whom and what you are responding. Thanks!

    For normal conversational replies, try using the QUICK REPLY box below or the REPLY button instead of REPLY WITH QUOTE.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  16. #16
    Registered User
    Join Date
    06-02-2017
    Location
    Lahore
    MS-Off Ver
    2010
    Posts
    22

    Re: Pivot table should show absent for the employee whose presence is not in source data

    @ Ali GW
    I have already said sorry.
    And I apologized for that mistake.
    I think that should be enough. Why are you still following that mistake by me and blaming me again and again?
    If you are administrator and you have some problems with me and cant accept sorry and you dont like new people on the forum (New people can do mistakes) then I request to delete my query and block me from forum (And block any people who do such mistakes).
    If you are so arrogant due to your knowledge and authority (Administration on forum) then please block ordinary people (who have less knowledge) like me.

  17. #17
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,389

    Re: Pivot table should show absent for the employee whose presence is not in source data

    I am asking you not to quote unnecessarily - that's all.

  18. #18
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Pivot table should show absent for the employee whose presence is not in source data

    Quote Originally Posted by Ramzan-ul-Mubarak View Post
    ...I think Adding custom column adds all dates rows to all employees separately and that increases the number of record lines by hundreds of times.
    One table has 2500 rows and other 25000. I think Adding custom column creates (2,500*25,000=6,25,00,000) rows.
    Yes, that's correct -- as Alan pointed out earlier, you need a physical record for every Employee and Date for this to appear in your subsequent Pivot.

    You could mitigate the number of permutations initiated in the cartesian join by isolating unique dates beforehand, this would make the 'remove duplicate' step (post-join) redundant.

    So, if you had 2500 EEs in table 2, and 25000 dates in table 1 of which only 1000 are unique you would reduce the cartesian join output to 2.5m (2500 * 1000)

    Obviously, if your Ee list also contains dupes you should remove those first too.

    Refer attached for working version -- again, the other more savvy PQ folk may have other / better ideas.
    Attached Files Attached Files

  19. #19
    Registered User
    Join Date
    06-02-2017
    Location
    Lahore
    MS-Off Ver
    2010
    Posts
    22

    Re: Pivot table should show absent for the employee whose presence is not in source data

    Quote Originally Posted by AliGW View Post
    I am asking you not to quote unnecessarily - that's all.
    I apologize from AliGW Moderator for violating any terms and conditions as pointed out by moderator in a message to me.
    Dear AliGW please accept apology and forgive me.
    Last edited by Ramzan-ul-Mubarak; 06-09-2021 at 07:35 AM.

  20. #20
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,389

    Re: Pivot table should show absent for the employee whose presence is not in source data

    Thank you for the apology.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 0
    Last Post: 01-23-2020, 05:45 PM
  2. Pivot table show top 10 Employee
    By janljan in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 04-19-2017, 06:24 PM
  3. Replies: 0
    Last Post: 01-06-2016, 07:00 AM
  4. Want to show source data for pivot chart in a table next to it
    By kmarie630 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 09-29-2014, 11:07 AM
  5. Replies: 1
    Last Post: 05-25-2011, 04:23 AM
  6. Replies: 1
    Last Post: 06-20-2010, 04:00 AM
  7. Pivot table - show absent categories
    By andrefrancis1 in forum Excel General
    Replies: 2
    Last Post: 11-07-2007, 06:00 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1