If You Do Anything With Data In Excel, Learn PowerQuery!

This week I’ve been digging into the wonderful world of PowerQuery and Power BI, two excellent tools developed by Microsoft that make preparing and visualising data something akin to magic.

Although I don’t yet understand PowerQuery, I have learned and experimented enough to derive value from it in less than a week. This is coming from someone who took much longer than that to learn VLOOKUP and other Excel formulas.

Another kind of transformation…

With PowerQuery, all of the data-cleansing and transformation can be done in a recorded step-by-step process, while linking to the source data without changing anything at the source, only within your own file. Not only that, but when the data is loaded into your workbook, it only loads what you pulled from the data source. If you linked a 50mb workbook but only used 1mb of the data in it, you’re only going to see your file size go up by the 1mb. Efficient!

Over the next week I’m going to try and learn the query language, because I’ve already seen an example of a table of dates being built with a simple query and I would like to be able to build similar tables myself without those tables having existed beforehand, or creating them from minimal input data such as a string of text in a file.

Having not spent as much time playing with Power BI as I have with PowerQuery, I don’t have as much to say about it. The interactivity of the charts is brilliant compared to regular Excel charts and can give stake holders instant answers to the questions they might have with the drilldown options. Self-service is a big plus point in my book because it leaves me free to analyse and investigate more new things, so I’m likely going to get more into Power BI over the next few weeks and months.

Overall, PowerQuery has really won me over this week and I look forward to using it more in my work to get the answers I want, faster. Much of the slowness I have now with PowerQuery is most likely down to lack of experience and knowledge, but even if it were no faster than the data-cleansing and transformation methods I’ve learned over the years in Excel, it still offers a more transparent and consistent approach to that type of work, so I’ll be using it more and more.

Tell me what you think

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

%d bloggers like this: