Ever had report usage data and needed to figure out which day of the week is quietest so you can do maintenance? Want to visualize spikes in sales activity based on hour ranges within the day? Need a date or time dimension for a proof of concept in Power Pivot before you bother to spin up a real data warehouse? You’ve come to the right place!
Many excellent BI bloggers have covered creating a basic date dimension using Power Query. Indeed, I learned a great deal from Chris Webb and Matt Masson as I was creating my own date & time dimension toolkit. What I’d like to cover in this admittedly long post is how to create Power Query functions that you can spin up and configure quickly in Power Pivot. These functions build in the fields you need for both the attributes you display and the hidden fields you use to make those display nicely.