Category: Excel and beyond

Posted on

Power Query uses – small charity edition

Screenshot of the Get Data Launch menu, provided for decorative purposes
Power Query (Get Data) Launch menu

People sometimes ask: “Can you give us an example of Power Query uses”?

(In fact, they often ask, “Can you give us an example of Power Query use cases” but I hate that phrase with a passion and refuse to use case it.)

So here are some very recent examples of Power query uses from a part-time FD role that I’m doing at the moment. It’s for a small but not tiny charity with lots of systems that don’t talk to each other.

Continue reading “Power Query uses – small charity edition”
Posted on

Power Query without using code

You can get a very long way in Power Query without using code.

Power Query is a tremendously useful tool for cleaning, processing and shaping your data. You can use it to create repeatable processes and save hours of time.

It comes with a neat User Interface (UI), which gives you hundreds of buttons to click to transform your data. Need to remove junk header rows, extract account codes, calculate figures net of VAT? There are buttons for all of those things.

However, if you get stuck and search for help with Power Query, you’ll almost certainly find the explanation given in M code, which is the code behind Power Query. (Every time you click a button, you’re generating code.)

Image shows a screenshot from Power Query showing the Advanced Editor. This shows the code that makes up the query.
Advanced Editor in the View Ribbon in Power Query shows you the code you have generated
Continue reading “Power Query without using code”
Posted on

A day’s Excel training in Cardiff

Photo of the Wales Millenium Centre in Cardiff Bay on a sunny day. (Image for decorative purposes only)

Last week I delivered a day’s Excel training in Cardiff for a medium-sized charity. 

I designed this bespoke course for a group of finance and data folk who had already done my half day Introduction to power query course and had good Excel knowledge to begin with.

I always love training people who work together, because I know that there’s an increased chance that the learning will continue after the course.

What we covered

I’ve posted before that I don’t have a standard “intermediate” or “advanced” Excel course. This is because clients have different needs, and different starting points. I don’t want to waste time showing you stuff you already know, or are never going to need.

But as an example of what could be included in a day’s bespoke Excel training, let’s look at last week’s Cardiff session. I had previously circulated a questionnaire to the delegates to understand what they already knew and what they wanted to find out more about.

We revised key building blocks like Tables, and concepts such as number vs text. We then went onto some upgrades. For example, we went through XLOOKUP and the many ways it’s better than VLOOKUP.

And we also covered lots and lots of new Excel content.

Some of this we did via brief examples – such as REGEX, Checkboxes and TRANSLATE. (It was interesting to see how it copes with Welsh!)

Dynamic arrays were completely new to everyone, so we spent a bit of time looking at FILTER, UNIQUE and GROUPBY, and also discussing what made dynamic arrays different.

We then finished with a session on dynamic charts, including some data visualisation tips.

Throughout the day I shared examples of useful shortcuts and navigation tools to make everyday work in Excel a little bit more efficient.

Book me for Excel training in Cardiff (or Newport)

I can design and deliver a day of bespoke Excel training in Cardiff (or Newport) for £875 (£825 for not for profits).  I’m based in Bristol so Cardiff is a really easy trip for me and I always enjoy spending time in Wales.

Please get in touch via my Contact page to discuss your needs in more detail.