Post

Ken Puls, FCPA, MS MVP
@excelguru.ca

A question sent in for our recent 'Ask Ken Anything' (AKA) session tackled a common reporting headache: how do you compare hours logged in a project management system against data from time-tracking software? 🕒 I walked through a solution I built using #PowerQuery and #PowerPivot. 🧵

Ken Puls, FCPA, MS MVP
@excelguru.ca

1️⃣ Clean both sources. 2️⃣ Replace "0:00:00" entries with nulls so empty records wouldn't clutter the #PivotTable. 3️⃣ Build shared employee and calendar tables 4️⃣ Write measures to calculate the variance. The breakthrough was one small step...

Ken Puls, FCPA, MS MVP
@excelguru.ca

The time tracker stored durations, which turned into tiny decimals next to the project hours. Using Transform > Duration > Total Hours converted 5h 59m 50s into 5.997h, making the systems directly comparable. 🧭Takeaway: When time data won't compare cleanly, make sure you’re using a common data type!

0 reposts0 likes

Ken Puls, FCPA, MS MVP
@excelguru.ca

Bring your trickiest data puzzles to the AKA and see them solved step by step. Enroll in my #SelfServiceBI (skw-t.com/ssbi) or #Excel Fundamentals (skw-t.com/excelfun) Academies at @skillwave.training to gain access, or subscribe to just the AKAs at at skw-t.com/aka

skw-t.comSelf Service BI AcademyPrepare to change your company's reporting game forever! In the Self Service BI Academy, Ken Puls will teach you how to use Excel, Power Query, and Power BI together to build robust Self Service BI…