This week's blog post on solving business problems with Power Query is a guest post by Alex Jankowski
Does your business or work group have manual tasks that are repeated regularly, such as data re-entry, copy/paste functions, sending the same old email, cross-referencing lists, etc.? We can automate these kinds of activities with a wide variety of tools, such as Excel (including VBA, Power Query and Power Pivot), Access, SharePoint and MS Power BI, as well as with other, more sophisticated software packages.
Solving Business Problems You Didn't Even Know Existed
I have found the following to be common themes that can inhibit identifying and solving business problems:
- Organizations are illiterate about their data.
- Organizations are allergic to cost.
- Many managers lack technical imagination. They can’t even conceive of the problems that the organization’s data could solve, let alone conceptualize potential solutions.
- Organizations think that only financial data is worthwhile.
- Organizations roll out massive ‘suites’ without a concept about how to get their money’s worth from the licenses. My guess is that on average less than 25% of the capabilities of MS Excel are utilized due to lack of training/imagination, which is not a good ROI.
- Organizations think that the IT group has a handle on this, whereas IT thinks that data belongs to the business or prevents anyone from accessing important data (IT acting as a “department of productivity prevention”).
And I’m not even talking about the mission critical data that resides in big ERP systems (including HR, Corporate Finance, Time Records, and Project Financials) or “Big Data” that everyone is looking over their shoulders at.
What about:
- Detailing a list of technical drawings on a project?
- Tracking hours spent on a project activity, or the employee vacation schedule against entitlements?
- Creating a dataset of employee skills and performance reviews?
- Linking that scheduling software package to a list of real deliverables?
The Basic Win - Owning Your Data
Every business has what can be termed ‘islands of data’ which are often generated and managed manually.
There are 'islands of data' that exist in every organization
Let’s say that your business splurges to automate and standardize some elements of the work process to be more effective. What are the benefits? Well, there will be a reduction in repetitive costs that offset the application implementation costs. People can then graduate from data makers to data owners (where they spend more time analyzing report data rather than creating the report itself), and begin solving business problems by making smarter, data-driven decisions based on questions like, "Is that it? Was it worth it? Where’s the big payback?"
The BIG payback is in linking the ‘islands of data’ with each other to gain unexpected insights. Don’t believe me? Let's take a look at a simple real-life example.
The Unexpected Win
A few years back, I was working in an engineering firm with a very large project. The Operations Manager (thankfully, a guy with great data imagination) wanted to a way to track piping isometric drawings as they were reviewed by various project leads. The drawings would get lost in the review process, like when the head of electrical left them on his chair when he went away for a month in the summer. So we came up with a solution to identify every isometric in the review process, who had approved it, when it was approved, and where it was going next. We met the application goal and stopped losing the isometrics in the review process.
Sample Isometric Pipe Drawing (from http://www.svlele.com/drawings.htm)
Was that the BIG win? Nope. The big win was when we started matching the list of drawings that had completed the review cycle with the list of drawings issued for manufacturing in the document control system (which would have been a great opportunity for Power Query – I wish we had it then). Guess what? We found a small count of approved drawings which had not been issued to manufacturing.
The Real Cost of Not Doing the Data Work
You're thinking, "So what?" Well, each isometric drawing represents a section of pipe that is fabricated by the manufacturer with flanges, valves, fittings, etc. The fabricated pipe is then sent to the construction site holding yard to wait its turn to be assembled in the pipe rack by the construction team. The impact of the drawing not being issued to the manufacturer is that the pipe section is not on site when it’s time to construct, and nobody recognizes it is missing because the drawing was never issued.
What’s the cost of that?
- extra cost: Rush to find and issue the missing isometric drawing
- Extra cost: Construction team has to re-sequence its work to allow for the missing pipe section
- Extra Cost: Rush fabrication by the pipe manufacturer, possibly resequencing their production schedule and possibly charging overtime rates
- EXTRA Cost: Rush shipping charges to get the late pipe section to the construction site
- EXTRA COST: Possible overall delay in the construction schedule
- BIG EXTRA COST + LAWSUIT: Possible overall delay in the refinery startup schedule
That extra cost could be thousands of times more than the cost of doing the data work, and possibly end up eating all of the project’s margin and more. (If you’re going to be allergic to cost, this is the one to be allergic to). Creating the application for isometric tracking was a win, but matching it to the document control system drawing list was the BIG win and nobody expected it!
Delivering the BIG wins
Let's take a look at some ways at solving business problems in Power Query using the data you already have. What if you:
- Need to find files, audit folder contents, or document a folder structure on the server? Power Query does that.
- Need to develop an email filing system for your project? Power Query can list the emails, their contents, and attachments.
- Need to open server files from an Excel list without navigating through the File Explorer? Power Query can help with that. (Eliminating the practice of people navigating from "My Computer" into the bowels of the server structure for each individual file is one of my personal lifetime goals.)
- Need to create a templated report about projects, employees, or hours from an ERP system extract? Power Query does that.
- Need to pull data from SQL for templated operations reports? Power Query does that.
- Need to read multiple CSV or Excel files into a single data set? Power Query does that (check out some of Ken's other blog posts, like this one on Combining Excel Files).
- Need to report on the status of drawings in your document control system? Power Query does that.
Once the 'islands of data' are being processed, Power Query does an awesome job of combining the data sets together, especially if all the column names are different and the data formats need to be aligned.
What data problems do you need to solve? How are you currently solving business problems, and are there opportunities for BIG wins in your business? Give us some feedback in the comments!
9 thoughts on “Solving Business Problems – Power Query Does That”
Excellent post. Data problems ARE business problems. And thinking about your data forces you to think logically about your business -- cause and effect, data definitions, etc. Computers don't speak BS -- they can't process the fluff that passes as "strategy" among many managers. Translating processes into computer-speak is actually very enlightening for management.
I also find it really interesting how the process of using data starts to affect the quality of the data being captured. When we first started building Power Pivot reports in the resort I worked at, we started discovering all kinds of oddities. Things like tournaments or groups not being rung in until several days after the fact.
As odd as that seemed, it was because the day of a tournament are VERY busy, and the staff were leaving the work to a slower time when they could focus on it more thoroughly. If you've ever been involved in running a big tournament, you know what a HUGE job it is, and how many fires are constantly on the burner at once. From a human perspective, you can understand why this was a tempting treatment.
The problem here is that the longer it went between the actual date and the recording date, the more of a challenge it became to reconcile. Partial transactions would get rung in, things would get missed all together, and sometimes they would even get lumped in with the NEXT event. Trying to report profitability by event was virtually impossible. And of course, the worst was when someone outright forgot one.
Once the dashboard reports became a daily task, it became the department manager's job to get good data. If the event wasn't rung in, they were back to their team to find out why, and drive the importance. End result was better reporting, less errors, and most importantly, no lost revenue.
"Many managers lack technical imagination. They can’t even conceive of the problems that the organization’s data could solve, let alone conceptualize potential solutions."
This. 1,000 times this.
It is so frustrating to walk into a company or department and see the amount of manual work being done that is just one step up from using scissors, paper, and tape to put a report together when just the slightest bit of imagination can ask "If all of this data is in a computer system somewhere, how can we get it together?"
Just ask. the. question!
Then find someone to help. You'll save time, reduce errors, and spend time on analyzing the data rather than compiling it, which is a win for the company.
The bigger thing for me is maintenance. My company is littered with the Ghosts of Automation Past, where someone made enough of a fuss to get something automated...And then the person who knew how that thing worked left, and none of the newer crop of technical people can understand the horrible mishmash of VBA, PowerShell, Access, Pascal (yes, we still use Pascal), and all kinds of other weirdness that comprised the solution. So as soon as anything with the process changes, it's basically rendered worthless.
Power Query is not only so much easier to set up, it's so much easier to maintain that we're not nearly as worried about that anymore.
@Matthew Runyon,
Exactly. Power Query is still a long way from being self-documenting, although the Query Dependencies view is a small step in the right direction, along with commands like #shared and #sections.
For me, the bigger picture is like this - Implementation scope (and budget) should not be limited to "ok, it worked". There are many elements to a competent implementation (my next rant, I think) even if its for an in-house application or utility. A couple of those things include design documentation and a support plan.
The impacts of the Ghosts of Automation Past can be mitigated with the right approach. It is also true that often the benefits of the now-obsolete app have already paid off - but it is only the go-forward risk that is perceived.
Ultimately, the cost of the app is not just the get 'er going cost. There are lifecycle upkeep costs (who's data is static?) and future replacement costs that need to be recognized.
Great post! I'm new to your site but not new to your book, M is for Data Monkey, which completely transformed how I think about my work. In fact, the biggest obstacle I encountered with applying PBI, is acceptance by others and getting people to think different. I'm not a wiz at the product but using your book, I sought out other groups in the organization and did a demo to build awareness and get creative and smart people informed on the potential. The ground swell is starting to rise and there is excitement...My concern: getting the data clean is the first step, got it... but once I start adding more tables and filtering, strange and unexpected results happen. I know it's because I don't know the ins and outs of filtering context and I probably didn't make the star scheme right but these are all new concepts for a " little better than average Excel" user. Anyway, keep up the amazing work,
Great article. Sadly., I'm asking "how?" After each declarative. Much to learn..
John, we'd love to help with that. 🙂
You can check out our online Power Query workshop here: https://www.powerquery.training/ This is designed to get people up and running with the technical aspects of the tool so that YOU can do that.
I work in construction too, so it is a pleasure to read about Powerquery and isometrics drawing !!!, I was introduced to PowerPivot/PowerQuery 2 years ago by a colleague and it changed my professional life, as i work in reporting I manage to build "Personal" Data-warehouse using PowerBI Desktop that automatically feed a dozen of reports, now all I do is refresh !!!