Archive for Microsoft

Use Power Query to Create Power Query Documentation

I wrote a blog post on last week on how to create a standard Power Pivot calendar using Power Query – from scratch.  I was doing some research this morning on how I could extend this concept to create a 4/4/5 Calendar (which is often used in the Retail Industry).  I was looking at this on-line reference documentation to find a function for a particular problem, and it was really hard to find what I was looking for.  There is no index at the top of the page, just a very long page of sections and formulae.

Then I had a brilliant thought – I wonder if I could use Power Query to download the Power Query documentation?  I tried it out, and the answer is a resounding yes.  This is what I did.

I copied the link to the web page.  This is the one I was using

I then opened a blank Excel workbook and went to the Power Query tab and inserted the above link in the box.


The Power Query Navigator returned a long list of formula categories, so I knew I was onto something.

Read More

How to Correctly Install Power Pivot for Excel 2010

As most subscribers of my blog would already know, I am a professional trainer of Power Pivot here in Australia. I do a lot of work for Corporations at their own premises with their own PC equipment. Not every company has made the move to Excel 2013, and in fact I sometimes find companies have to move from Excel 2007 to 2010 just to prepare for this training. There are a couple of traps when installing the Power Pivot for Excel 2010 Plug-in (and the Power Query Plug-in for that matter) and hence I am blogging these instructions to help people and companies get it right the first time. The main trap is getting the wrong version. But first a comment about 32 bit vs 64 bit.

32 bit vs 64 bit Power Pivot Versions

64 bit computers have been around for many years. The 32 bit architecture is quite old now and 64 bit is a lot more powerful. There are actually 4 technical layers that can be configured with either 32 bit or 64 bit, but each layer depends on the previous layer.

Let me explain. Starting at the bottom and working up, if you ever install 32 bit, then you can’t install 64 bit at a higher layer. But the reverse is not true. You can normally install 32 bit on top of a 64 bit layer (except for – the plug-in itself).

4 layers

Let’s discuss each layer

Read More

Power Query Issues with Power Pivot

Edit 16 May 2015: I now know the exact cause and cure for this problem.  See this post here

PhilC at asked me for advice on the best way to use Power Query with Power Pivot.  If you have come to this post via Google, it is probably because you have had issues and are looking for answers.  If you are reading this post for any other reason, then hopefully it will save you some pain by following my advice on how to minimise these Power Query issues with Power Pivot.

Quickly – what is Power Query

Power Query is the best thing to happen to Excel since Power Pivot!  Basically it is a lite extract, transform and load (ETL) tool for Excel users.  It allows us Excel users to grab data from almost anywhere, shape it into a form that can be used in Excel (increasingly Power Pivot), load it into your workbook and then away we go.

So what’s the problem?  Let me explain

Here is the scenario.  You grab some data using Power Query and then load it into Power Pivot.  You start building out your measures and adding value to your Power Pivot data model.  After investing many hours, you realise that you really need 1 more column of data from your Power Query table.  You go back to Power Query, add the new column of data, save the workbook and “BAM” you get the following error message.

We couldn’t refresh the table YourTableName from connection YourPQConnection. Here’s the error message we got: OLE DB or ODBC error: The query YourPowerQuery or one of its inputs was modified in Power Query after this connection was added.


When you first see this message, you can feel your stomach sink through the floor.  You have put hours into your data model and how you have to “start again”.

How to fix this problem after it happens

Read More

Power Pivot Forum now supports DAX Formatter

Well Power Pivot Forum has been live for 8 months now and has over 100 users, more than 400 posts and many thousands of page views. The Power Pivot forum continues to grow as new users are finding the need for help with their DAX problem.

Recently one of the forum members (xChillout) suggested that I take advantage of the API written for DAX to improve the experience. Basically this API allows you to send a DAX Formula over the Internet direct to DAX Formatter. DAX Formatter then runs its magic over the code, validates it is correct (or incorrect), and then sends a nicely formatted version of the DAX code back to the sender.

I have now integrated this into the forum so that anyone reading DAX Code posted by others will find it easier to read and understand.

So now at Power Pivot Forum you can type in some DAX code like this

DAX Wrappers

And DAX Formatter will send it back like this.

Read More

How to Bulk Refresh PowerPivot Workbooks on your PC

There are a number of different ways that you can refresh PowerPivot workbooks automatically on a schedule. The main ones I know of are:

  1. Using Power BI
  2. Using SharePoint Auto Refresh
  3. Using some sort of custom built desktop automation tool

The problem with Power BI and SharePoint

The problem with Power BI is that you need to have a subscription for every user that wants to consume the reports – and it is not cheap. I cover the downsides of Power BI in this post about how to access PowerPivot on iPad. SharePoint is a good solution if you have SharePoint Enterprise and you want to share your files in SharePoint. Many people don’t have access to SharePoint and/or need to refresh files for distribution in other ways. So that brings me to option 3 – desktop automation.

How to bulk refresh powerpivot workbooks on your PC

I have been a closet VBA coder for many years. In fact I first taught myself “really bad coding” using Lotus 123 Macros. Over the years I have accumulated code that I reuse for new purposes. At some stage about 10 years ago, I created a VBA workbook that will list the contents of a folder into a workbook. As I was thinking about the need to refresh PowerPivot Workbooks on a PC, I realised that I could reuse this code as the core of the project. Now I don’t profess this to be the best code, or even good code for that matter. But I can tell you this has been working successfully for me for some time.

How the refresh process works

Read More

How to access PowerPivot on iPad


PowerPivot on iPad – great experience!

One thing I find when working with my customers and meeting PowerPivot users is that a lot of people want to know how to access PowerPivot when they are mobile.  Most frequently people want to access PowerPivot reports on iPad, however in many cases just presenting data for PC users in an ‘easy to consume’ and ‘easy to control’ manner is also important.  Well there are 3 ways that you can set up to allow iPad (and PC users for that matter) to access PowerPivot reports from a web browser, and it doesn’t have to be difficult or expensive if you do it the right way.  The three options are:

  1. Microsoft Power BI in the Cloud
  2. An On Premise instance of SharePoint Enterprise Edition
  3. A Cloud based SharePoint Enterprise Service purchased as Software as a Service (SaaS).

I will cover each of these in turn leaving what I consider to be the best until last.


First of all, a definition of the term Power BI

Read More

PivotTables for iPad

Microsoft has released an upgrade to its Office Apps for iPad (version 1.1) that now includes – wait for it…. PivotTables for iPad Support!  Wooohoo.


But unfortunately there is good news and bad news.

First the good news.

You can indeed open a workbook that has PivotTables and you can interact with them on screen.


Now the bad news.

Unfortunately what is missing on the screen shot above is the “Slicer” that is in the workbook in the blank space in columns F:H.  I can see it when I open it in Excel 2013, but it is missing in the iPad version.  And there is more bad news too – no support for PowerPivot.


Well I guess Rome wasn’t built in a day, and this is definite progress.  However I will be waiting for PivotTables for iPad to include slicer and PowerPivot support before declaring this a game changer.

However all is not lost.  If you are looking to enable PivotTables and PowerPivot on iPad and other mobile devices, I can help you do that now – quickly and affordably with a cloud based hosted solution.  Most people don’t realise what a positive experience can be delivered if you do it right.  Just send me an email if you would like more information.

PowerPivot Forum Recruitment Drive

Back in early May I realised that there was not much in the way of a dedicated forum for PowerPivot users, especially for the general user.  There are some specialised forums out there for Microsoft Developers but not much for the average user.  As a result I went ahead and set up a new PowerPivot Forum and introduced it to the Web community.

PowerPivotForum.pngSo far so good – there are 30 members and growing slowly.  But any forum is only as good as its member community (size and participation level).  It doesn’t matter if you are a beginner or an experienced PowerPivot, Power Query, Power View or Power Map user – this is the place for you to come and ask questions or find answers.

If this is not you but you know someone that fits the bill, please share the link  with them.

Query Plan Analysis

Matt's curated best self serve BI content

Query Plan Analysis: The 5 Culprits that Cause 95% of Your Performance Headaches

I am fairly new to SQL Server, and I am by no means an expert. I learnt my database ‘skills’ using MS Access. In a recent engagement with a client, I had a need for a tool that could handle > 10GB of data, so I am now using (and appreciating the power of) SQL server. This session from Adam Machanic (great name for a DBA!) gives an excellent overview of the SQL Server Query Plan Analysis tools. It is useful for people like me who don’t know a lot about how this feature works, and the material is probably even more meaningful for experienced DBAs that really understand the content and can leverage the tips he is explaining. Having said that, if you do not write queries using SQL server, and are not interested in finding out about how SQL server is different to Access, then there is nothing for you in this session.

Top 5 culprits

Time Stamp Description
0:00 Intros
3:00 Adam’s contact details.,
3:55 Introduction to Query Plan
5:30 Demo of how Query Plan works and key concepts
18:00 Introduction and description of the 5 culprits, plus one bonus
26:35 Demos of them all

Here is the full video

Here is the link to see all the BI Track content.

Deep Dive into Power Query M Language

Matt's curated best self serve BI content

In this session, Matt Masson and Tess Palmer do a great job of showing everyone the depth and power of the Power Query M language that is the backbone of Power Query – it makes my list. Clearly they didn’t expect too many people in the audience to be able to follow them along the way, which came out in a light sarcastic way at times, but I think this reflects the state of maturity of this tool. Not too many people have deep knowledge since it is so new. I am waiting for Chris Webb to release his new book so I can get in some late night reading. Here is the agenda – mainly demos which is good.

M language agenda

and here is the relevant content

Time Stamp Description
0:00 Intros
1:55 Demo/Overview of the Power Query product, tab etc. Not too much new here.
2:50 A good lesson about the SQL Statement window. Don’t use it.
3:15 A great 2 min overview of what Power Query does, how the formula bar works, how to access the advanced editor, and good advice to use the UI first, then edit formulae.
5:00 Intro to M Language, why Microsoft selected it, why DAX is not up to the job.
7:30 Introduction to the concept of “Query Folding” – how Power Query pushes the work back to the database when it can. Good to know.
12:15 Some information about Library Functions. Here is a link with a PDF download that has a formula library reference that they talk to on this slide.
13:30 Demo time.

  • Explains the new features to change the default load behaviour to Excel and/or data model.
  • How to get inline help for the M language
  • Shows making changes to the advanced editor to bring in a 3 way join of tables from a DB.
17:45 Language Flow explanation – how the language passes on transformations from one step to another.
19:45 Type System. Explains the different data types, and how to define them, with a demo
29:30 Demo: Import and combine multiple CSV files into one table
32:40 Demo: Import a single file (Excel), edit the query to create a function, then import multiple versions of the same file and combine them. Matt shows you how to edit the regular query to a function.
41:30 Demo: Download complex data from Web. Good example of the breadth of the language using functions to download data.
48:15 Demo: Another web demo downloading multiple pages using a function.
55:40 An example to extract data from Binary objects. Far too deep for me!
61:00 Tips and Tricks. Worth watching. #shared in the formula bar will give you a list of functions and keywords.
64:10 Links Page
Language Reference:
Library Referenece:
Office Help:

Here is the full Power Query M Language Video

And here is the link to all the BI Stream Content

Solving Complex Business Problems with DAX – TechEd 2014

Matt's curated best self serve BI content

This is a continuation of my series on “best of” from TechEd 2014. This session by Kasper de Jonge makes my best of list. It is quite technical in places and digs in deep. I particularly like when he talks about “evaluation context”. Erik explains that there are 2 types of context (row context and filter context) and why it matters. The other thing really interesting in this session is that Erik describes how the development team uses (what he calls) ‘syntax sugar’ to create new functions that the user sees, but under the hood DAX is actually using a more complex calculation using other existing functions.



Time Stamp Description
0:00 Introductions and background
2:28 Agenda
3:40 What is evaluation context? A great bit of information at the 20 min mark about how “nested” measures are calculated – innermost
6:00 Demo: Running Totals
21:00 Demo: Tool Table (disconnected table example). He uses Values() to detect the “type” of data, and then uses a switch statement to select which measure to display in the pivot table, and explains how to prevent errors in Grand Total columns.
35:00 Demo: Time Intelligence example where you can create a “current month” calculated column that automatically updates with each new month, meaning you don’t have to change your pivot table each month.
40:00 Demo: Several row context examples for both columns and measures. He explains how using ‘calculate’ in a calc column will give you filter context, and now row context
48:00 Demo: Time Intelligence year on year growth.
57:30 Demo: Time Intelligence with a 445 calendar.
63:40 Q&A

Here is the full session

PowerPivot Forum Open for Business

As I spend more and more of my time working full time with PowerPivot and associated Excel products, I have been looking for suitable forums that are focused around PowerPivot. I have not found anything out there that I really like. So I spent some time over the weekend setting up a brand new PowerPivot Forum. In fact it is sooo new that it only has 4 members as of the time of this post.

It is my intention to grow this forum over time into a community of people helping each other to solve real world PowerPivot problems. While the forum is in its infancy, I will reply to every post to make sure that people that ask a question get a response. Hopefully others with the right skills will join and help out too, and it will hit critical mass in the not too distant future.

If you are looking for a PowerPivot Forum, then here is your opportunity to sign up.

PowerPivot Forum

Why Excel will SAVE you millions

Yesterday I read this post called “Why Excel is costing you millions” by Gayle Ryan.  I have never seen a more misleading and ill informed post about Excel, and I was motivated to provide a response to this article here.

Ryan starts out by talking about static reporting, and how that is no longer enough for business – this is definitely true.  She correctly points out that businesses need more data discovery, user focused, dynamic reports.  But from that point on, Ryan completely fails to recognise Excel for its strengths, and she is completely misleading in attributing problem after problem to Excel.  Let me share just a few of these incorrect statements along with my experience.

Ryan incorrect statement: “… spreadsheets don’t allow decision makers to see the big picture as effectively as a dashboard”.
What a load of rubbish.  Excel is a very powerful tool with fantastic dashboard capabilities built in, specifically PowerPivot, Pivot Tables,  Slicers and conditional formatting.  Excel has an enviable suite of visualisation capabilities and can easily be combined with tools like SharePoint Enterprise to provide an enterprise strength dashboard tool at a fraction of the cost of many other tools.  I personally built such a solution at Coca-Cola when I worked there.  Excel has a distinct advantage over many other dashboard tools – that is most business users know how to use Excel, and they can participate in the development of the dashboards themselves without having to rely solely on user requirements and specifications handed over to a developer.

Ryan incorrect statement: “Excel reports are static, and take time to produce”.
Absolute garbage!  Of course some reports are static, but to attribute Excel as being a tool that can only be used for static reporting is so wrong that I wonder what her motivation is for making such a statement.  Modern Excel has a powerful suite of capabilities including PowerPivot, Power Query and Power View that allow users to connect to almost any data source, produce a data model, and then analyse the data using discovery techniques.  When the user has created a report that has the potential for longevity, it can be polished into a user friendly reusable report with drill, slice and dice capabilities using Pivot Tables, Slicers and conditional formatting.  All this at a fraction of the cost to to the same with many other data visualisation tools.  And much of this capability is not new – Pivot Tables have been included in Excel since 2000.

Ryan incorrect statement: “Dashboards are also interactive, unlike Excel, which means that if a certain data point is troubling, dashboard users can drill down to get more information”.
Excel is a very powerful desktop or dashboard interactive tool using standard Pivot Tables, Slicers and Conditional Formatting.  Excel has taken further big steps forward with the release of PowerPivot and Power View (although in my opinion Microsoft has a lot of work to do to make Power View a real competitor to other more developed BI visualisation tools).

Ryan incorrect statement: “Excel spreadsheets … are actually hindering our ability to understand critical information by being difficult to read”.
You have got to be joking – are you real!? Excel is the most ubiquitous data analysis tool in use today, and probably over the last 20 years.  Excel is a flexible, cheap and agile tool that can present your data almost any way you want to see it.  If Excel is difficult to read, then it has more to do with the report developer than Excel itself.

Why Excel will save you millions

Excel will save you millions

In my view, one of the best ways to burn money is to believe that your problems are all about your tools, and that if you could just buy “a shiny new tool” that all your problems will go away.  Nothing is further from the truth.  My experience is that problems with existing BI solutions are related to “how” the tools are used and deployed as well as problems with master data standards and reporting processes.  Any business that reads Ryan’s article may be tempted to think that they should abandon Excel as a BI tool and instead go out and buy some new BI “discovery” tool.  It is definitely true that high end BI tools have a significant and growing role in business, but they are not the only tools.  My formula for how Excel will save you millions is as follows:

  1. Understand your business needs of data and reporting.  If you don’t know this, then you need to work it out BEFORE you spend a bucket of money on a new tool.
  2. Determine why your current tools/processes are not delivering on those needs.  Is it really the tools, or is it the way the tools are used, the process and the lack of data standards?
  3. Use Excel as a prototyping tool and determine “how” you can make your data work for you – learn by doing.  If you have a data warehouse, use PowerPivot to build on the fly data models that deliver real time, dynamic reporting that you can slice and dice to provide insights into your business operations.
  4. Iteratively improve your PowerPivot Excel tools until you get to a point that you can do no more.
  5. Then and only then should you ask yourself “do I need to replace what I have got with something better”.  In many cases the answer will be no.

The prototyping process will force you to think about your real problems (eg availability and cleanliness of data, knowing what you really need) and help you solve these problems as part of the prototyping process.  The further you go through the journey, the better your data and tools will become, and the more you will learn about what you actually need.  At the end of this journey you are highly likely to discover that you know what you need, and that you already have robust tools that can be published in the organisation using tools like SharePoint Enterprise (Excel Services, PowerPivot Galleries etc).

Compared with the risk of failure by believing that somehow some new shiny BI tool will solve your data woes, my approach above using Excel with PowerPivot is highly likely to save you many millions of dollars.

Create a PowerPivot Date Table with Power Query

I really love PowerPivot and Power Query. I have been doing Rob Collie’s on line training course on using PowerPivot. When I got to the section on Time Intelligence, it occurred to me that Power Query could be used to create the date table that you need. I did a bit of research and pieced together some techniques I found.

Here is the code to create a Date table for PowerPivot with Power Query.  Of course you can modify this to add or remove different columns if you like.

     Source = List.Dates,
     InvokedSource = Source(#date(2010, 1, 1), Duration.Days(DateTime.Date(DateTime.FixedLocalNow())-#date(2010,1,1)), #duration(1, 0, 0, 0)),
     TableFromList = Table.FromList(InvokedSource, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
     RenamedColumns = Table.RenameColumns(TableFromList,{{"Column1", "Date"}}),
     InsertedCustom = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date])),
     InsertedCustom1 = Table.AddColumn(InsertedCustom, "ShortYear", each Date.Year([Date])),
     InsertedCustom2 = Table.AddColumn(InsertedCustom1, "MonthNum", each Date.Month([Date])),
     InsertedCustom3 = Table.AddColumn(InsertedCustom2, "Day", each Date.Day([Date])),
     InsertedCustom8 = Table.TransformColumnTypes(InsertedCustom3,{{"Date", type date}}),
     InsertedCustom4 = Table.AddColumn(InsertedCustom8, "DayName", each Date.ToText([Date],"ddd")),
     InsertedCustom5 = Table.AddColumn(InsertedCustom4, "MonthName", each Date.ToText([Date],"MMM")),
     InsertedCustom6 = Table.AddColumn(InsertedCustom5, "QuarterNumber", each Date.QuarterOfYear([Date])),
     ChangedType = Table.TransformColumnTypes(InsertedCustom6,{{"ShortYear", type text}}),
     SplitColumnPos = Table.SplitColumn(ChangedType,"ShortYear",Splitter.SplitTextByRepeatedLengths(2),{"ShortYear.1", "ShortYear.2"}),
     ChangedType1 = Table.TransformColumnTypes(SplitColumnPos,{{"ShortYear.1", type number}, {"ShortYear.2", type number}}),
     RemovedColumns = Table.RemoveColumns(ChangedType1,{"ShortYear.1"}),
     RenamedColumns1 = Table.RenameColumns(RemovedColumns,{{"ShortYear.2", "ShortYear"}}),
     InsertedCustom7 = Table.AddColumn(RenamedColumns1, "Quarter", each "Q"&Number.ToText([QuarterNumber])&"/"&Number.ToText([ShortYear])),
     RemovedColumns1 = Table.RemoveColumns(InsertedCustom7,{"ShortYear", "QuarterNumber"})

One line of code above is the “secret sauce” – see below. This line of code was created in the advanced editor, and it creates a list of every day starting from 1 Jan 2010 through to today’s date. You can of course change the start date to suit your needs.

InvokedSource = Source(#date(2010, 1, 1), Duration.Days(DateTime.Date(DateTime.FixedLocalNow())-#date(2010,1,1)), #duration(1, 0, 0, 0)),

Faster Data Insights with Power BI

A couple of weeks ago Microsoft held a full day online session called Faster Data Insights With Power BI. There were 8 sessions of about 50 mins each that covered various aspects of the new Power BI suite. The sessions have now been converted to YouTube videos and are available from this YouTube Channel Playlist. Each video in the series starts with the text “Faster Data Insights…”

For those that want to learn more about the capabilities of these tools, these videos are a great resource. I particularly recommend watching numbers 2, 3, 4, 6 and 7 (although they all have relevance) – here is what you will see in these videos.

Faster Data Insights with Power BI, 01, Introduction to Power BI

This video is a general overview of the Power BI suite.  It explains how each product in the suite does a particular job in the end to end data analysis process.  There are some very high level examples of each tool.

Faster Data Insights with Power BI, 02, Drilldown on Data Discovery Using Power Query

This video is one of my favourites.  It shows a deep dive on Power Query from Matt Masson and he demonstrates the breadth of capability of this tool.  As of March 2014, Power Query is in a very fast development cycle with new releases coming out every month.

Faster Data Insights with Power BI, 03, The Data Stewardship Experience

This is a really important video, because it talks about Data Stewardship and how the Power BI – particularly the cloud based offering plays a role in making Power BI and enterprise strength tool.

Faster Data Insights with Power BI, 04, Building Stellar Data Visualizations Using Power View

In this session, Sandy Rivas demos the features of Power View.  There are good examples of the visualisation capabilities of this tool.

Faster Data Insights with Power BI, 05, Building 3D Visualizations Using Power Map

Personally I think Power Map is the least exciting tool in the suite.  However it is a good tool if you need to visualise geospatial and temporal data – this video shows you what it can do.

Faster Data Insights with Power BI, 06, Understanding Power BI Sites and Mobile BI

This video builds on video 3, and goes into a lot more depth on how the cloud version of Power BI can add value to the organisation.  There are lots of great examples of visualisation capabilities but also the share and reapply capabilities. Towards the end of the end of the video it talks about browser support and also mobile device support. Unfortunately no iPad support as at March 2014, but the video quotes “this year” for delivery of that.

Faster Data Insights with Power BI, 07, Working with Natural Language Querying Using Q&A

The natural language query tool shows a lot of promise.  This features basically allows you to load your data and reports into Power BI, and then allow users to type a natural language question in a browser, and get a visualisation that answers the question.

Faster Data Insights with Power BI, 08, Handling Data Management Gateway

This last video is quite technical, and describes how to set up Power BI for use in the Enterprise, how you can manage security and availability of the data.  This is not a video for the average desktop user unless you want to know what can be done.