This post shows how a quick Excel chart, prepared with data cleaned up using Power Query, can help identify the point in time when a change in the IT landscape caused an error in a dependent system.

The background

Something was wrong.

For the last two years, we’ve had a procedure in place that imports event data from System A to System B. Now the users reported that the time stamps from System A were off in System B. All of them. By 12 hours.

OK, my employer’s company is in New Zealand, which means that time zones, regions and locales are a big problem for software that can be very US centric. Therefore, the “12 hours off” pointed towards a possible locale issue.  But we couldn’t really see what had changed in our environment, or when.

We run many different systems from many different vendors, most of them from the US (Looking at you, Microsoft). There’s often a lot of fine-tuning and workarounds involved, so things work in New Zealand. And when the vendor publishes an update, our workarounds may be superseded and become obsolete, or in the worst case, even revert the issue and turn against us. That’s why we suspected that some change in one of our systems was to blame.

But which change? And when did it happen?

The approach

Enter Power Query and Excel charting for some quick ad-hoc analysis.

We knew in December 2016 the imported dates were still fine. We knew by the end of March 2017 the imported dates were off by 12 hours. So, something must have happened between December and March.

There are hundreds of events every day, so just “downloading” a few months worth of data into Excel would probably have caused a major grind-to-a-halt of my machine. But with Power Query, things are different.

I created a connection to the system where the dates were showing “wrong”, which is an on-premise SharePoint 2010 list. With just a few clicks on commands in the ribbon of the Power Query editor, I specified that I wanted data after 1-Dec 2016, and divided the data for date and time into different columns.

With that data in my spreadsheet, I created an XY Scatter chart with the dates on the X axis and the time on the Y axis.

Our business is 24/7, but I know that most of our events are during the day, so I expected a pattern of data points between 6 am and 6 pm. And sure enough, that is what I saw when I filtered the data for December:

That looked pretty normal. Each event is a dot. Most events happen during the day.

I changed the filter to January, then to February. The pattern stayed the same. Then I changed the filter to March and saw this:

Gotcha! The pattern changed after 15-March.

Now we know WHEN the change happened and we can consult our change management system to find out more about the exact changes that were performed on our systems that day and take steps to fix the problem.

That’s what I call “data squinting”.

Sometimes you don’t need a perfect chart, as long as you can identify a pattern by just glancing sideways at the data.

Setting up the data with Power Query was a breeze.

Before Power Query, I would have had to create a view in SharePoint first, with all the trouble you have when a list has more than 5,000 items. Then, after exporting the view to Excel, I’d have to use Excel formulas to split date and time into two columns. With that many data points, I’d be looking at some calculation time.

With Power Query, I can quickly set up my filter parameters by clicking a few commands, then split out date and time into different columns and load the query.

Then set up a scatter chart and off we go data squinting.

Have you used Power Query for a quick ad-hoc analysis like that? Let me know what you found.