I am writing my blog post this week from San Jose, California USA live from the 2016 PASS Business Analytics Conference. For the benefit of those of you that are not familiar, PASS is the Professional Association for SQL Server. Each year PASS holds a Business Analytics Conference – it […]
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 […]
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 […]
Level: Beginners Edit: Sep 2019 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 […]
Microsoft announced another awesome Power BI feature recently. This Power BI Analyze in Excel feature was requested by Avi Singh from PowerPivotPro.com and was heavily supported as a requirement by the community. You can read more about how you can influence future releases at the bottom of this post. But […]
Level: Beginners Edit: Sep 2019 I am still astounded by the number of people I meet that have never heard of Power Query and Power Pivot. The good news is there are lots of people out there in for a pleasant surprise. I think Power BI will solve the “lack […]
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 […]
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 […]
Level: Beginners All of the learning examples in my books Supercharge Power BI and Supercharge Excel are built on an Adventure Works database that has a single data table. But Power Pivot is a very capable tool for building reports that combine multiple data tables. Although it is very capable, […]
Ken Puls has kindly offered a special 35% discount (first 5 registrations only) for the upcoming XLSouth Sydney Conference next week. The conference is 2 days and features global Excel MVP/Experts talking on one of my favourite topics – Microsoft Excel The Sydney Conference is on Mon 7/Tues 8 March. […]
DAX Studio is a fabulous free tool that allows you to directly query your Power Pivot/Power BI data models. It is not immediately obvious (particularly to beginners) exactly how using DAX Studio can add value. This blog post today is a simple reference to help anyone that uses Power Pivot get […]
Level: Beginners In this post today I am providing a small extract from one of the chapters in my book Learn to Write DAX. It has been just over 2 months since the global release of the book. As at this time of writing, I have 6 customer reviews on […]
Level: Beginners It is now possible to share your (public) reports from PowerBI.com with anyone over the internet for free (currently a free preview), without the need to manage licencing, logins etc (see demo below). Microsoft has been promising this feature for a couple of months now and the speed […]
I was recently a presenter at the PASS BA Webinar Marathon – Dec ‘15. This was an online Webinar with a total of 7 separate presentations from various experts across the industry. My topic was “Moving from Excel to Power BI – A Business Analyst’s Perspective”. The session was recorded […]
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 […]
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 […]
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 […]
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 […]
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 […]
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 […]