Select Sidearea

Populate the sidearea with useful widgets. It’s simple to add images, categories, latest post, social media icon links, tag clouds, and more.

Call Now! 07792 547428

Microsoft Excel Data Transformation Magic

Yesterday I received an SOS from a colleague… “Sue, I’m stuck! It says it can do it but it can’t…. and I’m running out of time.  I have a deadline to meet and am seeing clients all day.  Why won’t it work, help!!!” .  So I did.


The problem was that the HR software that was exporting the data in a csv file.  Microsoft Visio can read in data from a csv file.  Simple, or so you would think.  This is where you enter my wonderful world of data, and to me it is wonderful.


Without getting too technical, both software systems will import and export csv files but what they fail to tell you is that they will only work together if certain conditions are met.  What does this mean?  It basically equates to a jigsaw puzzle.  You can’t put a square piece in a round hole.  


Therefore, it was Excel to the rescue.  The data needed to be changed in order for it to fit into the Visio software to give this amazing organisational chart.  It was amazing too.  300 members of staff placed into an organisational chart in ten minutes…. but only after the data fitted.


I could have used Python or R but the user wouldn’t have access to these to repeat the task as the records change. 


Therefore Excel to the rescue.  Excel has this amazing engine at the back called Power Query which enables you to change data to make it fit.  It also records each step so that you can redo the steps when you have new data.


The end of the story, both systems now talk to each other and there’s an Excel spreadsheet in the middle translating.   My colleague spent all day doing what they do best, running their business and earning money, happy in the reassurance that their problem was being solved.


Everyone has Excel, it really is an awesomely powerful piece of software which is increasing in power monthly.


If you are in need of some Excel wizardry, or want some help in mastering the joy of Excel, feel free to connect.


Everyone loves to complete that puzzle… or is it just me?

1 Comment
  • Mandy Bowden
    Posted at 5:12 pm, March 31, 2019

    Oh this sounds fantastic – so you end up with a Visio structure diagram for your organisation that can be updated automatically at the touch of a button – brilliant!!! Every business should have one – I will share with Delt’s HR team. 😀

Post a Comment


This site uses Akismet to reduce spam. Learn how your comment data is processed.