Blog Post XelPlus

            I can’t help feeling a little dubious, dear reader, when software companies make the claim that they can Easily Import Data to Excel!  My first question is, easy for who?  Afterall, I can easily help my friend unload his belongings by shoving them out the back of a moving van.  I wouldn’t call the results very useful, or pretty.

You would imagine that software designed for the benefit of the finance community would play nice with Excel, given its ubiquity.  Instead, the bean counters are treated to software devs who think their reports are pieces of art to be hung above desks and mantlepieces.  They do not want their data being extracted and thrown into those ugly cells, formatted down to become some line item in a common ledger! 

Thankfully, Leila Gharani provides us with an incredibly useful solution that, like the best things in Excel, can be automated after doing a little bit of work up front.  Her YouTube video is called How to Import and Clean Messy Accounting Data. 

Although the scattered mess of information she starts off with is her own sample data, it’s sloppy enough that you would believe it’s been thrown, sorry, imported, there only a moment before.

Two things jump out at me when looking at it.  First, is the amount column.  There is a row at the bottom of each cluster of data that provides the sum of that cluster.  Alarm bells are already going off because your counts will seem to have magically doubled, until you catch on that you combined their total along with what you’re totaling.  You can tell what each of the clusters represent by reading the Account column.  However, there is only one row of information at the top of each cluster, leaving us with blank lines until we reach the totals row at the bottom, meaning any attempt we make at filtering our table by the account info column will return the top row of each cluster only. 

After turning the whole mess into a table, Leila opens up Power Query and begins the dance of data clean up.  It’s a dance that I think goes largely unappreciated.  When done right, there’s no wasted movements, each step taken is clearly narrated and smoothly executed.  As if by magic, entire columns are selected and erased, blank cells are filled in, excess data is pulled neatly out to adjoining cells and eliminated just as quick. 

I did notice some old functions from my Office 2016 days that have been improved with the launch of 365 and given a new coat of paint.  Text to Columns can now be done using Split Column, but your given a dropdown menu with more choices on how to separate your values.  And that diminutive black square you’d typically drag over to fill in the empty cells beneath, has a more powerful counterpart called Fill.  You can summon it by right clicking the column header and selecting Fill.  It fills all the blank cells in the column with the information above it.

The biggest takeaway from this video is that every action done inside of Power Query gets recorded, like a macro.  You can see the fancy new Office Scripts narrate your every move as you plug away at the messy table.  The payoff, of course, is when you’ve got a new file to import, you just swap out the old data, right click on your pretty table and hit refresh.  The macro remembers all the moves you made, the mess is cleaned up behind the scenes, and you move onto your next project!