How Microsoft turned me into a monkey- A Data Monkey

I remember sometime early in 2015, I was involved officially in my first consulting project, Report Automation project for a top Multinational company in Nigeria. I was a rookie. One of the early steps in the project is to get the client's historical data. We requested for 5 years of historicals. Our client's data came in multiple excel files, and of course, requiring a lot of clean up and manipulation for each workbook. I had 60 workbooks of data to consolidate into one clean analysis-fit data table within a couple of hours. I could do this manually but that would take about a week to complete a good job of cleaning the data and combining all the files. There was no such time. How did I manage this? This is part of my @dbrownconsulting experience.

At first, I questioned the wisdom in my decision to quit Banking for such technical job. I thought I could do anything because I was excellent in my Customer Service duties. I was the go-to-guy for all customer service related issues in my branch, even in my region. I knew over 100 Finacle Menu shortcuts by heart. Some of which were even "long short cuts". How about HACXFRSC for a shortcut? I could vet a Corporate account opening form with as many as 5 directors within a minute to two. There were days I would open more than 300 accounts.

So, how come this job is becoming too difficult for me. I had gotten a good grip of payroll management and i was already handling about 5 companies monthly. I could write decent Excel formulas with nested functions. I could build a Financial Model from scratch. But this new assignment gave me a headache.

There is always light at the end of the tunnel they say. I got my lucky break, it turned out to be a tough one after all. My Principal Consultant, though refused to give me fish, neither did he teach me how to fish, he did well enough to let me know where the fish is and it was left to me to learn how to fish.

Introducing Microsoft Power Query

I learnt there is a magical tool already inside of excel that could swiftly clean and combine this 60 monster workbooks that has made me doubt myself for the first time as a professional. "Go and explore it". So I found Power Query in my Excel 2013 but did not know how to use it to achieve the result i desired. Google they say is your friend, at that time, Google wasn't of much help to me. This tool was so new there was little guidance online to help, as a matter of fact, no book on Power Query had been published.

After a few trial and error, i completed the task without writing a single line of code. The user interface is so friendly all i needed to do was click.....click.....click and click. So i could really do anything. The relief i felt was huge, but the joy inside me me was much bigger. I immediately fell in love with Power Query and I don't love easily. It was at that point that everything changed for me. I gradually started forgetting all those banking menus to create space for a new language in my "memory card".

I would later go on to read the book "M is for (Data) Monkey" by Ken Puls and Miguel Escobar.

Power Query is an ETL (Extract, Transform and Load) tool available in Excel and Power BI. What it can achieve is nothing short of Magic. It uses a language called "M" but you do not need to learn this language to get started, the user interface will be enough for most of your needs for Power Query. With it, you can:

1. Connect Excel/Power BI to over 80 data sources including MySQL, Oracle, Sybase, Azure, Access, Facebook, Twitter and even the Web.
2. Automate the cleaning process of any kind of data. I find it useful when I connect to files in a folder and every time i get new file, I just dump it into the folder and it gets cleaned and combined automatically.
3. Work with unlimited number of rows in Excel and Power BI. Yes, Excel has a row limit of 1,048,576 per worksheet, but when you connect with Power Query, you have no limit.
4. Perform various data transformations, all automated.The steps are being recorded for every step and click in Power Query.
5. Perform more advanced operations.

Using Power Query since 2015 has benefited me a lot, I cannot place a quantify this. I have helped developed time saving Power Query solutions for clients and trained people on how to use this tool.

Stop spending time on data. Start using Power Query, be a Data Monkey.