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.)

A couple of tricks for avoiding code completely

In my Introduction to Power Query course I teach a couple of techniques for more advanced data cleansing without using code. The first is the wonderful “Column From Examples”. Here you can simply give Power Query an example of the data you want to see and let it work out the code.

Another non-code trick I use a lot is the Conditional Column for extracting types of data from a mixed use column. For example, if you have both dates and account codes in the same column and you want to separate them out but keep both sets of data. You can combine Conditional Column with Fill Down to do some neat data cleansing without using code.

The limits of pressing buttons

I always say that I can do about 90-95% of what I want in Power Query without going near the M code.

The area that I’m most likely to need code for is combining data, particularly where column headers are inconsistent or might need to expand in future. The built in data combination options in Power Query don’t work unless the data is very consistent and in the real world, it mostly is not.

This isn’t an easy area to teach to beginners, because there are many many ways to combine data in Power Query with code, and the route you go down will depend on the exact circumstances you face. I might try to write a flow diagram of this at some point!

The other main areas I use code for is around connecting to Sharepoint and also incorporating file path information into a query.

Further resources

I wrote this post exactly a year ago about learning more Power Query. Since then both Mark Proctor and Chandeep Chhabra have released Power Query / M code courses within their training offer, and they and other people continue to add really helpful videos to help us move beyond the user interface.

If you’re stuck on a particular problem, I can also help. Consultancy starts at just £350 for a half day session, which may be all you need to sort your particular issue. Contact me to find out more.