Permanent Timecard Lookup Tool
The Problem
The timecard team's original Power BI dashboard had been built around tracking a complex 5-wave rollout across 4 months. When the final wave completed, the wave-based tracking features became obsolete. However, the team had integrated the dashboard into daily operations as their exclusive operational lookup tool for finding approver assignments, employee contact information, process documentation, and timezone references during customer interactions and timecard maintenance.
The dashboard required transformation from temporary wave tracker to permanent reference tool. Original technical implementation reflected first Power BI project limitations with 13+ visible tables cluttering field lists, 5 separate wave queries creating maintenance overhead, exposed helper tables, and manual refresh dependency requiring constant attention. Team needed the high-value lookup features amplified while obsolete wave tracking removed, but the underlying technical debt also required cleanup after 6 months of additional Power BI learning.
The Solution
Removed wave timeline visualization, countdown timer, and wave filter slicers completely. Amplified search functionality by adding GEMS ID search panel alongside existing name and cost center searches. Redesigned team member selector from scrollable slicer to at-a-glance button tiles with transparent Figma background eliminating scrolling needs.
Added timecard count column to main data table using DISTINCTCOUNT DAX measure showing workload distribution per cost center. This enables proactive workload rebalancing when employee attrition creates imbalanced timecard splits across the team.
Built dynamic employee information panel as major UX innovation. Panel remains completely blank in idle state with zero visible elements. Upon employee selection, panel instantly populates with comprehensive details including preferred name, email, GEMS ID, job title, employment type, state with full name mapping via SWITCH DAX covering all 50 states, timezone, and supervisor information. Three intelligent email buttons appear simultaneously, each constructing mailto links with appropriate CC recipients and auto-populated greeting using employee's preferred name.
Implemented non-app user detection system pulling from SharePoint source to display prominent visual alert when selected employee uses older phone requiring manual punch process. Alert provides instant visibility during customer interactions and timecard maintenance preventing incorrect process application for employees who cannot receive missing punch emails within normal timeframes.
Upgraded location visualization from Bing Maps to Azure Maps for clearer state labels and improved navigation. Retained info board Power App integration for team announcements.
Architecture
Migrated data source from Access Database to SharePoint Excel enabling scheduled refresh automation. Created PowerShell script running via Task Scheduler to copy employee list from network drive to SharePoint OneDrive sync folder with BurntToast module providing Windows toast notifications for copy status. Consolidated 5 separate wave queries into single cost center split query pulling from consolidated Excel file. Reduced query count from 13+ to 9 with proper visibility management hiding transformation tables from field list. Implemented star schema with Approvers List fact table joining to Guidelines dimension. Applied data quality improvements replacing nulls with "N/A" for critical fields. Advanced DAX implemented conditional visibility logic using ISFILTERED() to control employee information panel and email buttons appearing only when selection active. Mailto link construction concatenates email addresses with CC recipients and pre-populated greeting using preferred name. State name lookup uses comprehensive SWITCH statement mapping all 50 abbreviations to full names.
Key Implementation Decisions
- •SharePoint Excel over Access Database for data source enabled scheduled refresh automation eliminating manual intervention
- •PowerShell automation with Task Scheduler bridged network drive to SharePoint for seamless data pipeline
- •Consolidated 5 wave queries into single cost center query simplifying maintenance and reducing complexity
- •Conditional visibility using ISFILTERED() DAX created clean idle state with progressive disclosure UX
- •Three intelligent email buttons with mailto links automated recipient selection and preferred name greeting
- •Non-app user detection system provided instant visual alerts preventing incorrect process application
- •SWITCH DAX statement mapped all 50 state abbreviations to full names for improved readability
- •Hidden transformation tables in Power Query reduced field list clutter improving usability
The Results
Quantifiable Outcomes
- ✓Exceeded expectations by adding proactive features beyond requested wave removal
- ✓Intelligent email automation using preferred names received exceptional recognition for inclusive design consideration
- ✓Non-app user detection eliminated errors in missing punch email timing and customer expectations
- ✓Email workflow reduced from multi-step manual process to single-click automation with proper recipients and greeting
- ✓Timecard count column enables proactive workload rebalancing before complaints arise
- ✓PowerShell automation of specific Excel workbook refresh eliminated manual refresh dependency through scheduled execution
- ✓Cleaner data model with hidden transformation tables improved field list usability
- ✓Recognition for thoughtful feature additions demonstrating proactive problem-solving
- ✓Dashboard remains in active daily use as team's exclusive Power BI operational tool
Lessons Learned
- →User expectations provide minimum bar, not ceiling: Team expected wave removal but appreciated amplified capabilities and new features
- →Technical debt cleanup enables better maintainability: Consolidating 5 wave queries into 1 cost center query simplified future updates
- →Proactive inclusive design builds trust: Intelligent email automation using preferred names was unrequested but demonstrated consideration for all team members
- →Conditional visibility creates clean UX: Blank idle state prevents overwhelming users until information becomes relevant through selection
- →Automation eliminates maintenance burden: PowerShell scheduled refresh of specific Excel workbooks removed manual intervention requirement enabling focus on analysis
- →SharePoint migration enables better automation: Access Database connectivity limitations resolved through SharePoint Excel connector
- →DAX conditional logic enables sophisticated progressive disclosure: ISFILTERED() pattern coordinated multiple element visibility changes simultaneously