When you know that it should work and it doesn’t, it drives you crazy… or is that just me?
So the challenge was to take a data export from a time management software tool and convert it to a Power BI dashboard.
This particular piece of software is used to measure the time spent by staff within the business. The software captures which member of staff, which client, the category of work, the date and the length of time taken. Perfect what can go wrong?
So how does Microsoft Excel stop the computer saying no….
When you press a key on your keyboard, the computer converts the key that is pressed into a number. So if I press ‘a’ the computer reads 97 or in binary 01100001, capital ‘A’ is 65 (01000001) . These aren’t the same the computer says, but we know that this could be someone entering a name sometimes with a capital and sometimes not.
To create a dashboard, similar to below, we need to be able to group by name, category and conpany at the very least. Therefore it’s essential we group our data correctly.
Using power query we were able to clean up the data, change all the names to refer to one person. Ensure the categories were all the same and boom all done….
Well almost…. bar dates. A lot of software exports dates as text to enable you to import it in with the correct formatting. Dates in different parts of the world are formatted in many different ways. In the UK we generally use day, month, year whereas in the US it’s month, day, year.
It’s one of the areas that you need to be aware of to understand why sometimes the computer says no.
This excellent article gives a summary of the different steps you can use power query in Excel to transform your data.
Everyone has Excel, it really is an awesomely powerful piece of software which is increasing in power monthly. It’s more than a calculator but one that you can master with ease. If you are in need of some Excel wizardry, or want some help in mastering the joy of Excel, feel free to connect.
I will be running Excel courses at the China Fleet Country club every month.
The first course starts Thursday 25th April. A chance to unlease your inner genius!