Conditional Columns in Power BI Desktop

Conditional Columns in Power BI Desktop

Microsoft recently announced a new conditional column feature in Power Query (Power BI Desktop version) that warrants a blog post.  This feature makes the process of creating banded columns much easier for the average user.  I blogged about banded columns using DAX here, and now there is this second, potentially easier approach.  In the demo below, I will add a new column of “age bands” jus...
Power BI Personal Gateway Explained

Power BI Personal Gateway Explained

One of the many excellent sessions I attend this week at the PASS Business Analytics Conference in San Jose was a session titled “Get Latest Insights by connecting your data using Power BI Content Packs and PBI Gateways”.  The title was interesting but the content presented by Dimah Zaidalkilani and Theresa Palmer-Boroski (both Program Managers on the Power BI team at Microsoft) was truly exc...
PASS Business Analytics Conference 2016 – San Jose CA.

PASS Business Analytics Conference 2016 – San Jose CA.

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 is the premier conference for Data Analyst Professionals (as opposed to IT Professionals who are catered fo...
Sydney Modern Excel and Power BI User Group is Growing

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 would like to join the User Group, then we would love to have you on board. Objectives of the User Group The...
Banding in DAX

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 permanent link to the topic.  I first learnt about the DAX banding technique from Alberto ...
What is Power BI

What is Power BI

This 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 is also the most important to understand (as I will explain below). Why Power BI is so Important Microsoft Power BI is the single most important thing to happen in the data analytics m...
Power BI Analyze in Excel – What You Need to Know

Power BI Analyze in Excel – What You Need to Know

Microsoft announced another awesome Power BI feature recently – Analyze in Excel.  This 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 let’s get straight into this great new feature. Analyze in Excel is available for the fr...
What is Power Query

What is Power Query

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 of awareness of Power Query” problem over the coming months and years.  Power BI is much more visible as can been seen in this Google Trends chart that I first created...
Many to Many Relationships in DAX Explained

Many to Many Relationships in DAX Explained

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 a rocket scientist to understand these concepts, it is more that they are not naturally intuitive.  You si...
Quadruple Nested SUMX or CROSSJOIN

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, but it then needs to be transformed so it can be used in the model. There is a ...
Multiple Data Tables in Power Pivot

Multiple Data Tables in Power Pivot

Level: Beginners All of the learning examples in my book Learn to Write DAX 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, the logic of how to combine multiple tables is different in Power Pivot than most Excel users will be familiar with.  Thi...

XLSouth Sydney 7/8 March – 35% Discount

Ken Puls has kindly offered a special 35% discount (first 5 registrations only) for the upcoming XLSouth Sydney Conference next we...
Calculated Columns vs Measures in DAX

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 and also help many more on various forums.  The number 1 mistake I see in self taught DAX students with an Excel background is the use and over use of Calculated Columns. Attracted Like a Magnet As I mentioned i...
Getting Started with DAX Studio

Getting Started with DAX Studio

Level: Beginners 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.  I often refer people on various forums to use DAX Studio but then lack a suitable reference to refer them to so they can get started.  This blog post today is a simp...
What is the Best Training for Business Analysts?

What is the Best Training for Business Analysts?

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.  Regular readers of my blog will already know what is hot, but let me cover off a bit of background to what is hap...
Extract from my Book Learn to Write DAX – Calculated Columns

Extract from my Book Learn to Write DAX – Calculated Columns

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 Amazon from readers that have already purchased the book and had first hand experience reading and learning from it.  All 6 reviews have a 5 Star rating. Read some...
Power BI Public Story Telling is Pure Genius

Power BI Public Story Telling is Pure Genius

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 in which these new features continue to arrive continues to amaze.  The official name for this new feature is Power...
Moving From Excel to Power BI–PASS Presentation

Moving From Excel to Power BI–PASS Presentation

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 and is available to view for anyone that is interested. My objective was not to cover a...
A Fabulous new Excel Add-In for Power Pivot

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 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 already be done using various bespoke existing tools and procedures, but there...
Combine Excel Workbooks with Power Query – Method 2

Combine Excel Workbooks with Power Query – Method 2

There are many ways to combine data from multiple files into a single file/table in Excel.  In my first article in this series a few weeks ago, I described how to use a function to combine data from identical Excel Workbooks into a single file using Power Query.  Then last week I showed how to combine multiple CSV files into a single workbook/table using the “import from folder” method.  Th...

Bring your data to life - Let's discuss how Matt can help!