Archive - Blog Posts on Power Query

What is the Best Training for Business Analysts? - Level: Beginners The rate of change in the tools available to a Business Analyst has never been faster than it is in 2016.  The market is changing so quickly that many Business Analysts (BAs) are not even aware of the great new tools that are available to help them succeed in their jobs and careers.  […] Read More
Combine Excel Workbooks with Power Query – Method 2 - Edit: Feb 2017.  Microsoft has released an update that solves the problem described in this article.  This article still has learning value however you should also take a look at the announcement from Microsoft here https://blogs.office.com/2017/02/08/january-2017-updates-for-get-transform-in-excel-2016-and-the-power-query-add-in/ There are many ways to combine data from multiple files into a single file/table in Excel.  In my first article […] Read More
Combine CSV Files with Power Query - This is the second of a series of articles where I explain different methods of combining data from multiple files into a single workbook/table.  In the first article I described how to use a function to combine data from identical Excel Workbooks into a single file using Power Query.  Next week I will cover a […] Read More
Combine Excel Workbooks with Power Query – Method 1 - I have blogged about these concepts before but thought there was value in creating a couple of clear blog posts covering step by step instructions on 2 good ways to combine multiple Excel Workbooks with Power Query.  Both of these approaches require that the Excel files are identical in structure (same column names, same number […] Read More
Convert Monthly Budget into Weekly Buckets 445 Calendar - I had an interesting problem this week with a client that uses a 445 calendar.  The problem is that they had some monthly budget figures that they wanted to track against actual sales on a weekly basis.  We agreed to do a straight line split of the budget into weeks (ie the weekly budget will […] Read More
Traditional Pivot Tables using Power Query – Can Do - Power Pivot and Power Query are the best things to happen to Excel since the invention of Excel itself.  Once you start using these tools there is no turning back.  But someone asked me the other day “Is it possible to use Power Query with a traditional pivot table?” (ie not with Power Pivot).  Indeed […] Read More
Microsoft Fixes A Couple of Excel Bugs - Microsoft has released a couple of long over due fixes to some long standing Excel 2013 bugs, both of which have been quite painful in their own way.  Unfortunately for me the timing couldn’t have been worse.  I literally just placed the order for the print run of my new book Learn to Write DAX, […] Read More
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 as an Audit Tool - Level: Beginners Recently I was working for a client that required the matching of a list of data sent from a customer against some internal business data.  This type of data matching is actually an audit process that I would have historically done with MS Access, but it occurred to me that Power Query would be […] Read More
Brisbane and Sydney Training – it’s not too late to register - PowerPivot for Excel Live Training in October I have 2 training events scheduled in October and it is not too late to register. Brisbane – next week Mon 12th and Tues 13th October This one is next week, but there are still a couple of places left if you have time to attend (despite a late […] Read More
Load Power Query Directly to Power Pivot with Excel 2010 - Level: Beginners When you write a Power Query workbook in Excel 2013, you are given 4 choices of what to do with results.  One of the choices is to load the resulting query directly to the Power Pivot data model.  This choice does not exist in Excel 2010.  It is reasonable therefore to think that you […] Read More
Power BI Desktop Aug 15 Update is a Winner - I am really looking forward with anticipation and excitement about the future of http://PowerBI.com.  Microsoft has just released the August 2015 update to Power BI Desktop just 1 month after PowerBI.com becoming generally available (GA). Power BI Desktop used to be called Power BI Designer, and it is the tool you can use on your […] 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 PowerPivotPro.com. 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
Recommended Power Reading - Level: Beginners I love books.  Even in this world of the Internet I still like to buy a good book of structured “curated content” that takes the reader on a journey.  There are quite a few books out there about the various Microsoft BI products.  I plan to keep this page updated over time with […] Read More
Power Downloads - Level: Beginners It can be reasonably difficult to find the exact download you need for the various Power Products in the Microsoft BI stack.  Some of these products (Particularly Power Pivot) have a number of versions.  This page is provided to help you get the correct download first time.  You may like to book mark […] 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
PASS BA Conference Day 2 Wrap - Seriously, this has been the best 3 days of my life. Well except for my wedding day of course (sorry honey). There were many great sessions at the PASS BA Conference – about half of them I attended I would classify as “invaluable”. And there were many other sessions I couldn’t attend due to timing […] Read More