Archive - Blog Posts on PowerPivot

Excel Keeps Crashing? Check your VBA code - I use Excel 2013 64 bit and sometimes Excel just keeps crashing on me.  It used to happen to me a lot more in the past, but after I discovered what was causing the repeat behaviour I have been able to greatly reduce how often this occurs. First a description of the problem If this […] 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
Sydney Power Pivot Training just 2 weeks away - The next Power Pivot Training course from PowerPivotPro University in Sydney is just 2 weeks away.  There is still time to register and come along and learn new skills that will make you more productive and effective in your daily job role.  Those people that have completed the course tell me that Power Pivot is one […] 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
Fill Table with Last Survey Result - I have come across this problem a few times recently. There was no immediately obvious solution that worked at first. I knew what I wanted to do but I couldn’t get it to work – but now I have a solution and I want to share it for the benefit of others. First, let me […] 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
I’m Speaking at the PASS BA Conference - I’m really excited to be speaking at this year’s PASS Business Analyst Conference in San Jose California.  I will be sharing what I have learnt in my time working as BI Director at The Coca-Cola Company about how to share Power Pivot (and other Excel) Workbooks with people that simply want to consume the data. Part […] 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
VBA to Insert a Power Pivot Table - When Power Pivot was originally released by Microsoft, it was introduced as a Plug-In to Excel 2010.  After the second version of the Plug-In was built and released, the Microsoft Build team moved its focus to writing the product into the 2013 release of MS Office.  Unfortunately they ran out of time to write in […] Read More
Power Pivot Forum now supports DAX Formatter - Well Power Pivot Forum has been live for 8 months now and has over 100 users, more than 400 posts and many thousands of page views. The Power Pivot forum continues to grow as new users are finding the need for help with their DAX problem. Recently one of the forum members (xChillout) suggested that […] Read More
What is Power Pivot and Why You Should Care - I have been meaning to write this post for a long time – today is the day!  The answer to the question “What is Power Pivot?“.  You may also have landed here by searching for “Use this workbook’s Data Model”.  Often the first time people discover Power Pivot is when they see this message in […] 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
Use Simple SQL to Manage your data in Power Pivot - When I started learning Power Pivot, I learnt there are 2 ways to import data. There is the “standard way” that uses the table import wizard – good for us Excel folk. There is a second way that uses the Query Editor to write some SQL code and extract the data you need direct from […] Read More
Should I delete or re-import a column in PowerPivot? - One thing I learnt in my early days of PowerPivot was that you should never delete an imported column from a table in PowerPivot.  The rationale was that PowerPivot compresses the columns during import, so if you delete them after import, you will lose some of the compression benefits. However I am a sceptical type […] Read More
Problems Importing Access and Excel 2010 into SQL Server 2012? - Edit 10 Dec 2015: This blog post was written to solve a problem with SQL Server import drivers, however the process of addressing the issue should also work (in reverse) for people that are running 32 bit Office and want to install 64 bit Power BI Desktop.  If this is you, read on and you […] Read More
How to Bulk Refresh PowerPivot Workbooks on your PC - There are a number of different ways that you can refresh PowerPivot workbooks automatically on a schedule. The main ones I know of are: Using Power BI Using SharePoint Auto Refresh Using some sort of custom built desktop automation tool The problem with Power BI and SharePoint The problem with Power BI is that you […] Read More