Archive - Blog Posts on Power Query

Shaping vs Modelling in Power BI - Level: Beginners Power Pivot, Power Query and Power BI are 3 products that are closely related to each other and were all built for the same purpose – enabling Self Service Business Intelligence.  I first learnt to use Power Pivot for Excel, then Power Query for Excel, and finally Power BI.  But there is a […] Read More
Measures on Rows – Here is How I did it - Level: Intermediate You may or may not be aware that it is not possible to put Measures on rows in a Matrix in Power BI. But I came up with a trick that makes it possible, so read on to find out how. Measures Can Only be Placed on Columns First the problem. The only […] Read More
LASTNONBLANK Explained - Level: Intermediate Last week at my Sydney training course, one of the students asked me a question about LASTNONBLANK.  This reminded me what a trickily deceptive function LASTNONBLANK is.  It sounds like an easy DAX formula to understand, right?  It just finds the last non blank value in a column – easy right?  Well it […] Read More
Sydney Modern Excel and Power BI User Group is Growing - In October last year, Iman Eftekhari and I set up the Official Microsoft Power BI User Group (PUG) here in Sydney Australia.  Since then we have gone from strength to strength and as of the time of this post the user group consists of a total of 330 members.  If you live in Sydney and […] Read More
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 PowerPivotPro.com.  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 excited to share with you a new Excel Add-in that was authored by Bertrand d’Arbonneau and has recently been made available via SQLBI.com 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 http://powerpivotforum.com.au 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 http://powerpivotforum.com.au 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