Cumulative Running Total Based on Highest Value

Cumulative Running Total Based on Highest Value

Level: Advanced I’ve helped a couple of people out on various forums on this topic over the last week, and that is normally a good sign that I need a blog post on the topic. The scenario is to create a Pareto cumulative running total based on the top products, customers or whatever.  This is a bit tricker than a simple YTD running total, as the “order” of the best to worst products (or...
Top 10 Tips for Getting Started with Power BI

Top 10 Tips for Getting Started with Power BI

Level: Beginners I really love Power BI, and I have learnt so much over the last 12 months that sometimes it is easy to forget the challenges I had in getting started. Today I am sharing my top 10 tips on how to get started with Power BI. Build Your Reports in Power BI Desktop, Not the Service One thing that can be confusing is that there are 2 ways you can build reports – Power BI Deskt...
Conditionally Refresh Queries in Power BI

Conditionally Refresh Queries in Power BI

There was a great new feature that snuck in unannounced to the July release of Power BI Desktop.  It was only a tweet by my colleague Imke Feldmann from http://www.thebiccountant.com/ that I was even aware it was there. Include in Report Refresh When you look in the Queries pane in Power BI Desktop, you will see your list of queries in the Queries pane on the left hand side.  Simply right clic...
Convert a Cell Value into a Column with Power Query

Convert a Cell Value into a Column with Power Query

Level: Advanced Power Query I am in the process of finalising some action items after a great Power BI, Power Pivot and Power Query training course in Melbourne.  During the training we looked at a scenario for one of the attendees where they needed to combine data from multiple Excel files into 1 big table.  I have blogged about this before in 3 articles starting here. In this specific ...
Happy First Birthday Power BI

Happy First Birthday Power BI

One year ago today on the 24th July 2015 a brand spanking new BI baby was born.  And its proud parents named the new baby Power BI, and soon Power BI would become known to everyone around the world.  Today is therefore a day to celebrate what a great first year it has been for this bambino.   From Little Things, Big Things Grow It is no secret that I thought the baby was just a little bit...
Power Query to Combine Web Pages

Power Query to Combine Web Pages

Level: Intermediate There was an interesting question this week on http://powerpivotforum.com.au asking if there was a smarter way to user Power Query over multiple identical web pages to scrape the data in a single query.  I have been meaning to blog about this for a while, so it is a great opportunity for a mini-Friday post. The process of typing all the steps on how to do this is a lot l...
Measures on Rows – Here is How I did it

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 way that you can use the Power BI Matrix visualisation (at this writing) is to place the measures on the columns as shown below....
Direct Connect from Excel to Power BI Service

Direct Connect from Excel to Power BI Service

Today Microsoft announced a great new feature that allows you to direct connect FROM Excel TO Power BI and not the other way around.  This simple change really streamlines the integration experience between Excel and the Power BI Service, and makes Power BI even more like you own personal SSAS server. There are 2 immediate use cases that come to mind Personal SSAS Server It is now easier...
Top Tips for Sharing Content Using Power BI

Top Tips for Sharing Content Using Power BI

I have learnt a lot about sharing Power BI content over the last year since Power BI hit the main stream.  A lot of my learning has come from trial and error, and there are lots of things I wish I knew before I started.  Today I am sharing my top tips about how you can share, and when you should use each of the various methods It is not easy to categorise the sharing approaches into logical b...
Power Query Over a Command Screen Output File

Power Query Over a Command Screen Output File

Level: Intermediate Power Query. I spent a lot of last week helping to configure Power BI in preparation for go live for a client.  One of the important things to do when designing a Power BI solution is to make sure you have a good design for your user security access.   Today I am going to share the approach I used to track user security configuration with Active Directory Groups. This ...
Self Referencing Tables in Power Query

Self Referencing Tables in Power Query

I have had this idea in my head for over a year, and today was the day that I tested a few scenarios until I got a working solution. Let me start with a problem description and then my solution. Add Comments to a Bank Statement The problem I was trying to solve was when I download a digital copy of my bank statement, loaded it up into Excel using Power Query and then wanted to add some commenta...
LASTNONBLANK Explained

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 is a little bit trickier than that, and I am going to explain it all...
Power BI May 2016 Update is Another Cracker

Power BI May 2016 Update is Another Cracker

I was super excited by the May 2016 Power BI Desktop update as it had a stack of great new features.  Today I am covering the ones I think are the most useful. They are Conditional Formatting Nudge Objects on the canvas Quick Calcs Customisable ToolTips Improved CSV connector Conditional Formatting One thing I find curious about this new feature is that it is only available o...
Excelerator BI is Preparing to Expand

Excelerator BI is Preparing to Expand

It has been more than 2 years since I left my 25 year career at Coca-Cola to start my own Self Service BI company.  So much has changed since then, not least of which is the rise and rise of Power Pivot, Power Query and more recently Power BI.  I truly believe that Power BI is going to explode (along with Power Pivot and Power Query by association); the only question is when.  Excelerator BI...
A Double CALCULATE Solves a SUMX Problem

A Double CALCULATE Solves a SUMX Problem

Level: Intermediate I helped a member at http://powerpivotforum.com.au with a problem last week that ended with an interesting solution.  The explanation of how it worked was a bit complicated and worthy of sharing, hence this is the topic of today’s post. Count the Working Days Between Two Dates The requirement was to count the working days between the arrival date (say of a request) and ...
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 ...

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