Real world item often have more than one critical date, Orders will have an Ordered Date, Dispatched Date, Delivered Date etc… In Incident Management the core dates to begin with are Created Date and Resolved Date, but there may also be Status transisiton dates i.e. On-Hold/Off-Hold/Assignment Changes etc… I struggled trying to work out how to fit this into a “Narrow your data” mentality. In the end I tried a completly different solution in 2.0. I pivoted the dates in my attempts at PowerPivot godness I probably went a little too far – I couldn’t get the original idea to work.
Pivoting the dates wasn’t as bad for me as it may be for you, I was able to do it on the SQL server, I created a view from the original source of the tables, that view pivoted the data and then PowerQuery imports the view, drops the mic and walks off like a pro
SQL Source – I wrapped mine in a create view statement
SELECT [Incident GUID], DateSwitch.DateValue, DateSwitch.DateType
UNPIVOT (DateValue for DateType in ([Created Date], [Resolved Date], [Closed Date])) as DateSwitch
I’ll break it down because I know I promised not to get overly technical. I’ll also share a secret – GUID, if you talk to DBA’s or System Architects long enough you’ll come across this term and they’ll invariably try and look down their nose at you if you ask what it means so I’ll tell you. GUID stands for Global Unique IDentifier. Simply put a GUID should be unique within the system – if not the whole wide world. They are 16 digit strings of numbers and are semi-randomly generated (I’m not 100% on the science of their generation) basically though when someone says they’re going to use GUID’s it means they’re going to set the Database up properly, numeric 1,2,3,4… indexes not only repeat for each table they also commit the biggest sin in database terms… the change width, 1, 10, 100, 1000.
For those old enough to remember it that was the worry about the Millennium bug “How will a computer cope if it has to put 100 (three digits) into the space for 99 (two digits)?” Millions if not Billions was spent so we wouldn’t find out the hard way.
The UNPIVOT section is going to get me into trouble, basically it’s going to turn a column into a row in SQL PIVOT would turn a row into a column, in essense though I’m sticking to what I said, I pivoted the dates using the UNPIVOT command in SQL. So we’re turning a column into a row, remember though at any given point in the query we’re only looking at one row. That means our single row will be slit into the number of rows you mention in the the UNPIVOT. Our Tickets have three dates [Created Date], [Resolved Date] and [Closed Date] so our unpivot turns our one row into up to three (not all tickets have been resolved or closed remember.
Suddenly there was a way of really pulling the column counts down for the imports. The second step occurs in PowerQuery. When importing that DateValue column change the datatype to just be Date – you lose the time but the reality is for most reports you won’t use it. Retaining the time in the SQL though means that IF or in my case more likely WHEN senior management say “We want to know what time tickets were created” you can handle it
I got too keen when I first hit upon this and tried to combine all the dates from all the processes I needed to report – they’re still like that in the SQL, however PowerPivot (or more likely my skills) let me down. If you work out how to combine the dates from a single FACT table into a single “Date Pivot” please let me know!