Archive - Blog Posts on Power Query

Banding in DAX - Level: Intermediate As regular readers of my blog would know, I enjoy helping people on forums who need help with DAX, Power Query or Power BI.  Recently I have answered quite a few questions for people that could benefit from a DAX “Banding” solution and hence I have decided to write this post as a […] Read More
What is Power BI - Level: Beginners What is Power BI? Microsoft Power BI expert, Matt Allington, explains This ‘What is Power BI’ post is the third in my “what is” series having previously blogged about “What is Power Pivot” and “What is Power Query”.  This topic is the hardest of the three to give a straight answer, however it […] Read More
Many to Many Relationships in DAX Explained - Level: Advanced (Level: Advanced, but explained in detail so everyone can understand) There is a lot to learn in DAX if you want to be a ninja.  A couple of the more complex and important things to learn and understand are filter propagation and context transition.  It is not so much that you need to be […] Read More
Quadruple Nested SUMX or CROSSJOIN - Level: Intermediate I had an interesting problem with a client last week where I built a data model to calculate rebates payable to customers based on their sales turnover.  There were a few challenges that I am going to cover below, including: There needs to be a user friendly way to capture the rebate data, […] Read More
Calculated Columns vs Measures in DAX - Level: Beginners It has been 16 months since I wrote my blog post 5 common mistakes of self taught DAX students at  I train a lot of people at my training courses, including my Power BI online training course, and also help many more on various forums.  The number 1 mistake I see in […] Read More
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
A Fabulous new Excel Add-In for Power Pivot - I’m really exited to share with you a new Excel Add-in that was authored by Bertrand d’Arbonneau and has recently been made available via The Add-in called Power Pivot Utilities combines a number of existing tools into a single new tool bar in Excel as shown below (Excel 2013 and 2016 only). Everything could […] 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
Collections Reporting Using Power Pivot - Level: Beginners I was helping a user at with a question about collections reporting and thought it would make a good blog post. There are a few interesting concepts involved which I will explain as I go along. The scenario is as follows. We have 2 tables – one table contains all invoices (the […] Read More
Power BI Visuals Gallery is a Game Changer - Level: Beginners No software company can build every single feature that everyone wants to see all at once.  Ultimately there has to be some prioritisation, and along with the prioritisation comes compromises and ultimately disappointments for some (many?).  Back in July 2015, Microsoft started a chain of actions and announcements that will prove to be […] Read More
Learn to Write DAX – eBook available to purchase - Level: Beginners I’m super excited.  Today is the day I have been dreaming of for almost a year when I first decided to write my book Learn to Write DAX – A practical guide to learning Power Pivot for Excel and Power BI. This book is exactly what Excel users need to get stared with […] 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
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
Using SharePoint Enterprise with Power Pivot - As regular readers of my blog would know, I attended the PASS Business Analyst Conference in Santa Clara California this year (April 2015).  Here is the link for next year if you are interested.  While I was there, I had the pleasure of presenting on one of my favourite topics – how to use SharePoint […] Read More
DAX Query Tables in Excel 2010 - I was helping a participant at with a problem. Long story short, she needed to return a table to Excel from a data model – not a Pivot Table. This concept can be a bit strange at first – let me explain. Tables – Not Pivot Tables A Pivot Table is a tool that […] Read More
Multiple Relationships Between Tables in DAX - Level: Beginners I helped a couple of people this week on the topic of creating multiple relationships between two tables.  There is not a lot of information on this topic on the web so I thought I would write about it here. Power Pivot is not the Same a Relational DB For those of 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  Microsoft has just released the August 2015 update to Power BI Desktop just 1 month after 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
The Optimal Shape for Power Pivot Data - Level: Beginners One of the topics in my book “Learn to Write DAX”  talks about the optimal shape of your data tables.  It is such an important topic that I have decide to share some modified extracts from the book here. When I say “shape” of your data, I am talking about things like how many […] 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