Archive for Training Videos

Use Power Query to Compare Database Records

I was helping a user on this week. The user had a list of service desk tickets for multiple customers and the requirement was to identify when a customer opened a second new ticket within 7 days of closing a previous ticket.  This is not an uncommon scenario so I thought it would make a good blog article.


One of the challenges that new users have when using Power BI is to decide if they should use Power Pivot (DAX modelling) or Power Query (PQL shaping) to solve each problem.  And if the answer is DAX, then they also need to decide if it should be a measure or calculated columnThere is no one single correct answer to these questions and it always depends on the circumstances.  I generally recommend to shape your data properly prior to loading to Power Pivot, that way the DAX formulas are easier to write and the reporting database will be more performant.   I looked at the options for this problem and decided to use Power Query to shape the data before loading.  Power Pivot and DAX are not really designed to do ordinal row comparisons, so this task is better handled with Power Query.

For this particular problem I thought I would produce a video that demonstrates one way in which this problem can be solved.  Here it is.

My Online Power Query Video Training

One of my new year’s resolutions was to baton down and create an online Power Query training course.  This has been on my to-do list for almost 2 years now and I am well under way.  The video above is a sample of my video course.  I start from the beginning and walk the viewer through the foundation skills they need to know to be a Power Query guru.  Towards the end of the course I will have a stack of videos like the one above where you can broaden your awareness of how you can use your new skills in real world situations.  If you would like to sign up and be notified once the course is finished (no obligation), then please enter your contact details in the form below.

Power Query to Combine Web Pages

Level: Intermediate

There was an interesting question this week on 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 longer than simply recording a video.  So instead of a long post, I have embedded a video below from my YouTube channel.

First the Problem

Take a look at this web page

It contains all the public holidays for the calendar year 2016.  But there are 2 similar web pages with the same structure but different results

How to do it in Power Query

I’m sure I first learnt this technique watching Miguel Llopis from Microsoft, probably at PASS SQL BA Conference in 2015.  The technique can be reused in many different scenarios like this one. The trick is virtually identical to the technique I showed in this post about combining multiple Excel workbooks into a single query. The solution works equally well in Power Query for Excel as well as in Power BI (Get and Transform). The steps are

  1. Build a query for one of the sets of data
  2. Turn the query into a function using the simple parameter step
  3. Modify the query to use the parameter instead of the hard coded set of data
  4. Create a table with a column of the parameters you want to use (in this case the data must be text, not numbers).
  5. Add a new column that takes the text from the first column and processes the function for each row.
  6. Expand the column into a single table.

Here is a video showing how easy it is to do.

Seems there is a special birthday this weekend.  Should be fun!

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 to share Power Pivot workbooks.

SharePoint Enterprise is very powerful, but there are some things that I think you need to do to make it a positive user experience.  My presentation covers what I have learnt over the last few years on this topic.  My objective is always to get away from the generic look and feel (shown on the left below) and instead deliver a optimised web browsing experience (shown on the right below).


I have now made a video of my presentation available online to anyone that would like to watch it. Read More

Use Simple SQL to Manage your data in Power Pivot

When I started learning Power Pivot, I learnt there are 2 ways to import data. There is the “standard way” that uses the table import wizard – good for us Excel folk. There is a second way that uses the Query Editor to write some SQL code and extract the data you need direct from the database. The problem is that you need to know SQL to be able to do this. So right from the start I decided this second approach wasn’t a very useful tool for the typical Excel user.
import choices

Recently though I have come across some real life challenges that I needed to solve. I have a very large “master” workbook that has all my measures and all my data (for several years) loaded – it is almost 400 MB and contains more than 40 million rows of data. I keep a master copy so I don’t have to re-write all these measures every time I have a new request for a report. But on the flip side, if someone wants a report that say contains just 10 products, I don’t want to send them the entire 400 MB workbook. Read More

powerpivot(pro) University Australian Training Dates

live training Australia with map
Powerpivot(pro) University has now arrived in Australia.  I have scheduled PowerPivot training classes for Adelaide, Brisbane, Melbourne and Sydney in late Oct and early Nov this year.

This PowerPivot for Excel training was developed by one of the best

Rob Collie has spent almost 20 years dedicated to Microsoft Excel with most of the last 10 years being totally focused on PowerPivot for Excel.  Rob was the Program Manager at Microsoft responsible for the development of PowerPivot v1, so he is better placed than most to know how it works.  Since 2010, Rob has been developing his knowledge and approach to training PowerPivot from the perspective of the user as a consultant and prolific blogger.  His deep knowledge of the PowerPivot along with his extensive consulting experience have helped him develop one of the world’s best approaches to training PowerPivot.

Your Australian trainer is a career expert in practical data use

Read More

Rob Collie’s powerpivot(pro) University coming to Australia

Next week is going to be a really exciting week for me – I am off to Cleveland Ohio to participate in this powerpivot(pro) University training course hosted by Rob Collie.  The primary purpose of my trip is to become accredited in the delivery of Rob’s world-renowned approach to teaching PowerPivot for Excel.

I first stumbled upon PowerPivot when working as the BI Director for The Coca-Cola Company in Asia Pacific.  I was looking for ways to deliver fast and cheap dashboards for my user community.  I discovered PowerPivot for Excel and I couldn’t believe the fabulous capabilities and its tight integration with SharePoint. Since then I have learnt A LOT about how to get the best out of the tools from Rob’s online training, Rob’s book DAX Formulas for PowerPivot, and most importantly from experience with working at The Coca-Cola Company, with my clients and their real world problems.  But I have also learnt that it can be hard to get your head around some of the concepts, and that learning from instructor lead training can be a real accelerator of skill development and knowledge retention.

I am a true believer in the fundamentals of Rob’s approach to using PowerPivot.  As a Self Service BI evangelist it is natural for me to want to share what I have learnt from Rob with others about the best way to get value from these tools.  And what better way to do that than to become accredited in Rob’s material and then deliver this powerpivot(pro) University training here in Australia.  After all, unfortunately most people can’t travel to the USA to do the course live with Rob.

PowerPivot Training Australia

If you would like to see the course outline for what is offered next week in Ohio and get a preview of what is coming to Australia, you can take a look at the course outline.


If you would like to register your interest to attend this training in Sydney around Sep/Oct 2014 you can use the form below.  If you live in another Australian city and are interested in the course, please let me know that too.  If there is enough interest, I will extend the training to other cities.

[contact-form-7 404 "Not Found"]