Matt Allington

Matt Allington is a Microsoft MVP specalising in Power Pivot, Power BI and Power Query Consulting and Training based in Sydney Australia.

Power Query As An SSIS Alternative

Power Query as an SSIS Alternative

By Matt Allington

One of the many things I love about Power Pivot and Power Query is that these tools have put BI into the hands of users and there is no longer a reliance on highly technical IT skills such as MDX to enable BI reporting.  This is game changing for all the reasons you have read about on this blog over the years.  Over time, as I have used Power Pivot more and more, I have found increasing value in learning about and working with SQL Server as a tool to manage my data. I know the thought of using SQL Server can be a bit daunting to Excel users (as it was to me at first) but it is actually not that hard to setup and use.  Anyone can download and install a free version of SQL Server Express on their PC, and there is a lot of learning material on YouTube to get you started.   But there is one thing about SQL Server that I have found to be much more difficult than with other more consumer friendly databases like Microsoft Access – that is how to get the data loaded.

Getting Data into SQL Server

SQL Server uses a tool called SQL Server Integration Services (SSIS) to load data.  This is a very powerful piece of software, however I have found it is quite difficult to learn how to use it (as a casual user).  I have no doubt that SQL Server professionals have no problems, but there is a big difference between firing up SSIS once every 3 months to load a couple of tables and working with the tool each day.  I have never had the time to master SSIS and I cringe each time I have an issue that requires me to edit my SSIS packages.  In the past I have outsourced this work to a developer as it just wasn’t worth the inefficient use of my time to try to work it out myself.

Enter Power Query as an SSIS Alternative

Now before I get a million comments from professional SQL Server experts, I am not suggesting that everyone should swap out SSIS for Power Query.  I understand the importance of an enterprise strength tool like SSIS and I know it is a great tool for that purpose.  I am just talking about people like me that work in the Self Service BI/Excel space, use SQL Server as a tool, but have only limited opportunities to work with SSIS.  This is my situation, and if this also sounds like you then Power Query is a great alternative.

The benefits of Power Query over SSIS include:

Read the Rest
Nested SUMX Or DAX Query?

Nested SUMX or DAX Query?

by Matt Allington

I was helping a friend out recently with an interesting problem.  It all started with a SUM( ) that wasn’t behaving. It quickly became a SUMX( ) problem but evolved into a DAX Studio/Query problem.   Let me explain.

The Root Problem – Same Store Sales

Many retail businesses open and close stores throughout any given year.  This creates a problem because it is difficult to determine if business growth is vertical (increased sales within existing stores) or horizontal (expansion of the store base).  It is common to do analysis on “same store sales” where you only include stores that had sales for the entire period this year and also last year. There are quite a few posts already on this topic on PowerPivotPro.com, but this is a good opportunity for me to talk about SUMX and also DAX as a query language.  I have reconstructed the scenario with some test data.  You can download the workbook here if you want to take a look.

I started off with some base measures as follows:

A. Total Sales:=SUM(Sales[Extended Amount])
B. Total Sales LY:=CALCULATE([Total Sales], 
      SAMEPERIODLASTYEAR(Calendar[Date]))

There are a few different ways to work out sales last year, but I have used SAMEPERIODLASTYEAR above and this works just fine in this instance.

Read the Rest

Consolidated Worksheets with Power Query

by Matt Allington

The more I use Power Query, the more I want to use Power Query – and of course the more I learn.  I have seen quite a few references on the Web to “consolidating multiple Excel Workbooks into a single table”, but I came across a problem working with a client recently where I had to undo someone else’s ‘helpful’ partitioning of data into separate worksheets in the one workbook.  You may have come across the problem yourself.  Someone has been collecting data over time, and they start a new sheet every month.

image

Well intentioned but actually quite unhelpful.

The procedure I use here to bring this all back together in a single table is the same basic concept as consolidating multiple workbooks, however I have learnt a thing or two since the first time I copied someone else’s pattern on how to do this.  Specifically I have learnt the correct and easiest way to create and work with Power Query functions.

The basic process/pattern is:

  1. Create a query to import a single sheet of your data.  Do all the transformations you need here.
  2. Convert the basic query from step 1 above into a function (the easy way) while keeping some backwards capability.
  3. Create a second query that uses the function from step 2 and consolidates all the objects (Sheets in this example), but the same principles apply for any other object.

There are other approaches you can use to do this (as is often the case with Excel) but this procedure demonstrates how easy it is to write Functions if you understand the basic concepts.

First Create a new blank workbook edit a single sheet

Go to Power Query Menu\From File\From Excel.  You will see in the navigator the list of sheets.  Just select any one of these sheets in the list.   For this process to work, the column structure of the sheets needs to be the same.  

Read the Rest
Power Query Super Charges The Internet

Power Query Super Charges the Internet

by Matt Allington

I guess most of the readers of PowerPivotPro.com will already have a pretty good idea that Power Query is awesome.  I spent time recently thinking about how Power Query has really opened up the data on the Internet and made it more accessible to us all.  It reminded me of something I worked on a couple of years ago when I first discovered Power Query. I want to share my journey from back then to demonstrate the point about how Power Query really “Super Charges” the Internet – and because the example is just cool.

First the background

The long story short is that I was frustrated one day when SalesForce.com released its “Spring” release of its software.  Now as someone that lives in the southern hemisphere, giving a software release a “northern hemisphere” season for a name is totally meaningless – let alone completely confusing.  Anyway in my rage (and spare time), I set about trying to find out what percentage of the world population experiences “southern hemisphere” seasons (go figure why! – I’m just like that sometimes).

Anyway, in my journey I found a link to a NASA website that provided tables of data with the total population of the world at every 1 degree of longitude and every 2 degrees of latitude.  So basically it was just a big text file 360 columns wide and 180 rows long, with each number being the population in that 2 degree square surface area of the earth – just what I needed to check the population in each hemisphere.

Read the Rest

Jump between pivots with context

by Matt Allington Today I am sharing a trick that I have used a number of times for clients – it allows you to use simple VBA to jump from one pivot table to another, and when you arrive at the second pivot table it is automatically filtered to show the context for the data you want to see.  It is a great user experience.

Here is a sample use case and demo

You are browsing and drilling into a product hierarchy looking at the change in performance vs last year.  You drill down to the product level and want to see the weekly sales for that particular product.  You simply click on the “show detail” button and you are taken to a detail page that shows you the details for the specific product you were looking at.  jump with context   Let me share the process of how to build this interactive report.

Use the VBA Recorder to do the heavy lifting

After I set up my 2 Pivot Tables, I recorded a couple of steps with the VBA Recorder to help me write the code.  Here is the process I followed.

Read the Rest

Create a 445 Calendar using Power Query

by Matt Allington

In my last blog on PowerPivotPro.com I showed how easy it is to create a standard calendar for Power Pivot using Power Query.  Most of my customers however don’t use a standard calendar but instead use a 445 calendar (which is very common in the Retail Industry). A lot of people shared with me their 445 calendars after my last post, and that reminded me that although the concept of a 445 calendar is very common, everyone seems to have different rules on how the calendar works.

Differences in 445 Calendars include

  1. What month you start the financial year
  2. What day of the week is the start of the week.
  3. 365 divided by 7 = 52 + 1 day remainder. Different companies handle the extra day in different ways.

So there are almost as many permutations as companies and I don’t want to go down the path of trying to write 1 calendar that will work for everyone.  Instead this post covers the techniques I used to solve the 445 calendar problem for one of my customers.  If you are so inclined, you can copy these techniques plus some of your own to meet your own 445 needs.  To give you an idea, it took me about an hour to think through the problems, research the functions and build a working calendar.  If you copy my techniques, you should get a head start on that for your own 445.

The rules of this particular calendar are

  1. The first day of the financial year is the Monday on or before 1 May.
  2. The number of weeks each month are in the pattern 454 (4 for May, 5 for June, 4 for July) and then repeats.
  3. The calendar weeks, months and years mirror the financial data.  So Mon 31 Dec 2013 is the first week of financial week 36.  Even though the date is actually in the year 2013, it is treated as part of the first calendar week of 2014.

Here is how I did it.  But first a word of warning.  The steps are easiest to follow if you first download the sample workbook (link at the bottom) and step through the Applied Steps as you read my explanations in this post.  If you are not a Power Query expert and you just read the post, it will quite hard to understand.  So do yourself a favour and download the workbook, and step through it as you read the post

Read the Rest

Create a Custom Calendar in Power Query

by Matt Allington

In this post, I am going to show you how simple it is to create a custom calendar using Power Query.  If you follow the steps below, you will get a good sense of how capable Power Query is, and may spark your interest to learn more about it.

Different Types of Calendars

There are lots of different ways of creating a custom calendar for use in your Power Pivot workbooks, and plenty of discussion about them on PowerPivotPro.com (25 prior to this one).  Historically I have just used an Excel workbook that I created with all the relevant columns, and enough rows of dates to cover for the next year.  There is one problem with this approach however –  the date over runs when you are looking at last years’ data in a pivot.

over run

As you can see above, we have dates into the future that are showing sales for last year with no sales this year – because we haven’t had “this year” yet.  It is a pain to change the import filter on your Excel calendar each time you refresh (not practicable actually).  You can write some snappy Excel functions in your source calendar table to code around this, or you could make your DAX more complex, but better still – I think this is a great opportunity to learn some new skills – Power Query.

Building a custom calendar from scratch in Power Query is actually quite straight forward, and I am going to take you through it step by step below.

First Create a New Blank Power Query Workbook.

The Blank Query option is right at the bottom of the “From Other Sources” menu.

blank query

If you haven’t done so already, turn on your Formula bar from the view menu.  You will need this so you can easily see the Power Query Code that is generated by the UI tools, and also to allow you to edit the code by hand when needed.

turn on formula

Read the Rest