Archive - Blog Posts on PowerQuery

Cross Join with Power Query - Power Query is still very new, so there is a lot to learn.  I like to mix my learning up by doing some formal learning (such as reading a book like M is for Data Monkey) as well as “on the job” learning.  What I mean by “on the job” learning is when I go […] Read More
M is for Data Monkey - Digital Edition
M is for Data Monkey Book Review - The long awaited book from Ken Puls and Miguel Escobar has just been released in digital edition.  I read this book over the weekend and wanted to share my experience with anyone considering buying this book or wanting to learn more about Power Query. This book is 220+ pages of Power Query information that covers […] Read More
Power Query Combine Multiple Files in Folder – another method - Level: Beginners I have blogged before about how to combine multiple files in a folder using Power Query.  The way I have always done it is to use a Function, and this is not hard to do if you follow the instructions I provided on my last blog on this topic at Recently I had a […] Read More
Creating a Desensitised Copy of a Power Pivot Workbook - Level: Beginners I have been working with a client here in Sydney were we are producing some Human Resource (HR) reporting tools to analyse what is happening across the business.  HR data is quite often very sensitive because it can contain personal information as well as salary information.  A lot of the calculations that are […] Read More
Solved: Power Query Issues with Power Pivot – Exception from HRESULT: 0x80020009 - level: Intermediate I have written a couple of posts here and here about the problems and dangers of using Power Query with Power Pivot.  Well I am very pleased to say that I now fully understand the problem, how it is caused and how it can be avoided. Edit: Nov ’15.  Microsoft has released a […] Read More
Power Pivot Calendar Tables - Level: Beginners Technically you don’t need a calendar table to use Power Pivot, however in all but the most basic use cases you really should use one.  Reasons to use a Power Pivot calendar table include: A calendar table allows you to use the filtering power of Power Pivot to make your reports fast and snappy. […] Read More
Start a Second Instance of Excel - Excel is a bit like a drug for me – the more I use Excel, the more I need to use Excel.  I work on a lot of Power Pivot workbooks these days and sometimes these workbooks need to run a refresh or recalc that can take a bit of time.  Increasingly I am finding […] Read More
Excel is the Swiss Army Knife of Business Intelligence - I was recently asked by an Industry Excel expert what made Excel such a compelling product for Business Intelligence, Reporting and Analysis.  As many people know, I have worked in IT as well as commercial business roles over the years, so I have seen ‘both sides’ of the story and hence I am qualified to […] Read More
Create Incremental Backups when using Power Query - Edit 16 May 2015: I now know the exact cause and cure for this problem.  See this post here This backup tip is still useful though. This is a follow up to this post where I talked about Power Query and warned to take frequent backups when editing your Power Query Workbooks.  The issue is […] Read More
Power Query Performance Improvements – Test Drive - Microsoft has just released the March ‘15 update to Power Query.  On the official blog, MS called out 3 things: Performance improvements Microsoft Dynamics CRM Online connector New transformations Performance improvements?  You have my attention.  I have found that Power Query (and Power Pivot for that matter) are both slow to load Excel files.  The […] Read More
Use Power Query to Create Power Query Documentation - I wrote a blog post on last week on how to create a standard Power Pivot calendar using Power Query – from scratch.  I was doing some research this morning on how I could extend this concept to create a 4/4/5 Calendar (which is often used in the Retail Industry).  I was looking at […] Read More
How to Correctly Install Power Pivot for Excel 2010 - As most subscribers of my blog would already know, I am a professional trainer of Power Pivot here in Australia. I do a lot of work for Corporations at their own premises with their own PC equipment. Not every company has made the move to Excel 2013, and in fact I sometimes find companies have […] Read More
Power Query Issues with Power Pivot - Edit 16 May 2015: I now know the exact cause and cure for this problem.  See this post here PhilC at asked me for advice on the best way to use Power Query with Power Pivot.  If you have come to this post via Google, it is probably because you have had issues and […] Read More
2015 Australian Training Dates – Power Pivot for Excel - I have now locked all the training dates for my Power Pivot for Excel Australian Training public classes.  You can find full details of the dates here. Why you should attend my Power Pivot for Excel Training I am affiliated with Rob Collie from in the USA and I deliver training material developed by […] Read More
How to access PowerPivot on iPad - One thing I find when working with my customers and meeting PowerPivot users is that a lot of people want to know how to access PowerPivot when they are mobile.  Most frequently people want to access PowerPivot reports on iPad, however in many cases just presenting data for PC users in an ‘easy to consume’ […] Read More
Data Model for WordPress Data
WordPress Reporting using Power Query - I recently created a Book Shop on my WordPress website using WooCommerce.  The next logical thing I wanted to do was to get an extract of all my orders/invoices that have been sold through the shop into Excel.  I couldn’t find a way to do this in the WordPress UI or via a free plug […] Read More
Chris Webb’s Power Query Book Review - I have been waiting for this book Power Query for Power BI and Excel by Chris Webb for some time and I am pleased to advise it is a great book for a broad range of people with anything from zero to medium skills in Power Query. I fall into the later category and I found […] Read More
PowerPivot Forum Recruitment Drive - Back in early May I realised that there was not much in the way of a dedicated forum for PowerPivot users, especially for the general user.  There are some specialised forums out there for Microsoft Developers but not much for the average user.  As a result I went ahead and set up a new PowerPivot Forum and […] Read More
Matt's curated best self serve BI content
Deep Dive into Power Query M Language - In this session, Matt Masson and Tess Palmer do a great job of showing everyone the depth and power of the Power Query M language that is the backbone of Power Query – it makes my list. Clearly they didn’t expect too many people in the audience to be able to follow them along the […] Read More
A smorgasbord of Microsoft Self Serve BI at TechEd 2014 - Last week in Houston Texas, Microsoft held TechEd 2014 (North America, 12-15 May). I was browsing the videos from this conference and wanted to share the wealth of material that is available for anyone to view. If you just filter on the “Data Platform and Business Intelligence stream“, there are over 40 sessions focussed on […] Read More